VBA宏:将Visio Shape报表导出到新的Excel文件中，然后创建数据透视表 [英] VBA Macros: Exporting Visio Shape Report into New Excel File, then Creating a Pivot Table

问题描述

因此，基本上，我有一个Visio文件，其中包含许多形状和数据.我正在尝试将形状报告创建到新的excel文件中，然后让excel文件以编程方式将导出的数据转换为数据透视表.

So basically, I have a Visio file that has lots of shapes and data. I'm trying to create a shape report into a new excel file, and then have the excel file turn the exported data into a pivot table programmatically.

我有一个在Visio中运行的宏，该宏已经使用标准表格式的数据生成了excel文件.我希望能够在Visio中运行一个宏，该宏可以激活导出数据的excel窗口并运行一个宏以使其成为数据透视表.但是，我放入visio宏模块中的任何excel宏代码都无法识别(例如范围")，大概是因为它们不是Visio可以识别的单词.

I have a macro running in Visio that generates the excel file with data in normal table form already. I want to be able to run a macro in Visio that activates the excel window of exported data and runs a macro to make it into a pivot table. However, any excel macro code I put into my visio macro modules is unrecognized (e.g. "Range"), presumably because they're not words recognized by Visio.

我的问题是:如何运行从Visio模块更改Excel文件的宏?

My question is this: How do I run a macro that alters an Excel file FROM a Visio module?

我知道如何从Visio(Excel.run"ModuleNAME")在Excel文件中调用宏，但是这将要求宏已经存在于Excel中.由于我在获取形状报告时正在创建新的Excel文件，因此这些文件中没有任何宏.

I know how to call a macro IN an Excel file from Visio (Excel.run "ModuleNAME"), but that would require the macro to already be in Excel. Since I'm creating new Excel files when I get shape reports, these files don't have any macros in them.

一种替代解决方案是，如果我能够将Visio中的形状数据报告作为新工作表导出到已经包含该宏的EXISTING Excel文件中，但是我不确定如何执行此操作...(导出形状报告从Visio到现有的Excel文件)

An alternative solution would be if I was able to export shape data report from Visio as a new sheet to an EXISTING Excel file that already contained the macro, but I'm not sure how to do this either... (export shape reports from Visio to existing Excel file)

我的代码以生成Excel报告:

My Code to Generate Excel Report:

Sub Excel2()
Visio.Application.Addons("VisRpt").Run     
("/rptDefName=ReportDefinition_2.vrd/rptOutput=EXCEL")
End Sub

我想在运行Excel2()之后运行此宏

I would like to run this macro after running Excel2()

Sub NewMacro()
AppActivate "Microsoft Excel"
Dim AppExcel As Excel.Application
Set AppExcel = CreateObject("Excel.Application")

'Do all sorts of fancy stuff with making pivot tables
'Do all sorts of fancy stuff with making pivot tables
End Sub

推荐答案

您可以仅使用Get/CreateObject函数来使用Visio中的Excel应用程序实例.

You can just use the Get/CreateObject function to use an Excel application instance from Visio.

看看这篇文章:

http://msdn.microsoft.com/en-us/library/gg251785.aspx

您可能还会发现这很有用:

You might also find this useful:

http://support.microsoft.com/kb/309603

...它描述了相反的方向(即，从另一个应用程序控制Visio)，但是在您的情况下，您将执行以下操作:

...it describes the opposite direction (ie controling Visio from another app), but in your case you'd do something like:

Dim AppExcel As Excel.Application

   On Error Resume Next

   Set AppExcel = GetObject(, "excel.application")

   If AppExcel Is Nothing Then
      Set AppExcel = CreateObject("excel.application")
   End If

希望有帮助.

这篇关于VBA宏:将Visio Shape报表导出到新的Excel文件中，然后创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！