Culwatrnca11
asked on
adding "ungroup sheets" to existing vbs code
Found this code for converting XLSX to XLSM which works great on a different project. With this project when I open the excel file I need to "ungroup sheets" in the workbook then save_as xlsm. There are about 6 tabs total if that matters.
I execute it in the same directory as the excel file via a batch file:
start /min /wait "c:\Batch_Automation\xlsxt oxlsm.vbs" "C:\Batch_Automation\CCYY_ MM_RUN_MMD DCCYY.xlsx " "C:\Batch_Automation\CCYY_ MM_RUN_MMD DCCYY.xlsm "
Question: How do you add ungroup sheets to this code?
File name: XlsxToXlsm.vbs
------
if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsxToxlsm SourcePath.xlsx Destination.xlsm"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Applic ation")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscr ipt.Argume nts.Item(0 ))
oBook.Worksheets(1).Activa te
oBook.Worksheets(2).Activa te
oBook.Worksheets(3).Activa te
oBook.Worksheets(4).Activa te
oBook.Worksheets(5).Activa te
oBook.Worksheets(6).Activa te
oBook.SaveAs WScript.Arguments.Item(1), 52
oBook.Close False
oExcel.Quit
Thank you,
I execute it in the same directory as the excel file via a batch file:
start /min /wait "c:\Batch_Automation\xlsxt
Question: How do you add ungroup sheets to this code?
File name: XlsxToXlsm.vbs
------
if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsxToxlsm SourcePath.xlsx Destination.xlsm"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Applic
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscr
oBook.Worksheets(1).Activa
oBook.Worksheets(2).Activa
oBook.Worksheets(3).Activa
oBook.Worksheets(4).Activa
oBook.Worksheets(5).Activa
oBook.Worksheets(6).Activa
oBook.SaveAs WScript.Arguments.Item(1),
oBook.Close False
oExcel.Quit
Thank you,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Shums,
On the first solution I get script error.. line 18 char 1 error Object Required: 'activeWindow'
On the 2nd solution I get script error line 1 char 1 error type mismatch: 'im'
On the first solution I get script error.. line 18 char 1 error Object Required: 'activeWindow'
On the 2nd solution I get script error line 1 char 1 error type mismatch: 'im'
Please paste your full code
ASKER
figured it out.. missed something in my cut and paste. Thank you again
Open in new window