Combine Excel workbooks into 1 sheet with file name

Question

In a directory, there are multiple identically structured Excel workbooks with one sheet in each. The sheet names are all labelled the same (i.e. Sheet 1). I need to consolidate the dataset into one sheet and extract the last 8 characters of the file name for each book and add this as a column in the combined dataset

* Sheet name in each workbook (Sheet1) 
* File Name: Accounting_Details_-_MMDDYYYY

I used the wildcard feature to import along with file names (i.e. Accounting_Details*), but for some reason it is not importing all the workbooks. It only imports 3/12 workbooks. Total record count is 4.8K, whereas I am expecting 27k. What could be causing the issue?

alex · Answer

Using Notepad++ or similar, look at the what should have been imported in the 4th file and you may see it has extra column, different column name or other issues.  More than likely you should use the batch method.  Lots of examples of this method in the discussion board and articles.  Directory tool to bring in file names, formula tool to add sheet name to the file name and then feed that into a batch macro.  Output file name as field and rip the date out the name in the macro as well.

Robotomation · Answer

I am just using the input tool with a wildcard. I've attached the screenshots below of the file directory and the actual workflow. It's not erroring out, but it's only taking the first three files. I would want it to import all the files in that directory

Screenshot1.png

Screenshot2.png

rzdodson · Answer

@Robotomation there isn't enough information to help you out here. What is your method for bringing these files together? Are you utilizing a batch macro, a Directory tool, or some combination thereof? If able, would you mind posting a screenshot of the workflow you are currently trying to troubleshoot with where it is erroring out?