adding "ungroup sheets" to existing vbs code

Culwatrnca11
Culwatrnca11 used Ask the Experts™
on
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\xlsxtoxlsm.vbs" "C:\Batch_Automation\CCYY_MM_RUN_MMDDCCYY.xlsx" "C:\Batch_Automation\CCYY_MM_RUN_MMDDCCYY.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.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.Worksheets(1).Activate
oBook.Worksheets(2).Activate
oBook.Worksheets(3).Activate
oBook.Worksheets(4).Activate
oBook.Worksheets(5).Activate
oBook.Worksheets(6).Activate


oBook.SaveAs WScript.Arguments.Item(1), 52
oBook.Close False
oExcel.Quit

Thank you,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi,

Try activating just one worksheets instead of all.
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.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(WScript.Arguments.Item(0))
oBook.Worksheets(1).Activate
oBook.Worksheets(2).Activate
oBook.Worksheets(3).Activate
oBook.Worksheets(4).Activate
oBook.Worksheets(5).Activate
oBook.Worksheets(6).Activate

If ActiveWindow.oBook.Worksheets.Count > 1 Then
    ActiveWindow.oBook.Worksheets(1).Select
End If

oBook.SaveAs WScript.Arguments.Item(1), 52
oBook.Close False
oExcel.Quit

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
another approach would be:
Dim oExcel
Dim oBook
Dim oFSO

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

Set oExcel = CreateObject("Excel.Application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oBook = oExcel.Workbooks.Open(oFSO.GetAbsolutePathName(Wscript.Arguments.Item(0)))
oBook.Worksheets(1).Activate
oBook.SaveAs oFSO.GetAbsolutePathName(WScript.Arguments.Item(1)), 52
oBook.Close False
oExcel.Quit

Open in new window

Culwatrnca11Data Analyst

Author

Commented:
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'
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Please paste your full code
Culwatrnca11Data Analyst

Author

Commented:
figured it out..   missed something in my cut and paste.   Thank you again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial