使用Access Table作为源在VBA中创建Excel Pivot [英] Create Excel Pivot in VBA with Access Table as source

问题描述

我正在编写一些VBA，以使用现有的本地表在新工作表中创建Excel数据透视表。


由于卷，我不想在工作簿中存储原始数据（在另一张纸上并隐藏它）。


对SourceType：=的任何想法和SourceData：=参数，或工作模型的一个例子？


谢谢。

I am writing some VBA to create an Excel pivot table in a new worksheet using an existing local table.

Because of volumes, I don''t want to store the original data (on another sheet and hide it) within the workbook.

Any ideas for the "SourceType:=" and "SourceData:=" parameters, or an example of a working model?


Thanks.

推荐答案

我还没有使用Pivot表，但Excel的一个好处是可以通过录制你的工作轻松地在（VBA）宏中创建代码。


我建议你录制一个宏并使用Access作为存储库设置一个noddy数据透视表。你应该从中找到你需要的东西。


告诉我们你是如何上场的。

I haven''t worked with Pivot tables, but one of the nice things about Excel is the ease with which you can create code in (VBA) macros by recording your work.

I suggest you record a macro and set up a noddy pivot table using Access as a repository. You should find out easily enough from that what you need.

Let us know how you get on.

我确实录制了一个''pivot在Excel中创建并传输它，但是当我运行VBA代码时它并不喜欢它。


重新阅读我原始编写的内容，并且只是为了澄清： br />

我正在Access中编写VBA，运行一个模块，该模块将使用模块所在的同一数据库中的表创建数据透视表（在Excel中）。


我可以轻松地在Excel中创建数据然后转动它，但我不想因为数量很大而将所有数据存储在另一张表中。理想情况下，我只想把它的轴与表连接起来。


希望现在很清楚。

I did record a ''pivot create'' in Excel and transferred it, but when I ran the VBA code it didn''t like it.

Re-reading what I orginally wrote, and just to clarify:

I''m writing VBA in Access, to run a module which will create the pivot (in Excel) using a table in the same database that the module will reside.

I can easily create the data in Excel and then pivot it, but I don''t want to store all the data in another sheet because of high volumes. Ideally I just want the pivot with it linked to the table.

Hope that''s clear, now.

@ redman08


查看应用程序自动化。应该提到本机（Excel）代码与从另一个应用程序（EG.Access）运行的代码不同。 Excel中有默认引用，这些引用在Access中不可用，因此代码需要明确引用（Excel中不需要的地方）。
@ redman08


我之前说过我对数据透视表没有直接的了解。


你能创建一个链接到Access表吗？如果是这样，我建议你这样做，然后检查决定它如何连接的位的结果（属性等）。


我害怕我只能指出你那个方向。其他人可能会给出更准确的说明，但我认为这应该足够了。

@redman08
Have a look in Application Automation. It should mention that native (Excel) code is a different from that which runs from another application (EG. Access). There are default references in Excel which are not available in Access, so the code needs to refer to the explicitly (where it doesn''t need to in Excel).
@redman08
I said earlier I have no direct idea about Pivot Tables.

Can you create one that links to an Access table? If so, I suggest you do so then examine the results (properties etc) for the bit that determines how it is connected.

I''m afraid I can only really point you in that direction. Someone else may be able to give more precise instructions, but I expect this should be enough.

这篇关于使用Access Table作为源在VBA中创建Excel Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！