Excel Macro run via command line

Question

I want to run excel macro via bash command Like: cscript fileformat.vbs I got the Error every time.

"C:\xampp\htdocs\magento\readcsvitem\fileformat.vbs(4, 48) Microsoft VBScript compilation error: Expected ')' "

Sub siddfinal1()
' siddfinal1 Macro
' Read Item format
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\xampp\htdocs\magento\readcsvitem\csvitem\CmCSVExport-final.csv", _
        Destination:=Range("$A$1"))
        .Name = "CmCSVExport-final"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.SaveAs Filename:="C:\xampp\htdocs\magento\readcsvitem\csvitem\Book1.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

You could do this quite easily with PowerShell. Why do you want to run a workbook macro via command line? Easiest way would be to create a VBS file that calls the workbook macro, and call the VBS file from command line instead. — SierraOscar

Leviathan Leviathan · Accepted Answer · 2016-05-06T08:06:34

What you have is VBA code. It is very similar to VBS, but there are some key differences: https://www.safaribooksonline.com/library/view/vbscript-in-a/1565927206/ch01s03.html

The error message you receive is caused by the named parameters you use for QueryTables.Add() - named parameters are not allowed in VBS. (4, 48) means the error occured in line 4 on character 48. That is the colon indication the named parameter.

The Refresh method also uses a named parameter, so does SaveAs. You can fix this by removing the parameter name and make sure they are noted in the correct order with optional parameters not used being left empty (skipped).

As ShaggyRogers already stated in his answer, ActiveWorkbook is not available in VBS since there is no Excel host application that you can take the active workbook from. You have to create a new Excel application first and then create the workbook you want to create the QueryTable in.

Also, the predefined constants you use to set properties for the QueryTable object (like xlInsertDeleteCells) are part from the Excel VBA core. Since that is not loaded you have no access to these constants. They must be replaced by their respective values.

I incorporated these changes and cleaned up the code a bit. This should work for you now:

Option Explicit

Const strcConnection = "TEXT;C:\xampp\htdocs\magento\readcsvitem\csvitem\CmCSVExport-final.csv"
Const strcOutputFile = "C:\xampp\htdocs\magento\readcsvitem\csvitem\Book1.csv"

Dim app
Dim wb

' create a new Excel instance
Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Add()

' create a new QueryTable
With wb.Sheets(1).QueryTables.Add(strcConnection, wb.Sheets(1).Range("A1"))
    .Name = "CmCSVExport-final"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = 1 ' xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = 1 ' xlDelimited
    .TextFileTextQualifier = 1 ' xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh False
End With

' save the data imported from the QueryTable as a *.csv file
wb.SaveAs strcOutputFile, 6 ' xlCSV

Excel Macro run via command line

1 Answers