Excel save as .xlsx from .xlsm file extension

Question

I am trying to write a VBScript to open an excel. Run the macro and Save it as xlsx. Post this close the excel document.

Pardon my knowledge of VBScript as I am still novice and lack detailed knowledge of VBScript.

However, this is important and the piece of code I have written is not able to help me out.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Desktop\Report0165.xlsm")

objExcel.Application.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

objExcel.Application.Run "Report0165.xlsm"
objExcel.ActiveWorkbook.SaveAs "Report0165.xlsx", FileFormat = 51
objExcel.ActiveWorkbook.Close saveChanges=True

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

I am trying and still I am unable to do this basic things: I need to execute all the macros inside the excel. Hence I have not specified the macro name.

a. Open excel with macro

b. run macro

c. Save the xlsm to xlsx

d. close the document

Can anyone please guide on where am I going wrong.

brettdj brettdj · Accepted Answer · 2015-08-11T06:33:40

This code should get you there other than in my test it opened up the xlsm with macros disabled.

Pls note I have changed the path
You don;t need to make Excel visible when automating
Turning off alerts stops the prompt when you overwrite Report0165.xlsx
FileFormat can't be used in a vbs
your code wasn't specifying a macro to be run inside Report0165

updated as apparently OP didn't want the new book that question adds with objExcel.Workbooks.Add

*code*


    Set objexcel = CreateObject("Excel.Application")
    Set objWorkbook = objexcel.Workbooks.Open("C:\temp\Report0165.xlsm")

    'Objexcel.Application.Visible = True
    Objexcel.DisplayAlerts = false
    objWorkbook.Sheets(1).Cells(1, 1).Value = "Test value"

    'objExcel.Run "Report0165.xlsm!Test"
    objWorkbook.SaveAs "C:\temp\Report0165.xlsx", 51
    objWorkbook.Close

    Objexcel.DisplayAlerts = true

    objexcel.Application.Quit
    WScript.Echo "Finished."
    WScript.Quit

Excel save as .xlsx from .xlsm file extension

1 Answers