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 -->
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 -->
