时间戳

首页 > 解决方案 > VBScript 调用 VBA 宏来构建数据透视表

excel - VBScript 调用 VBA 宏来构建数据透视表

问题描述

我正在尝试执行以下操作：

  1. 使用 VBScript，将数据从 QlikView 导出到 Excel
  2. 使用 VBScript，触发 Excel 宏
  3. 有一个 Excel 宏，它根据从步骤 1 加载的数据构建数据透视表

我已成功导出数据（步骤 1）并触发了 Excel 宏（步骤 2）。

这个宏理论上应该实现第3步（如果我从Excel手动运行它，它会成功完成）。

但是，当在步骤 3 中触发的宏创建数据透视表时，它会失败并且在 QlikView 中，VBScript 消息是：

无法运行宏“procBuildPivot”。该工作簿中的宏可能不可用，或者所有宏都可能被禁用。

绝对不是这种情况，因为如果我直接在 Excel 中触发宏，则会构建数据透视表。或者，如果我从 QlikView 调用宏来执行任何操作（直到构建数据透视表，但不包括在内），它就可以工作。

我的 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

我的 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

独立地，VBA 工作（当直接在 Excel 中调用时）和 VBScript 工作以发送数据并调用 Excel 宏

问题似乎是当我尝试将两者结合并构建数据透视表时。

标签： excelvbavbscriptpivot-tableqlikview

解决方案

VB脚本：

在打开工作簿之前您无法获得工作表。您需要捕获工作簿对象，以便稍后关闭它。将工作簿名称添加到.Run通话中，以确保您到达您想去的地方。在释放对象句柄之前关闭并退出。

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：

集缺少“=”。使用您在数据透视表中创建的 pvtCache 对象。添加显式选项以捕获拼写错误。

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

推荐阅读