Writing a Macro for combining individual workbooks into one.

**DotWFM** · 12-10-2012, 04:05 PM

In my work I run a report using 200 individual reports in separate workbooks and I was hoping to make a macro that combines all 200 into one workbook that I can use the vlookup function on. Currently I am manually looking at each individual report and typing in the four needed numbers from each. I would use the vlookup function but it won't carry over to each workbook I need to search from. I know about dim but don't know how to use it to create a macro that copies, pastes in a "Master Workbook", then closes or minimizing the workbook and moving on to the next one and repeating the process until I have every report on one "Master Workbook" that I can use vlookup. How I would write that, I don't know. Anybody?
DotWFM

**xladept** · 12-10-2012, 04:48 PM

You want to "run the directory" - here's a routine that you can alter to your specifics:

Please Login or Register  to view this content.

Change "Database" to "Master" etc. etc.

**DotWFM** · 12-10-2012, 06:22 PM

Thank you for your quick response, I appreciate your help. Could you explain the specifics of each step of your response? Can you, or someone with a lot of time, walk me through what the steps are actually doing? Sorry, this is my first time here and I'm feeling like the ol' Jalopy stuck in 1st trying to merge into fast lane.
DotWFM

**xladept** · 12-10-2012, 08:46 PM

Maybe the old jalopy is a hot rod:

'Running a Directory to extract data

Please Login or Register  to view this content.

I broke out the code and commented it - hope this helps!

**DotWFM** · 12-11-2012, 03:37 PM

So if I wanted to copy cells A1:D10 and paste it on the Master where would I insert that into the macro? Where can I edit is what I am asking.
DotWFM

**xladept** · 12-11-2012, 03:50 PM

Please Login or Register  to view this content.

**DotWFM** · 12-11-2012, 05:30 PM

K, I am running into this error:
Capture.PNG

Here is what I have so far on the Macro

Formula:

Please Login or Register  to view this content.

What do I need to change to make this work?
Also, can I just hit run when the books are open or do I need to run it on the Master? Because so far nothing has changed. I follow it through the steps but no workbook moves, nothing is copied, nothing.

**xladept** · 12-11-2012, 09:18 PM

You don't need to run it on the Master.

Please Login or Register  to view this content.

is valid code iff the

Please Login or Register  to view this content.

above yielded a non-null - step (F8) through the code and see whether the directory is initiated.

**DotWFM** · 12-12-2012, 10:51 AM

Okay, I changed the the U=Dir() to U=Dir(P) and it now gives no error. I still don't have any movement at all within the workbook.
I believe it is because of the names of the reports. First, they are all .txt files. I generally run a basic macro that adds some formulas and hides a bunch of columns and then I save them as .xlsx files. If possible I would like to tack my macro onto yours to have them run in sequence, but that might be putting the buggy in front of the horse at this point.
Second, all these individual reports start with "report_list_bcms_agent" and then have a different 4 digit number for each agent, and then it ends with the dates. So, I am guessing the macro is running perfectly but cannot find anything that matches to copy and paste. I stepped through it and when it came down to the part about

Formula:

Please Login or Register  to view this content.

it just skips that step and goes to the next one and then finishes. Am I right in assuming that the names are where my problem lies?

DotWFM

**xladept** · 12-12-2012, 03:03 PM

I think you should change this line:

Please Login or Register  to view this content.

The U=Dir(P) must occur just once:

Please Login or Register  to view this content.

Then at the procedure:

Please Login or Register  to view this content.

Perhaps you should change this:

Please Login or Register  to view this content.

And get rid of this:

Please Login or Register  to view this content.

.
.
.

Please Login or Register  to view this content.

**DotWFM** · 12-12-2012, 05:28 PM

So it goes to the right path now and everything works perfectly except what I actually need to run. Here's the whole thing:

Formula:

Please Login or Register  to view this content.

Highlighted in red is just skipped when I step through it. Then it does an infinite loop at the blue highlighted. What is wrong?

**xladept** · 12-12-2012, 08:12 PM

You can only have U=Dir(P) once, at the beginning - Change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

And take these lines out:

Please Login or Register  to view this content.

then see if it at least gets into the red!

**DotWFM** · 12-12-2012, 08:19 PM

No, still skips it. But it is now accessing the Master correctly and pulls it into the book.

**xladept** · 12-12-2012, 08:28 PM

What do you get for

Please Login or Register  to view this content.

?????

