Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

**justin11** · 07-31-2013, 05:36 PM

Hey guys,

I have another question I can't seem to find an answer for in the forums or anywhere else. The only answer I get is to copy/paste them all into one.

I have about one hundred separate excel files that have just one worksheet in them. They all have identical column headers, as it is the same report run every couple of days.

Whenever I need to search for data, I need to open up several files one at a time and run a search for what I'm looking for. I'd like to put these all into one giant worksheet so that I can sort, pivot, search any way I want with all the data collectively.

Does anyone know how to do this? Sorry if this has been answered elsewhere, but I haven't been able to find it.

Thanks in advance.

**Alf** · 08-01-2013, 12:42 AM

Perhaps a macro like this?

Please Login or Register  to view this content.

Macro will loop through all files (xlsx type) and copy all information to the active sheet on "master" file. If you only wish to open and copy some of the files you must supply additional information so rules can be set up in order to select files of intrest.

Before running macro change the "sPath" string i.e. "C:\Temp\" to the folder where your files can be found.

If files contains formulas change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The line

Please Login or Register  to view this content.

skipps copying the 1st line in every file as I assume this line is only headings. If you do wish to copy all remove the "Offset(1)" part or if there are two lines of headings you wish to avoid copying the "Offset(1)" should be changed to "Offset(2)"

Alf

**justin11** · 08-01-2013, 11:05 AM

Thanks for your help, Alf. I'm a complete novice with VBA, so sorry if I'm asking obvious questions, but:

I'm getting an error message that reads

Compile error: syntax error

when I try running the macro, and the line --- If i = 1 And Range(“A1)” = “” Then --- gets highlighted automatically.

I assume that means this line is where the error is? Any help is greatly appreciated. Thanks!

**Alf** · 08-01-2013, 11:11 AM

Don't know what my keyboard did (or perhaps I) the line should read

Please Login or Register  to view this content.

Sorry about that.

Alf

**justin11** · 08-01-2013, 11:56 AM

Thanks for the quick response, Alf.

Okay, now the error message doesn't show up, but nothing happens when I run the macro. Am I skipping a step?

**Alf** · 08-01-2013, 12:35 PM

No I don't think so. Did you change the "sPath"?

Please Login or Register  to view this content.

Must be changed to the drive and folder where you keep your files. The last "\" in this line is also most important if you don't add it the macro don't work.

I've also assumed the files you wish to import are .xlsx files. If they are .xls files you must change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If having checked all this and macro still don't work then I recomend you recod a macro opening one of files you would like to import and post that macro result.

Alf

**justin11** · 08-01-2013, 12:43 PM

Ooohh, I didn't include the last backslash. So it runs now, but immediately gives me a run time 1004 error. "application-defined or object-defined error"

What does this mean?

Thanks again for your help. I really appreciate it.

**justin11** · 08-01-2013, 12:46 PM

Wait!! It actually DID work for most of the contents of the folder. It's all compiled into one worksheet now. The only problem is it didn't capture ALL of the files, I think because there was too much data. Is there a workaround or a way to add these files?

**Alf** · 08-01-2013, 01:21 PM

I think because there was too much data.

Yes if you manage to fill all rows in the sheet down to row nr 1048576 (max rows for excel 2007) then you will get this error when you try to add more rows.

Is there a workaround or a way to add these files?

Yes that should be possible. Remember my comments in post #2 ?

If you only wish to open and copy some of the files you must supply additional information so rules can be set up in order to select files of intrest.

So what file do you wish to import? And perhaps you could upload a sample file as well. Perhaps data from the files could be copied to column B, C, D and so fort and then add the file name in column A so you have a chance to indentify wich file you got the data from?

If you realy wish to import all files then the only solution would be to check the value of "i" and add an if statement that if "i" greater than say 1040000 then activate sheet2 and import the rest of the files to this sheet unless you can persuade Microsoft to exstend the number of rows in the worksheet as both excel 2007, 2010 and 2013 have the same max number of rows.

Alf

**justin11** · 08-01-2013, 07:39 PM

Hey Alf,

I figured it out. The problem was I said no when it gave that prompt, [paraphrasing] "there's a large amount on the clipboard, do you want to save it to open the next time you use excel?" I selected "Yes," but the same message would just pop back up. Finally I just said "No" and that's why it only captured the first few files.

I thought I'd give it another shot, and I just kept hitting "Yes" to that prompt for the hell of it. After maybe 20 clicks on "Yes" it finally worked! I think there's just SO MUCH data and excel constantly had to ask me to save it.

Thank you so much for your help. This could not have worked any better; it did exactly what I wanted.

Thanks again Alf!

**Alf** · 08-02-2013, 12:16 AM

After maybe 20 clicks on "Yes" it finally worked!

Hah, so that was the problem I'm glad you figured it out because I newer thought about that. Well the "cure" should be changing these lines to:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Since your problem now seems solved could you then please mark thread "solved" as per forum rules.

Alf

Ps

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

**justin11** · 08-02-2013, 11:02 AM

Marked SOLVED.

Hey, that "Application.DisplayAlerts = False" line works!

So now every time I save a file in the folder I specified, when I open the sheet you helped me make with macro, I can just Run Macro and it will update.

Thanks so much for all your help.

**Alf** · 08-02-2013, 02:17 PM

when I open the sheet you helped me make with macro, I can just Run Macro and it will update

Well yes and no. As this macro "knows" nothing of what has happened before it will when run import all the files in that specific folder to the workbook and active sheet. So if the sheet contains the info from a 100+ files adding an extra file and running the macro again will result in that the target file now contains information from 201+ files.

If line:

Please Login or Register  to view this content.

Is added to the macro after the last "Dim" statement this will clear the target range before the macro starts importing all the 101+ files.

Solution 2 is to copy the sFile (i.e. the file name to say sheet2) and name that range (say data). This is done the first time macro is run.
For the subsequent runs macro could use the vlookup function to check if a particular sFile is already present in the data range. If so macro skips this file and test next file in folder.

Problem with method 2 is if somebody do update a particular file and saves it keeping the same name the next time macro is run vlookup could see that this file is already imported to target file and skip it.

On the other hand if a file is deleted after a macro run the file values will still stay in the target file.

Have a think about how you wish to proceed. Solution 1 you can do on your own but if you go for solution 2 you may require a bit of help. If so do tell me and I'll give a hand.

Alf

**Alf** · 08-03-2013, 02:23 AM

Thanks for rep!

Alf

**justin11** · 08-03-2013, 12:50 PM

No problem on the rep, you were extremely helpful!

I ran with solution one and tested it out. I don't think I'm having issues, as it doesn't look like there was twice as much data, but I didn't have time to closely evaluate it yet. Thanks!

**Alf** · 08-03-2013, 01:05 PM

as it doesn't look like there was twice as much data

Sorry my bad. Using the original macro for a rerun will give you twice as much data in the target file unless you manually clear data first.

Adding the line "Activesheet.UsedRange.ClearContents" will first clear all data from target file before it starts importing the xlsx files. To me this is the safest method because then you know your data is most accurate i.e. any change of files/data that has happened after you previous run will be captured.

Alf

Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Re: Combine multiple (100+) excel files w/ one worksheet each into one giant worksheet

Thread Information

Users Browsing this Thread

Similar Threads

combining multiple different excel files with the same format in to 1 worksheet

Macro to combine many excel files into one workbook/worksheet

Excel 2010 Macro -how to split worksheet into multiple files

[SOLVED] Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet

Print specific worksheet from multiple excel files as batch

Bookmarks

Bookmarks

Posting Permissions