This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have multiple files in a folder with different months of data. the data is all the same style. I need help figuring out how to: ex run Jan data with workflow and than run Feb with workflow and than append the data I chose from the workflow together. I can't have the same workflows joined at the beginning because I am using the select records for certain rows. I read you can use a batch macro but I am not sure how to do that at all. Any help would be absolutely great. thanks!
This can certainly be accomplished with a batch macro, but I'd first suggest ruling out that it cannot be done by reading the files in and segmenting by file name. You mention that this approach wouldn't work because you use the select records tool. Which records are you selecting - how are you determining this? Would it be possible if you selected "File Name" or "Full Path" in the "Output File Name as Field" setting of the input? That, coupled with, perhaps a tile tool and filter might help you accomplish what you set out to do.
Can you share sample data and/or what field you're using to select records?
Attached is some sample data and my current workflow. They are pulling from the same file but with different tabs but the data each month the data comes from a new file where I need to be able to put all the months in one single folder location and run the all together and append the new month with the current list from the rest of the months. Thanks!
Given that your files/sheets are formatted the way they are, I believe you are correct in that a macro is probably the best path forward. See the files attached which I think accomplish what you're looking to do.
First, I created a batch macro and incorporated it into this workflow to import all files/sheets in a location. Each file/sheet is run through the macro and the output of each run is appended to the previous.
Note that for the file input, I'm using a wildcard to pull in all files that start with "Sample" and are in an excel format (.xlsx). Next, notice that we're only bringing in the list of sheet names along with the full path of the file, rather than the data itself.
For the macro itself, I've simply taken one of your workflow paths and added an action to update the file path. This will read from the list of files from our input of the workflow.
Running this workflow will initially get all of your input files/sheets processed and into the same output file ("Final.xlsx").
I've also turned this into an app which will allow you to choose a file for when you want to add new file/sheets to the same output.
Note that when you are prompted, after selecting a file you'll want to make sure you select "Import only the list of sheet names" before you continue running the app.
The output here is configured to append the results to your previous "Final.xlsx" file.
Hopefully this gets you to where you need to be. Let me know if you have any questions