**DotWFM** · 12-12-2012, 08:37 PM

I don't really understand what that means. It's the path of the active workbook, correct?
Is this it?

Formula:

Please Login or Register  to view this content.

**xladept** · 12-12-2012, 09:01 PM

OK - that means that the directory is being accessed - are there any files in the directory that you don't want to process???

**DotWFM** · 12-12-2012, 09:03 PM

No, I made sure that the path had no files but the macro, master, and individual reports.

**xladept** · 12-13-2012, 02:13 AM

Actually it should be the first workbook in the folder - not including the path!

That's the critical part of the routine - if you don't get a book name for Dir(P) then the whole directory isn't being accessed and the routine is moot. Try running it from the Master (with the Master activated) and step to the Dir(P) and play with the Path and Backslash until you get a book name for Dir(P).

**DotWFM** · 12-13-2012, 02:28 PM

Okay, I changed some things and now I am getting the error 'Subscript out of range' right here:

Formula:

Please Login or Register  to view this content.

I moved the Master into a different folder than the macro and it is now hitting more of the steps. I believe it will work now if we can figure out why it's stuck on the wb.sheets.

**xladept** · 12-13-2012, 02:47 PM

The subscript will fire if there is no "Sheet1" - Change that to Whatever the sheet name actually is!

**DotWFM** · 12-13-2012, 04:16 PM

OOoooh, got it. It worked!
Thank you so much for your thorough help.

**DotWFM** · 12-13-2012, 04:18 PM

Second question,
I generally run a macro on these files before I compile them, any way I can work that macro into this macro? I've been researching "Call" so I could call the macro but it hasn't clicked yet.

DotWFM

**xladept** · 12-13-2012, 04:37 PM

Hi DotWFM,

Glad you got it running -

Please Login or Register  to view this content.

**DotWFM** · 12-13-2012, 04:57 PM

Beautiful! Thank you so much.
P.S. I know I should give you reputation points or something, how do I do that?

**xladept** · 12-13-2012, 06:03 PM

You're welcome! To give rep just click on the star icon to the left of my post.

**tsinovoi** · 12-14-2012, 04:42 PM

Hi Guys,
I am attempting to do the exact same thing just different ranges, but mine doesn't seem to work. Could you publish the final code? what I have is the following but the Dir() value goes through all the files and becomes invalid before the code even gets to the U=Dir() portion

Any help is much appreciated.
Cheers,
Eli

Please Login or Register  to view this content.

**xladept** · 12-14-2012, 04:51 PM

Hi tsinovoi,

The whole routine depends on the first directory call - Dir(P) - play with that until it yields a book name.

(BTW You should start your own thread for this!)

**tsinovoi** · 12-14-2012, 04:53 PM

It does yield a book name, it yields "Master Worksheet.xlsm"

**xladept** · 12-14-2012, 08:44 PM

Try it like this:

Please Login or Register  to view this content.

BTW I think SubMerge is quite clever!

**tsinovoi** · 12-14-2012, 09:04 PM

Yes its is ! very clever.

still having the same issue though. When i put a watch on the Dir() expression it seems that the expression iterates through each file in the folder before it is even mentioned in the code. Maybe I dont have the file in the right directory? or am pasting the code in the wrong place? currently its module 1 of the master worksheet. The file directory itself has the following files Master Worksheet.xlsm,W1.XLSM, W2.xlsm, W3.xlsm.

What do you think?

**xladept** · 12-14-2012, 09:12 PM

Try commenting this out:

Please Login or Register  to view this content.

and see if it gets to the open

**tsinovoi** · 12-14-2012, 09:22 PM

Doesnt work, same issue.

**xladept** · 12-14-2012, 09:29 PM

It never gets to the open???

**tsinovoi** · 12-14-2012, 09:31 PM

it gives an error on U=Dir()
So no it doesnt ever get to any file besides the master

**xladept** · 12-14-2012, 10:29 PM

I set up a test with your basic information (Book Names & Sheet Names) and it ran for me?????

**tsinovoi** · 12-17-2012, 10:16 AM

Are you keeping all of the excel files in the same folder?
Also where are you pasting the code for Sub Merge

**xladept** · 12-17-2012, 02:53 PM

Yes - the files are all in the same folder(directory). The code is in a module in macro-enabled workbook.

Writing a Macro for combining individual workbooks into one.

LinkBack

Thread Tools

Rate This Thread

Display

Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Re: Writing a Macro for combining individual workbooks into one.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions