Macro to copy all spreadsheets into 1 spreadsheet

Macro to copy all spreadsheets into 1 spreadsheet

Post by RHRvd24gRG » Wed, 17 May 2006 03:13:02


Hello.
I have a folder that has 300 excel spreadsheets in it. I take these 300
spreadsheets and combine them into 1 spreadsheet buy going to run>"cmd"
prompt and then using dos commands to copy all 300 into 1 spreadsheet.

Is there a macro that will do this all for me so I don't have to everytime
go out to "cmd" prompt and type the dos commands?
 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by Ron de Bru » Wed, 17 May 2006 03:34:38

You can find a example here that use Shell
http://www.yqcomputer.com/


--
Regards Ron de Bruin
http://www.yqcomputer.com/

 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by RHRvd24gRG » Fri, 19 May 2006 00:33:01

Thank you. Thank You.

Although I'm having trouble changing the code so that it will save it in a
different location other than My Documents. Can you help.

I really need to do somethign to pay you back. Your site is wonderful. Is
there anything I can do to support you?
 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by Ron de Bru » Fri, 19 May 2006 00:50:53

Hi Dtown

You can change
DefPath = Application.DefaultFilePath

To this for example

DefPath = "C:\Data"



Making you happy is my paycheck
Have fun with Excel and enjoy all the good stuff in the newsgroups


--
Regards Ron de Bruin
http://www.yqcomputer.com/
 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by RHRvd24gRG » Wed, 24 May 2006 02:44:01

Ron.
One more question if you don't mind?
Can I specify in the code to only copy specific files from the folder.
Right now the code copies all the .csv files in the folder I choose.

For example, can I specify to only copy the following files
*softwareinstalled.csv

Everything else is working fine. I appreciate your help.
 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by Ron de Bru » Wed, 24 May 2006 03:13:06

Hi Dtown

Yes try it

Change this line
Print #1, "Copy " & Chr(34) & foldername & "*.csv" & Chr(34) & " " & TXTFileName

--
Regards Ron de Bruin
http://www.yqcomputer.com/
 
 
 

Macro to copy all spreadsheets into 1 spreadsheet

Post by RHRvd24gRG » Fri, 09 Jun 2006 01:11:04

nstead of having teh macro prompt me for the location of the files, can I
change the code so that it automatically goes to a specific location without
asking?
Sorry for all the questions. I'm starting a class in one week on this stuff
so hopefully I can do it on my own.

"Ron de Bruin" wrote: