The Macro giving error that sheet name does not match a sheet or named range in File

[ansh09]

Hi i am facing an issue with the Alteryx Version 2024.1 version having AMP engine while running my Old Workflow which is been build in the previous version.

As when i am trying to dynamically input my file containing temporary test data, the first macro takes the data and creates a new column with full path which contains the full path of the excel file in concatenation to sheet name.( 

This macro takes the FullPath field (XLSX file and directory) and updates the Input Data tool. The Input Data tool outputs the list of sheet names from the current Excel file. It also outputs the Full Path as an additional field.

A Formula tool then builds the string of FullPath+Table/Sheet name. Notice that it also adds a "$" to the end of complete string. The Select tool removes everything except the constructed string from the Formula tool and is then fed into the next macro.)

But as the data goes to 2nd Macro (This macro simply takes each sheet from one Excel file and opens them one by one, unioning all results at the end and returning to the first macro.) it didn't get execute , it just throws an Error that " Sheet Name does not match a sheet or named range in File.xlsx or the file is corrupted .

Please can anyone help me in solving the error? Pasting below the error screenshot for the same.

[binuacs · Accepted solution]

@ansh09 can you update the macaro1 formula like below and try

[ansh09]

Thanks for the quick solution, it worked.!!

[MMarch]

Hi binuacs,
I have the simular error. I tried the formula that you have indicated, but it is not working.
Have you any suggestions how to fix this error.
Please see attached the snap of the errors in my workflow and the macro2 (original version and the version with solution that you have mentioned):

This is an extraction of the main workflow:

Below is the macro2 that has the function of selecting in the <list of sheet names> the sheets with the information "2024" and then apply this rule for the all spreadsheets in the folder indicated in the input of the main workflow above.
We use the formula function to indicate the information that should be replaced: FileName1 and the sheet names:

Below is the macro2 with the solution that you have indicated, but the selection function is no longer providing the full path with 2024 sheets:

We have also tried to combine your solution with the previous formula:

But it is still not working.
Thank you in advance for any ideas ti solve this issue.

[Pilsner]

Hello @MMarch 

I'm not certain as to why you're getting an error, but here are a couple of things to check. 

Please make sure the formula is applied in the first macro (macro 1), and also surround the sheet name in single quotes.

Secondly, please make sure that in the second macro, the control parameter in macro two is set to overwrite the entire file path within the input tool.

 

Finally, it appears as if you might be using the text to columns tool to parse out the "||<list of sheet names>" text from your file path. This will prevent the formula from doing its job properly as it need to din this string, in order to know where to write the sheet name to. 


Apologies if any of these are not the case / don't fix the issue, but without the workflow itself I'm struggling for further ideas. I have tried to recreate the same process myself and attached the packaged workflow below. (I have used relative paths so it should work on your end).

If you have any questions please let me know. 

Regards - Pilsner

[MMarch]

Hello Pilsner,

Thank you for your help.

We have applied the modifications and now there is new error, the macro2 are not working properly: all the functions are only been applied to the sheets of the file selected as the input exemple of the macro1 and 2.

In the snap below, we can see the that output of macro1 is correct: the selection of the 2024 sheets has been performed for the all excel files:

In the macro2, we have indicated on the control parameter to replace a specific string:

Any ideas how we can make macro2 apply the formating rules for the other sheets and not only the spreadsheet selected as the input exemple?

[Pilsner]

Hello @MMarch 

Im not certain I've understood correctly, so please let me know if I'm mistaken. 

From what I can tell, when you're hitting run on the main workflow, it runs without any error messages, but only seems to process a single batch (one sheet) worth of data. If this is the case. There are a couple of things to check here. 

Firstly, its always worth double-checking the batch macro is actually pointed at the column with the sheet names inserted. 

Secondly, in macro 2, you don't actually need to have replace specific string selected (i know i did but this wasn't needed). It might be worth unchecking this box, then just clicking on the "file - value= ....." section instead.

Finally, if your sheets have different schemas, this could cause an issue too. To get around this you can go into the interface designer (within macro 2) and select one of the other output modes i,e Auto configure by position.

My only other suggestion would be to place all your additional tools, such as the multi row, outside of the macro. This could help to isolate the issue further if it still proceeds.

If you have any questions please let me know. 

Regards - Pilsner