Importing Data from Multiple Excel Documents Into a Single File

**DinahMarcinko** · 08-21-2012, 06:56 PM

Hello

I am wanting to export specific cells from multiple (800+) excel files into a mail merge document to create mailing labels. The file name is the same for each document - just in a different location with in the my directory. The cell location is also the same on each document. How would I do this with out having to open each of the documents individually?

Any help you could provide would be greatly appreciated.

Thank you
Dinah

**JBeaucaire** · 08-21-2012, 07:25 PM

To open all the files in a specific directory and copy specific cells from the first (only?) sheet in those file into a row-based database in a new workbook

1) Name the sheet Mail Merge (the sheet, not the workbook)
2) Put your headers in row 1
3) Open the VBeditor and put this macro in a standard code module (Insert > Module)

Please Login or Register  to view this content.

It's the BOLD section you'll need to really work on.

**DinahMarcinko** · 09-06-2012, 04:11 PM

Hi Jerry

I tried your code above and it didn't work. Mostly I am sure because I have know idea what I am doing.

What did you mean it is the BOLD section I will have to really work on? What do I do after I enter the code in the VB editor? I just clicked the green run button. Below is the code you gave be edited with specific source information if that is helpful. I can also send a sample of the directory if that would also help.

Please Login or Register  to view this content.

Any instruction you have is greatly appreciated.

**JBeaucaire** · 09-06-2012, 08:48 PM

This thread has been moved to the programming forum.
====================

TIP: Code tags are added manually with braces [ ]. Or highlight the code and click on the # icon.
I've corrected your post above. You can use the EDIT POST option in the lower right corner of posts to correct your own posts when needed.

====================

It looks like you figured out where the BOLD TEXT from my post #2 is, it was only 3 lines of code highlighted in bold. You've added more of those lines correctly in your post. I see no particular problem with your edits. Perhaps you put it in a sheet module instead of a standard code module. Or perhaps your data is not in the first sheet of the data workbooks?

Generic steps for macros:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

**DinahMarcinko** · 09-07-2012, 04:02 PM

Hi Again

The data needed is in the first sheet named "contact information". In which I edited the code to read "contact information" instead of sheet 1 . Would this prevent it from working?

**DinahMarcinko** · 09-07-2012, 04:18 PM

I have attached the work book and a sample of the files I am trying to get the data from. Maybe you could have a look to see what I am doing wrong. Thank you so much. Contacts ad.xlsm PROJECT WORKBOOK.xls Contacts ad.xlsm PROJECT WORKBOOK.xls

**JBeaucaire** · 09-07-2012, 04:30 PM

This is wrong:

Please Login or Register  to view this content.

You cannot set the value of one cell to the value other multiple cells (merged cells) with that syntax. You simply reference the primary cell, B3. I don't use merged cells, so I have little experience working around them, and little patience for the problems they introduce, this as an example.

Just changing that source to B3 should work.