Combining multiple workbooks into 1 new workbook with multiple sheets

DennisL2

New Member
Joined
Mar 6, 2009
Messages
1
I found this VBscript (below) posted on http://www.MrEXCEL.com/forum/showthread.php?t=298659 .
It appears that this will suit my need to combine multiple workbooks with a single sheet into 1 new workbook with multiple sheets (tabs).
However, when I execute it (via Wscript or Cscript), I encounter a VBscript Compilation Error (code 800A0401): Expected End of Statement at line 2, char 11.
The Dim statement seems to be fine. What am I doing wrong? Occurs on 2 PC's running MS Windows Script Host Version 5.6 & 5.7 .

My multiple spreadsheets are all identical CSV data files - is there any way to get Excel to import and CONVERT them on-the-fly from CSV format using only the semicolon (;) as the delimiter? The resulting worksheets rows should be properly parsed with one value in each column.

Also, will the script work correctly if I set 'MyPath' to = "." ? (Seems that it should.) In this context/application, the single period represents any current directory.
This would make the script dynamic and portable to run from any folder and process all the spreadsheets collected there.

Thank you in advance for a reply - I am in a pinch to automate this Excel merge task somehow.

DennisL2

----- your VBscript code ------------------

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\MyPath" ' change to suit
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End <!-- / message -->
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,191,398
Messages
5,986,361
Members
440,020
Latest member
IfsandSums

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top