Macro Input Error

[SH_94]

Hi Community ,

I have the error as shown below after running the macro and would like to ask how should we fix that?

[clmc9601]

Hi @SH_94,

Try making sure that the end of your filepaths are exactly as follows:

Book5.xlsb|`Sheet1$`

A single pipe (|) and backticks (`) not apostrophes ('). I made these changes in the text input tool and formula tool on the lower datastream in your workflow. The automatically imported one from the upper macro worked as expected. I hope this helps!

[mceleavey]

Hi @SH_94 ,

It appears you're trying to open an Excel file with the sheet name "Sheet1$" which does not exist.

Try dropping the $ from the fullpath formula.

M.

[mceleavey]

Hi @SH_94 ,

If you're trying to load in multiple sheets with different tab names, I've attached a couple of macros and an example for you.

M

[SH_94]

Hi @mceleavey ,

The file name actually automatically given in the alteryx. Can i just confirm that normally we will still need to check and update accordingly?

Thank you.

[SH_94]

Hi @clmc9601 ,

May i know how we should rename the path and update it accordingly so that no error coming out ?

As i always fill the replace string column with the data obtained from screenshot below .

[SH_94]

Hi @mceleavey ,

Thank you for the macro files shared. Can you briefly explain in what circumstances, i need to use the macro that you have shared?

Or i need to use these three files together?

[mceleavey]

Hi @SH_94 ,

If you look at the example workflow you will see how it works.

The first retrieves the sheet names, which you can then filter out accordingly. This then builds the fullpath and loads them all in using the second macro.

M.