使用VBA添加或更改Power Pivot数据连接 [英] Using VBA To Add or change Power Pivot data connection

问题描述

即使搜索并尝试了很长时间，我也不知道是否可能。

Even after searching and trying for a long time I am not sure whether it is possible it or not.

我有一个使用SQL源的PP模型。假设命令是：

I have a PP model using SQL source. Let's say command is:

select * from FactInternetSales where CustomerKey  = 11001

，我有过滤器 CustomerKey，并且基于过滤器选择，我想更新命令。例如：

and I have filter, 'CustomerKey' and based on the filter selection I want to update the command. For e.g:

select * from FactInternetSales where CustomerKey  = 11002

我正在尝试的代码是：

Dim mdl As ModelTable
    Dim wcon As WorkbookConnection
    Dim cmd As String

    Set mdl = ActiveWorkbook.Model.ModelTables("Customers")
    Debug.Print mdl.SourceWorkbookConnection.OLEDBConnection.CommandText
    mdl.SourceWorkbookConnection.OLEDBConnection.CommandText = "select * from      FactInternetSales where CustomerKey  = 11002" 

尝试分配命令会导致运行时错误1004

Trying to assign a command results in 'Run-time error 1004'

我只是遇到了这个链接，这似乎是有可能的。但是我没有得到临时表部分。请分享您用于解决此问题的意见（和VBA）代码。

I just come across this link, it seems possible. But I am not getting the 'temporary tables' part. Please share your opinion (and the VBA) code you have used to address this.

谢谢，
Prakash

Thanks, Prakash

推荐答案

很长一段时间后，我在这里看到了自己的问题。我已经找到了解决方案，所以可以回答我自己的问题。

I see my own question here after a long time. I have already found the solution so answering my own question.

解决方案是，需要从Excel数据菜单中创建连接并从那里加载/导入数据（ 仅创建连接和将此数据添加到数据模型）。如果使用PP窗口创建创建，则数据模型被标记为只读。另外，如果从Excel数据菜单中导入后重命名/添加了表/字段，则数据模型被标记为只读，但是在关系和度量创建/添加方面没有问题。

The solution is that the connection need to be created from Excel Data menu and load/import the data from there ('Only Create Connection' and 'Add this data to Data Model'). The Data Model is marked read-only if the creation is created using PP window. And also if the tables/fields are renamed/added after importing from Excel Data menu, the Data Model is marked read-only but no problem in relationship and measures creation/addition.

谢谢

这篇关于使用VBA添加或更改Power Pivot数据连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！