Convert VB Script in Excel to Standalone executable script

Question

I have written a VB script in the Excel module to export charts to a Word document, and it works correctly if I run the module in the Microsoft Visual Basic Application. But I need to write a standalone executable VBScript program so that I can invoke this script programmatically.

I have fixed some errors to make it standalone, but I am getting an expected statement error on line 57.

Below is the VB Script in Excel Module.

Sub ExportingToWord_MultipleCharts_Workbook()
    'Declare Word Variables
    Dim WrdApp As Object
    Dim WrdDoc As Object
    Dim SecCnt As Integer

    'Declare Excel Variables
    Dim ChrtObj As ChartObject
    Dim WrkSht As Worksheet
    Dim Rng As Range
    Dim ChrCnt As Integer
    
    ' Define paths to Excel and Word files
    excelFilePath = "D:\GIT\modules\core\bin\logs\AssessmentReport.xlsx"
    wordFilePath = "D:\WordDocument.docx"

    ' VBScript to read data from Excel and export tables to Word with formatting

    ' Create Excel and Word objects
    Set objExcel = CreateObject("Excel.Application")

    ' Open Excel workbook
    Set objWorkbook = objExcel.Workbooks.Open(excelFilePath)


'Create a new instance of Word
    Set WrdApp = CreateObject("Word.Application")
        WrdApp.Visible = True
        WrdApp.Activate
        
    'Create a new word document
    Set WrdDoc = WrdApp.Documents.Add
    
    ChrCnt = 0
    
    'Loop through all the worksheets in the Workbook that contains this code.
    For Each WrkSht In objWorkbook.Worksheets
    
        'Fix the instability error
        WrkSht.Activate
    
        'Loop through the charts on the active sheet
        For Each ChrtObj In WrkSht.ChartObjects
        
            'Copy the chart
            ChrtObj.Chart.ChartArea.Copy
            
            'Increment Chart Count
            ChrCnt = ChrCnt + 1
            
            'Fix the instability error
            Application.Wait Now + #12:00:01 AM#
    
            'Paste the Chart in the Word Document
            With WrdApp.Selection
                .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:=wdInLine
            End With
            
            'Count the pages in the Word Document
            SecCnt = WrdApp.ActiveDocument.Sections.Count
            
            'Add a new page to the document.
            WrdApp.ActiveDocument.Sections.Add

            'Go to the newly created page.
            WrdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext
            
           
            'Fix instability Errors
            Application.CutCopyMode = False
            
        Next ChrtObj
    
    Next WrkSht
    
     WrdApp.Selection.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.12", Filename _
            :="D:\GIT\modules\core\bin\logs\AssessmentReport.xlsx", LinkToFile:=False _
            , DisplayAsIcon:=True, IconFileName:= _
            "C:\WINDOWS\Installer\{90160000-000F-0000-1000-0000000FF1CE}\xlicons.exe" _
            , IconIndex:=1, IconLabel:="AssessmentReport.xlsx"
            
    WrdDoc.SaveAs wordFilePath

End Sub

Below is the updated version to run program standalone.

Sub ExportingToWord_MultipleCharts_Workbook()
    'Declare Word Variables
    Dim WrdApp, WrdDoc

    Dim strdocname
    On Error Resume Next
    Dim SecCnt

    'Declare Excel Variables
    Dim ChrtObj
    Dim WrkSht
    Dim Rng 
    Dim ChrCnt
    
    ' Define paths to Excel and Word files
    excelFilePath = "D:\GIT\modules\core\bin\logs\AssessmentReport.xlsx"
    wordFilePath = "D:\WordDocument.docx"

    ' VBScript to read data from Excel and export tables to Word with formatting

    ' Create Excel and Word objects
    Set objExcel = CreateObject("Excel.Application")

    ' Open Excel workbook
    Set objWorkbook = objExcel.Workbooks.Open(excelFilePath)


    'Create a new instance of Word
    Set WrdApp = CreateObject("Word.Application")
        WrdApp.Visible = True
        WrdApp.Activate
        
    'Create a new word document
    Set WrdDoc = WrdApp.Documents.Add
    
    ChrCnt = 0
    
    'Loop through all the worksheets in the Workbook that contains this code.
    For Each WrkSht In objWorkbook.Worksheets
    
        'Fix the instability error
        WrkSht.Activate
    
        'Loop through the charts on the active sheet
        For Each ChrtObj In WrkSht.ChartObjects
        
            'Copy the chart
            ChrtObj.Chart.ChartArea.Copy
            
            'Increment Chart Count
            ChrCnt = ChrCnt + 1
            
            'Fix the instability error
            Application.Wait Now + #12:00:01 AM#
    
            'Paste the Chart in the Word Document
            With WrdApp.Selection
                .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:=wdInLine
            End With
            
            'Count the pages in the Word Document
            SecCnt = WrdApp.ActiveDocument.Sections.Count
            
            'Add a new page to the document.
            WrdApp.ActiveDocument.Sections.Add

            'Go to the newly created page.
            WrdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext
            
            'Fix instability Errors
            Application.CutCopyMode = False
            
        Next ChrtObj
    
    Next WrkSht
    WrdDoc.SaveAs wordFilePath

End Sub

Error Message

Answer 1

You cannot use named arguments in VBScript.

So these:

With WrdApp.Selection
    .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:=wdInLine
End With

will need to be replaced using commas until the argument is reached in order.

Also the constants will need to either be manually declared or you would need to use their actual values.

Const wdInLine = 0
Const wdPasteOLEObject = 0

With WrdApp.Selection
    .PasteSpecial , True, wdInLine, , wdPasteOLEObject
End With

... or simply just use their values:

With WrdApp.Selection
    .PasteSpecial , True, 0, , 0
End With