Importing Multiple Files into Excel 2010

I have files (with .ill extension) in individual folders (that have other files in them as well) that I would like to import into Excel as separate worksheet in one workbook. By using 'get external data, 'from text', and 'text to column, space-delimited' I can easily import these into Excel. However, there are 1000 such files and it would be great if I can automate this process - naming each tab with the name of the file (picture attached). Thanks in advance for your help!

Perhaps a macro similar to this

Please Login or Register  to view this content.

This macro loops through a specific folder ("D:\a_tester\") this you need to change of course to suit your needs. For every *.ill file it finds it will add a new worksheet after the last sheet in the workbook with the same name as the .ill file minus the .ill part.

The other parts of the macro starting with ".FieldNames = True" down to "End With" may not be suited for how you wish to import data so I would recommend you record a macro importing one .ill file and replace the parts of my macro that differ with your recorded macro.

In your post you speak of 1000 such files. Does this mean that you will end up with a workbook containing 1000 worksheets? Not sure this would be a good idea.

Alf

Hi Alf,

Thanks a lot for the thoughtful reply! Yes, a workbook with 1000 tabs may not be the best way of doing this. Next I have to find the standard deviation (SD) of every row and column in each of those tabs, find the mean of the SD in each tab, row-wise and column-wise, yielding two values per tab as x-y coordinate of a point and finally print a graph from the resulting 1000 points. If you happen to have a suggestion of doing this any other way do let me know!

Hi,

Since I don't know anything about ".ill" files, I've no suggestion how to work with them "in situ" to get the desired result. What I probably would do is with a macro first import the ".ill" file to the excel worksheet as in my macro above. Then let the macro calculate the SD of all the rows and columns and then "condense" this to an x-y coordinate and copy it to a "Result" sheet and then delete the "old" worksheet find the next ".ill" file to import create a new worksheet extract data and copy x-y coordinates to "Result" sheet ...... and repeat this for all ".ill" files.

In this way I would end up with all my data on one sheet.

Alf

Hi Alf,
Thanks again for the thoughts. I will keep your suggestions in mind as I try to work this out.
Best,
Shamim.