VBA 宏：将 Visio 形状报告导出到新的 Excel 文件中，然后创建数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11729959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Macros: Exporting Visio Shape Report into New Excel File, then Creating a Pivot Table
提问by ays0110
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 文件中，然后让 excel 文件以编程方式将导出的数据转换为数据透视表。
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 文件。我希望能够在 Visio 中运行一个宏来激活导出数据的 excel 窗口并运行一个宏以使其成为数据透视表。但是，我放入 visio 宏模块的任何 excel 宏代码都无法识别（例如“范围”），大概是因为它们不是 Visio 识别的单词。
My question is this: How do I run a macro that alters an Excel file FROM a Visio module?
我的问题是：如何运行从 Visio 模块更改 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 的 Excel 文件中调用宏（Excel.run“ModuleNAME”），但这需要宏已经在 Excel 中。由于我在获取形状报告时正在创建新的 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)
另一种解决方案是，如果我能够将 Visio 中的形状数据报告作为新工作表导出到已包含宏的现有 Excel 文件中，但我也不知道如何执行此操作...（从Visio 到现有的 Excel 文件）
My Code to Generate Excel Report:
我生成Excel报告的代码：
Sub Excel2()
Visio.Application.Addons("VisRpt").Run
("/rptDefName=ReportDefinition_2.vrd/rptOutput=EXCEL")
End Sub
I would like to run this macro after running Excel2()
我想在运行 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
采纳答案by JohnGoldsmith
You can just use the Get/CreateObject function to use an Excel application instance from Visio.
您可以仅使用 Get/CreateObject 函数来使用 Visio 中的 Excel 应用程序实例。
Have a look at this article:
看看这篇文章：
http://msdn.microsoft.com/en-us/library/gg251785.aspx
http://msdn.microsoft.com/en-us/library/gg251785.aspx
You might also find this useful:
您可能还会发现这很有用：
http://support.microsoft.com/kb/309603
http://support.microsoft.com/kb/309603
...it describes the opposite direction (ie controling Visio from another app), but in your case you'd do something like:
...它描述了相反的方向（即从另一个应用程序控制 Visio），但在您的情况下，您会执行以下操作：
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
Hope that helps.
希望有帮助。
