Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Merge / combine multiple Excel Spreadsheets - possible?


(!)

Reconsniper1's Avatar
Reconsniper1 Reconsniper1 is offline
Junior Member with 15 posts.
THREAD STARTER
 
Join Date: Apr 2008
30-Jul-2008, 11:37 PM #1
Merge / combine multiple Excel Spreadsheets - possible?
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
31-Jul-2008, 05:10 AM #2
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's Avatar
Reconsniper1 Reconsniper1 is offline
Junior Member with 15 posts.
THREAD STARTER
 
Join Date: Apr 2008
31-Jul-2008, 01:32 PM #3
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
31-Jul-2008, 02:07 PM #4
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
31-Jul-2008, 03:31 PM #5
That sounds like a find unmatched query that is being converted into an append query.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
31-Jul-2008, 04:10 PM #6
Yeah - in Access it would take what, 15 minutes at the most?
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
31-Jul-2008, 04:20 PM #7
Aye at the most.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
31-Jul-2008, 05:56 PM #8
Arrrrgh, that would be a fine bit of treasure, that method....
Reconsniper1's Avatar
Reconsniper1 Reconsniper1 is offline
Junior Member with 15 posts.
THREAD STARTER
 
Join Date: Apr 2008
07-Aug-2008, 11:29 PM #9
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's Avatar
sohel08 sohel08 is offline
Computer Specs
Junior Member with 10 posts.
 
Join Date: Jul 2008
Experience: Computer Illiterate
07-Aug-2008, 11:56 PM #10
comtent
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's Avatar
Reconsniper1 Reconsniper1 is offline
Junior Member with 15 posts.
THREAD STARTER
 
Join Date: Apr 2008
08-Aug-2008, 12:11 AM #11
Thanks for repeating part of my question again
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
08-Aug-2008, 12:55 AM #12
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
08-Aug-2008, 08:06 AM #13
Quote:
Originally Posted by Reconsniper1 View Post
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's Avatar
Reconsniper1 Reconsniper1 is offline
Junior Member with 15 posts.
THREAD STARTER
 
Join Date: Apr 2008
12-Aug-2008, 07:42 PM #14
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's Avatar
Computer Specs
Member with 11,731 posts.
 
Join Date: Feb 1999
Location: Allentown, PA
Experience: Intermediate
12-Aug-2008, 10:22 PM #15
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