Importing Data from Multiple Excel Documents Into a Single File

**Maurica** · 09-28-2009, 03:11 PM

I have thousands of Excel files, each with a generic names (i.e. 2009092812163503.xls)

Each of them contain a header with contains column titles like Company Name, Executive Contact, Address, etc and then a single row of data for a single company.

I want to be able to task excel to extract all the data in the second line and enter them all into a single spreadsheet for further work.

We're talking 30,000+ unique files here, what would be the best way to approach this?

**stanleydgromjr** · 09-28-2009, 03:39 PM

Maurica,

Welcome to the Excel Forum.

We could probably use formulae to get the data, instead of opening 30,000+ files.

What is the full path to the 30,000+ files?

Do the 30,000+ files only have one worksheet? If so, what is the sheet name?

Your data in the 30,000+ files is in row 2, from column A to what last column?

What is the workbook name, and sheetname, that the data will be copied to?

In your summary worksheet, we could have in column A all the filenames of the files, and their data from column B to last respective column of data.

Please attach a copy of one of the 30,000+ files - scroll down and see "Manage Attachments".

**Maurica** · 09-28-2009, 03:57 PM

For now I've set up the files in a base directory within My Documents
C:\Documents and Settings\maurica\My Documents\CompanyContacts\Raw

I'm not exactly up to lingo with Excel so I'm not sure which is the workbook name, though if my assumption is correct, the file name is just a random mixture of numbers. (2009092812163503.xls)

Data extends out to Column IV

Again, a little fuzzy on the differences, the file I want to combine all data in is being aptly named 'Master.xls' and the sheet name shall be 'Compilation'

Is there any quick way to import the 30,000 file names into Column A?

**stanleydgromjr** · 09-28-2009, 04:37 PM

Maurica,

Thanks for the workbook.

In your summary worksheet, we could have in column A all the filenames of the files, and their data from column B to last respective column of data.

The workbook you supplied contains titles in all 256 columns, row 1.

We would not be able to include the filenames in column A, in sheet "Compilation".

Could we use one of the other columns?

Do you have access to Excel 2007?

**Maurica** · 09-28-2009, 04:43 PM

I do not believe we can use any of the other columns because in some files a particular group is relevant, while in others it may not be so... I believe all-in-all, throughout the 30,000 files all 256 become relevant. (just taking a look at 10,000 which have been compiled manually by another worker over the past week)

I do not have access to Excel 2007, only 2003

**stanleydgromjr** · 09-29-2009, 02:41 PM

Maurica,

I have tested the below code in my environment, with three copies (different names) of your sample workbook.

Here you go.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Please Login or Register  to view this content.

Then run the "GetMyData" macro.

If you needed a worksheet in your workbook to record the "Company Name" and "filename", I have that code ready to go. It would record this information, in the same row as the above, in a sheet "CompanyFilename".

**YvonH** · 11-14-2012, 03:15 PM

Thanks stanleydgromjr for your macro.

I had a similar problem and I was able to fix by tweaking your macro.

**stanleydgromjr** · 11-14-2012, 10:21 PM

YvonH,

Welcome to the Excel Forum.

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.

**YvonH** · 11-26-2012, 01:30 PM

Instead of copying a full row, I'd like to copy a column from row 1 to 1000. I'm not sure how I should modify the macro to make this work. I tried a few things but my understanding of the VBA language is still a bit limited.

Thanks,

**stanleydgromjr** · 11-26-2012, 03:24 PM

YvonH,

Because your requirements are completey different than those of Maurica.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In the new post:

1. Attach a link from/to this post.

2. Post your modified macro code from this post:
Posting the modified VBA code, please use Code Tags - like this:

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

3. Attach a workbook:
In one worksheet (what worksheet name) put the structure of where the new data is going into.
In one worksheet (what worksheet name) put the raw data of one of the workbooks/worksheet that we are copying from.
What is the column you are interested in?