Combine all files in a folder X 89!

[Fu On Li]

Hi All

I am going to start from scratch here

Do anyone know how to create a macro that will combine all files (xlsm) in a folder and save but for 89 separate folders???

eg

I have Folder1, Folder2, Folder3 etc 89 in total

Each folder has files Folder1A, Folder1B, Folder1C etc (xlsx)

I want to combine all files in Folder1, then save to a new workbook, move on to Folder2, combine every thing then save to anyother workbook for 89 time :S

Any Suggestions???

Many Thanks in Advance

 

[carden2]

try something like this just running a dos command from a macro this just looks in a folder copies all the files with that extension into a .xlsm file. you can either run a loop to do all the folders or just manually put each folder number in there and file number.

Dim MyCommand As String
Dim TaskId As Integer

MyCommand = "CMD /C Copy C:\path\foldername\*.xlsm C:\filename.xlsm/b"
TaskId = Shell(MyCommand, 1)

 

[Fu On Li]

Hi Carden2

Many thanks for your suggestion, however I am not from a computing background hence I don't fully your recommendation

Apologies on my incompitence

How do I run and how dose the 'MyCommand' work?

What does the Shell function do?

Thanks in advance

 

[carden2]

try something like this just running a dos command from a macro this just looks in a folder copies all the files with that extension into a .xlsm file. you can either run a loop to do all the folders or just manually put each folder number in there and file number.

Dim MyCommand As String
Dim TaskId As Integer

MyCommand = "CMD /C Copy C:\path\foldername\*.xlsm C:\filename.xlsm/b"
TaskId = Shell(MyCommand, 1)

sub macroname()

Dim MyCommand As String
Dim TaskId As Integer

MyCommand = "CMD /C Copy C:\path\foldername\*.xlsm C:\filename.xlsm/b"
TaskId = Shell(MyCommand, 1)

end sub

just paste that in your macro and fill in the path's and file names according to what files your working on. It takes all files ending with a .xlsm in a folder and copies them together into a .xlsm file.

 

[Fu On Li]

sub macroname()

Dim MyCommand As String
Dim TaskId As Integer

MyCommand = "CMD /C Copy C:\path\foldername\*.xlsm C:\filename.xlsm/b"
TaskId = Shell(MyCommand, 1)

end sub

just paste that in your macro and fill in the path's and file names according to what files your working on. It takes all files ending with a .xlsm in a folder and copies them together into a .xlsm file.

Here what I've done

Sub macroname()

Dim MyCommand As String
Dim TaskId As Integer

MyCommand = "CMD /C Copy G:\Year\All\Group\Aintree\*.xlsm G:\Aintree.xlsm/b"
TaskId = Shell(MyCommand, 1)

End Sub

It save a file called Aintree.xlsm that wouldn't open/corrupted and it seems to be blank as it only has 11kb?

Am I missing something obvious??? BTW all the files has the same headings in the first row, how do i get it to copy the data only and not the headings as well???

Thanks in advance