Merge 250 Excel files into one... 2

[YNOTU]

Hi all, I need some help badly.

I have about 250 files that I need merged into one. All the files contain exactly the same layout and amount of fields.

Can someone help me with merge code? or point me to where I can read how to accomplish this?

Thanks in advanced

 

[mikej336]

Some vary rough examples.

You need to lookup the FileSystemObject in vba help
then just put some code in in a loop like below and you are on your way.

Workbooks.Open FileName:="D:\my documents\excel\05118006C.xls"

Workbooks("05118006C.xls").Worksheets("Sheet1").activate

Cells.Select
Selection.Copy
Workbooks("book1").Worksheets("Sheet1").activate
Cells.Select
ActiveSheet.Paste

Good Luck

Uncle Mike

 

[YNOTU]

Hi Mike, Excuse my ignorance but does this line mean I should repeat for each file name? Workbooks.Open FileName:="D:\my documents\excel\05118006C.xls"

 

[vladk]

Just put all your excel files in one directory and use FileSystemObject to iterate thru the files. Like:

dim fso as new FileSystemObject
dim myFile as File
For each myFile in fso.getfolder(MyFolder).files
....
next myFile

 

[PHV]

And why not simply use the native Dir function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[YNOTU]

vladk, I'm sure your suggestion would work great but I'm sorry


I don't know what

dim fso as new FileSystemObject
dim myFile as File
For each myFile in fso.getfolder(MyFolder).files
....
next myFile

means and how it's used


PHV, I appreciate your suggestion but again but

I don't know what the

native Dir function is

 

[AnotherHiggins]

DeCojute,

Tek-Tips is a great place where experienced people share their knowledge with each other. But it is not a help desk. You are expected to do some work for yourself.

Have you done any research on the suggested methods?

If you are interested in learning how to do this, then try the following:

Turn on your Macro Recorder (Tools > Macro > Record New Macro) then combine a couple of files. Then post the code that is generated back here for help editing it. You can view the code by pressing [Alt]+[F11] while in Excel to bring up the Visual Basic Editor.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

http://www.rondebruin.nl/tips.htm

6th one down I would think.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ettienne]

If your Excel speadsheets are identical and you have field names in the first row you can use DAO (or ADO) to open the spreadsheets as tables, import them into an Access database, then export the result to an Excel file.

DIR works a bit like the DOS function, in the first DIR statement you give the file criteria you are searching for, let's say you are looking for *.xls in c:\temp:
if DIR("C:\Temp\*.xls") <> "" then
'Process first file
end if

DIR returns an empty string ("") if no match is found.

Next loop thru all files:

Do While DIR <> ""
'Process rest of the files
Loop

Note that no file criteria is specified for the second DIR statement, it repeats the search in the first DIR statement.

 

[YNOTU]

Thanks guys.