Combining Workbooks with different file extensions

[matt1112]

Hello,

Each day i receive a zip file containing multiple excel workbooks with differing file extensions (.csv, .dat,. and .xls). Some workbooks contains only one worksheet while others contain multiple. Each workbook's file name changes on a day to day basis.

I'm looking to create a macro to combine all worksheets from these workbooks into one workbook.

I've been searching for a way to do this but haven't been able to find a VBA code for my particular situation.

Please help.

Thanks,

Matt

[patel]

attach please a sample zip file and desired result

[matt1112]

Hello Patel,

Thanks for your response. Please see the attached example.

I want to combine all the worksheets that are included in the the zip folder into one workbook, as shown in "Sample 1 Combined Workbook".

I receive the zip folders by email from an automated source. Ideally, i would like to combine only the workbooks that i open or ones I place into a a new file path.

Please let me know if any more info is needed.

Thanks again,

Matt

[westconn1]

Set sh = CreateObject("shell.application")
Set n = sh.namespace("c:\temp") ' change to suit
Set combined = Workbooks.Add
combined.SaveAs "Combined workbook.xlsx" ' supply full path
For Each itm In n.Items
If Right(itm.Name, 4) = ".zip" Then Set n = sh.namespace(itm.Path): Exit For
Next
For Each itm In n.Items
Set wb = Workbooks.Open(itm.Path)
For Each sht In wb.Sheets
sht.Copy , combined.Sheets(combined.Sheets.Count)
Next
wb.Close False
Next
combined.Save
combined.close ' or notthis assumes that your zip file is the only zip in the folder, also that all files in workbook will open in excel as workbooks
the existing sheets in new workbook will still exist unless deleted
i have not tested the code in full