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

[Nmassarone]

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]

Dude - you have to include screen grabs of your action tool configs - not your workflow. Basically formula tool in outer app replaces "<List of Sheet Names>" with the actual sheet name so you are feeding in "myexcelfile.xlsx|sheetname" - looks like your action tool in your inner macro isn't configured properly to update the input data filename.

make sure you've set up your inner (And outer macro) to adjust schema based upon unioning by Name (in interface designer)...

[apathetichell]

Also change  "List Of Sheet Names" to "List of Sheet Names"

[Nmassarone]

It was more of a basic question if there's a way to use the Interface File Browse to update both the Input on the APP and the MACRO.... Should the Macro just be a guide or a template? should it not have to be updated if the INPUT on the APP is updated?

These are the screengrabs of the action configs.....

inside the APP

Action Tool 

i use the formula to generate the full path with the sheet name to get everything into the macro to output all sheets to 1 table

This flow works if i keep the File the same on the Macro and the APP. As soon as I change the file on the APP it kicks back the error saying does not match sheet or named.

If this should work without having to change the INPUT file on the macro then it might just be something I'm doing wrong like if the INPUT inside the MACRO is just a reference.

[apathetichell]

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.

[Nmassarone]

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]

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...