# Merge / combine multiple Excel Spreadsheets - possible?



## Reconsniper1 (Apr 17, 2008)

Hello!

Here is my current project / problem. 

I have several Excel Spreadsheets. Lets call them text.xls, test1.xls, test2.xls, and test3.xls

All 4 spreadsheets have the same columns (date / name / amount / cause / solution).

Is it possible to make a VB script within Excel that would copy the contents of all 4 spreadsheets and put them in a one .xls. If that is indeed possible, is there a way to also tell it not to copy the stuff already on the spreadsheet so there are no duplicates. 

Thanks,
T


----------



## OBP (Mar 8, 2005)

T, yes it can be done by a VBA macro. is this a "one off" requirement or do you need to do this regularly?


----------



## Reconsniper1 (Apr 17, 2008)

I'm unfamiliar with the one off terminology but I would say that I would run it monthly or whenever needed. I know I can just copy and paste the information but I would rather have it automatically done. I took VB in high school but that was so long ago that it has been forgotten. Any help would greatly be appreciated..

Thanks!


----------



## slurpee55 (Oct 20, 2004)

It would be possible to do this even easier, perhaps, in Access (I know OBP could help you with this too) and then export them back to Excel.


----------



## jimr381 (Jul 20, 2007)

That sounds like a find unmatched query that is being converted into an append query.


----------



## slurpee55 (Oct 20, 2004)

Yeah - in Access it would take what, 15 minutes at the most?


----------



## jimr381 (Jul 20, 2007)

Aye at the most.


----------



## slurpee55 (Oct 20, 2004)

Arrrrgh, that would be a fine bit of treasure, that method....


----------



## Reconsniper1 (Apr 17, 2008)

I know I can make tables in Access and then just do Queries but I'm wanting to merge Excel spreadsheets so I can have them in separate locations on the hard drive. So can anyone help on this?


----------



## sohel08 (Jul 9, 2008)

Is it possible to make a VB script within Excel that would copy the contents of all 4 spreadsheets and put them in a one .xls. If that is indeed possible, is there a way to also tell it not to copy the stuff already on the spreadsheet so there are no duplicates.


----------



## Reconsniper1 (Apr 17, 2008)

Thanks for repeating part of my question again


----------



## slurpee55 (Oct 20, 2004)

This will get you halfway there - it is VBA that will combine all the worksheets in a directory into one worksheet (or more, if you exceed the size limits of Excel).
http://www.vbaexpress.com/kb/getarticle.php?kb_id=773
Then you can use this to delete duplicates
http://www.vbaexpress.com/kb/getarticle.php?kb_id=520


----------



## jimr381 (Jul 20, 2007)

Reconsniper1 said:


> Thanks for repeating part of my question again


Be nice, he is just rephrasing or brainstorming. We are all trying to help you here.


----------



## Reconsniper1 (Apr 17, 2008)

I meant no disrespect by that.. It just confused me. 

Thanks for the VBA info. Do you know if there is any way to tell it to pull the .xls file from different directories rather than it having to be all in the same directory?

Thanks,
T


----------



## Anne Troy (Feb 14, 1999)

Reconsniper1: I suggest you go join VBA Express and ask your questions there. You might like the site as much as I do...even tho TechGuy has my roots.

And sohel08 confused me, too. I actually thought it was you logged in under a second name.

I was Recon, too. VQ2 (Navy). A VERY long time ago.


----------



## slurpee55 (Oct 20, 2004)

Hey Anne, congrats on the MVP - long and well deserved!


----------



## jimr381 (Jul 20, 2007)

Anne Troy said:


> Reconsniper1: I suggest you go join VBA Express and ask your questions there. You might like the site as much as I do...even tho TechGuy has my roots.
> 
> And sohel08 confused me, too. I actually thought it was you logged in under a second name.
> 
> I was Recon, too. VQ2 (Navy). A VERY long time ago.


I would have guessed Navy.  We have a former squid (just kidding) that works here that reminds me of you.


----------



## Anne Troy (Feb 14, 1999)

LOL!! In what way, Jim?


----------



## jimr381 (Jul 20, 2007)

Anne Troy said:


> LOL!! In what way, Jim?


You sometimes come of gruff initially, then you get warmed up to and she is the same way.


----------



## slurpee55 (Oct 20, 2004)

As long as you say "Yes Ma'am!!!", huh Jim?


----------



## jimr381 (Jul 20, 2007)

No, not I. I treat her the same way as I treat all my other users. I tell them I do not care if it is a LTG or an E1 I will still deliver the best I can to them.


----------



## slurpee55 (Oct 20, 2004)

Having absolutely no military experience, well, uh....okay....


----------



## jimr381 (Jul 20, 2007)

A LTG is a 3 star general. Think of him as a tier under the big big cheese.


----------



## slurpee55 (Oct 20, 2004)

Hmmm, The Michelin Guide only goes up to 3 stars....


----------



## Keebellah (Mar 27, 2008)

I think you could have an extra sheet as an Admin sheet where the list op locations is kept and use this in the vba code to retrieve them,
In this manner all you have to do is add or remove locations without having to change the code avery time a new sheet is added.
I use this method for everything that is suceptible to vary and would need code to be altered.


----------

