HI All,
I am trying to combine a number of workbooks, all of which have tabs titled the same.
I tried to create a workflow with input data for each of the tabs and with the use of "*" to pull automatically from a folder as and when more files come into it.
However I am getting an error which says that one of the tabs from one of the worksheets has a different schema than the 1st file
I think i need to use a batch macro but I am not sure how. Any suggestions how to solve this issue?
Thanks
Solved! Go to Solution.
Hi @seelan89 ,
Use the directory tool in conjunction with the attached macro. This will loop through and load in each sheet at a time. This is very useful as it circumvents a problem you often encounter with the standard Directory input with the Dynamic Input tool, where things such as merged cells in Excel often cause problems.
I hope this helps.
M.
I am a beginner here. I could not understand this macro and have no idea on how to run this. Would you please elaborate?
For me, I do have a problem with Read all input files. It goes well for all the files from 1Jan to 8Jan, but showed error for 9Jan saying it has a different schema than the 1st file in the set.
Hi @JokeFun ,
download the attached macro and save it into your macro folder (if you have not set one yet go to Options->User Settings->Edit User Settings and click the "Macros" tab. Click the + sign and navigate to a folder you want to use for your macros.
Once you have done this, use the directory tool to select the parent folder of where your Excel sheets are sitting (using the "Include SubDirectories" option if they are in multiple folders. Then feed this into both macro inputs. In the macro, on the "Questions" tab, select the fullpath field.
This should now load in all of your spreadsheets.
Hope this helps.
M.
Hi @mceleavey
I've run into a similar issue - I'm trying your macro solution. However, the macro throws up an error to specify a sheet. How do you do this?
Thanks
Hi @tgill1 ,
you need to use the directory tool to retrieve all filenames, you then use the formula tool to build the fullpath. In that formula, use "+|||<Sheet Name>" add the sheet name to the path.
M.
Thanks - I'm a beginner with macros so sorry for the questions.
I've added a formula in between the directory and macro tool. I get the error that File 1 is missing from the folder I saved the macros in.
Do you know why this may be?
@tgill1 @mceleavey Hi - I am running into the same issues trying to use this macro - was this ever resolved? thanks!
User | Count |
---|---|
19 | |
14 | |
13 | |
9 | |
8 |