VBScript Calling VBA Macro to build Pivot Table

Question

I am trying to do the following:

Using VBScript, export data from QlikView into Excel
Using VBScript, trigger Excel Macro
Have an Excel Macro that builds a Pivot Table based on the data loaded from Step 1

I have successfully exported the data (step 1) and triggered an Excel Macro (Step 2).

This macro should theoretically achieve Step 3 (if I run it manually from Excel, it completes successfully).

However, when the Macro that is triggered in Step 3 creates the Pivot Table, it then fails and in QlikView, the VBScript message is:

Cannot run the macro 'procBuildPivot'. The macro may not be available in this workbook or all macros may be disabled.

This is definitely not the case as, if I trigger the macro directly in Excel, the pivot table is built. Alternatively, if I call a macro from QlikView to do anything (up to and excluding building the pivot table) it works.

My VBScript:

Set objExcelApp = CREATEOBJECT("Excel.Application")

Set objExcelSheet = objExcelApp.Worksheets("Sheet 1")

objExcelApp.Workbooks.Open "Test.xlsm"

objExcelApp.Application.Run "procBuildPivotReport"

objExcelSheet.SaveAs "Test2.xlsm"

SET objExcelSheet = NOTHING
SET objExcelApp = NOTHING

My VBA:

Sub procBuildPivotReport()

Dim wb As Workbook
Set wb = ThisWorkbook

Dim ws as Worksheet
Set ws as ActiveSheet

Dim pvtCache as PivotCache
Dim pvt as PivotTable

Set pvtCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("QV Data").Range("A1:D10"), Version:=6)
Set pvt = ws.PivotTables.Add(PivotCache, TableDestination:=Range("J1"), TableName:= "Sales Analysis")

With pvt
    .PivotFields("Project ID").Orientation = xlRowField
    .PivotFields("Name").Orientation = xlColumnField
    .PivotFields("Hours").Orientation = xlDataField
Emd With

End Sub

Independently, the VBA works (when called directly inside Excel) and the VBScript works to send data and call an Excel Macro

The issue appears to be when I try and combine the two and build a pivot table.

Try objExcelApp.Application.Run "'" & excelPath & "'!procBuildPivotReport". If a Sub needs parameters, they can follow after the comma... Replace objExcelApp.Workbooks.Open "Test.xlsm" with objExcelApp.Workbooks.Open excelPath. excelPath is, of course the excel workbook full path... Do the same for objExcelSheet.SaveAs "Test2.xlsm". — FaneDuru
Thank you @BigBen for the reference, and @FaneDuru for the suggestion. Sadly neither did the trick. The VBA macro to build the Pivot table definitely triggers all the way until after the Set pvt.. call as after it fails, i can open the unsaved - but edited - file and see the frame of a pivot table. — Paul van Schalkwyk
You should use a String for the SourceData argument of PivotCaches.Create. From the docs: "When passing a Range object, we recommend that you either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly" — BigBen
You should include Option Explicit - this forces you to declare all variables. — BigBen

HackSlash HackSlash · Accepted Answer · 2020-02-25T20:28:18

VBScript:

You can't get a sheet before opening the workbook. You need to capture the workbook object so you can later close it. Add the workbook name to the .Run call to ensure you get where you want to go. Close and quit before releasing the object handles.

workbookPath = "Test.xlsm"

Set objExcelApp = CREATEOBJECT("Excel.Application")
Set thisWorkBook = objExcelApp.Workbooks.Open workbookPath
Set objExcelSheet = thisWorkBook.Worksheets("Sheet 1")

objExcelApp.Application.Run "'" & workbookPath & "'!procBuildPivotReport"

objExcelSheet.SaveAs "Test2.xlsm"

thisWorkBook.Close False
objExcelApp.Quit
Set objExcelSheet = Nothing
Set thisWorkBook = Nothing
Set objExcelApp = Nothing

VBA:

Set was missing "=". Use the pvtCache object you created in the pivot table. Add option explicit to catch typos.

Option Explicit
Sub procBuildPivotReport()

    Dim wb As Workbook
    Set wb = ThisWorkbook

    Dim ws as Worksheet
    Set ws = wb.ActiveSheet

    Dim pvtCache as PivotCache
    Set pvtCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("QV Data").Range("A1:D10"), Version:=6)
    Dim pvt as PivotTable
    Set pvt = ws.PivotTables.Add(pvtCache, TableDestination:=Range("J1"), TableName:= "Sales Analysis")

    With pvt
        .PivotFields("Project ID").Orientation = xlRowField
        .PivotFields("Name").Orientation = xlColumnField
        .PivotFields("Hours").Orientation = xlDataField
    Emd With

End Sub

VBScript Calling VBA Macro to build Pivot Table

1 Answers