Update Import Excel File with Multiple Sheets through Batch Macro Update Both

Question

Hello, I'm new to the whole Batch Macro thing and hoping someone can help with this question. I have looked all over to see if there is anything like this but cant seem to find anything. I looked into using the Dynamic INPUT but the thing is not all columns are the same and always got an error when trying to import that the Schema was not the same.

The is the closest I got to doing what i want to do but get hung up when trying to change the input file.

i have a APP Workflow my team runs weekly it compares to an excel file with multiple Sheets. I found online a batch macro that can pull in multiple sheets and join them all into 1 list which is fine.

The only thing is that INPUT FILE has to be updated on a weekly basis that I put into a File Browse Interface. The only thing is once the sheet is updated in the APP the INPUT Tool in the MACRO no longer matches the input sheet and kicks back an error.

Is there a way to update the input File in the App alongside with the INPUT inside the MACRO?

Any help would be appreciated.

Thank you

MACRO

apathetichell · Answer

O.k. - a few things:

1) you don't want to use a data cleansing here - the space is part of the sheet name ie if the sheet name is "my sheet  " - you want it to include those spaces.

2) when using those special characters you'll need to wrap your excel sheet name in quotes. so instead of using your sheet name variable in your formula tool you'd use "`"+[sheetname]+"$`" as your replace term... yes - the entire thing. basically in normal situation file|sheet is fine - but file|`sheet$` is actually more specific...

Nmassarone · Answer

ok Perfect thank you for the info ... with that i think i kind of figured what's causing the issue ....

the file that I was trying to pull in it looks like 2 of the tabs (that are giving errors) have spaces at the end of the TAB NAME. Even after running a data cleansing after the formula to remove the spaces it still kicks an error.

But if i use another sheet (where none of the tabs have spaces) it seems to work fine. Also i went into the Excel file made a copy, removed the spaces from the TAB NAME and it seemed to work.

Even adding a data cleansing tool inside the MACRO and inside the APP i guess didn't adjust it enough to run it without error so it might just be a process of making sure the tabs do not have any spaces before being uploaded to the APP.

Thank you for your help on this appreciate it.

apathetichell · Answer

Your setup seems o.k. - I'd recommend changing your action tool to simplify it inside of your macro. Your flow is mostly correct though.

filebrowse - action tool - input data (list of sheet names) in your outer macro. replace the list of sheet names with the sheet - feed in via control parameter to inner macro and update input data file there.

input inside the macro should be a template/test data which should work - but it should be updated on execution.

try changing your regex_replace to a vanilla replace. if you are still getting an error look at what's being fed into your macro and if you are still stumped let me know.