I have a folder of xls and xlsx spreadsheet files, each with different tab names. I am only interested in importing the first tab of the spreadsheet (so perhaps a formula would work here), how do I go about doing this?
a.xlsx have a first tab named 'x'
b.xlsx have a first tab named 'y'
c.xls have a first tab named 'z'
d.xls have a first tab named 'w'
I figured that using directory, followed by a macro (control parameter, update value with formula, input data) would work here. But it seems that the table or query in input data requires me to specify the tab name for the template, and this workflow would not work when there are differing tab names.
I tested your sample solution workflow by changing the directory path to a folder containing one xlsx and xls spreadsheet, each with two different tab names. I got the following error ' The external program 'csript.exe' returned an error code :1'
Strangely enough, when I ran the workflow inside the macro, I got some results.
Using a dynamic input on the output of the formula tool doesn't work. Little bit more background : My goal is to do a batch macro (some workflow that I have created) on each of the filenames in turn. I already a batch macro that could read all xlsx files in a folder and perform the same workflow for each xlsx file. However, now with a bigger data set, I need to also run the workflow on xls files.
I tried that but the issue persist. But at least the above proposal with the macro that you introduced works, it successfully export all xls and xlsx files into a single data table, so I would mark it as solved.