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 receive around 120 Excel XLS files each month. I need to input all of those at once. There is only one sheet in all the excels, but names are different for all.
Please help me with this to Input the XLS files
Go to Solution.
Go to Solution.
There are many historical discussions about importing multiple Excel files. Please search the community and review some of the prior questions and answers.
Here is one:
The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
Yes i did went through those but those are related xlsx extension. But the files which i have are of xls. Batch macro are not picking up those.
Can you post your workflow that is not picking up xls files?
The below is the attached Workflow.
If all of your XLS files are in the same folder, you can use the Input Data tool with a wildcard for the file name, instead of using the Directory tool.
If you looked at the post for The Ultimate Input Data Flowchart, which path did you follow in that flowchart?
For the problem you're trying to solve, each specific piece of information is important and will help you find the right posts in the forum.
- are files XLS or XLSX? because we can't easily get <List of Sheet Names> for XLS files
- are all sheet names the same
- do all sheets have the exact same layout (same column names)
Is the sheet name the same in all XLS files?
If yes, you need a formula tool before calling the macro to add "E" (your sheet name) to the FullPath (FullPath + "|||E")
If no, you will need to get every sheet name from every XLS file and use a formula tool to append the sheet name to the FullPath. Search the forum for XLS sheet name.
You'll see posts with info like this:
Table or Query = <List of Sheet Names>
but as far as I know that only works with XLSX files.
The first thing you'll need is a full path including Sheet Names, if you have varying sheet names.
The app in this post may have a method to get sheet names from XLS files, not sure....
see the comment about....a fantastic app that takes a directory path and converts all xls files to csv or .xlsx respectively.
The post is located all the way at the bottom.
Thank You it was a great help.