使用Excel VBA将Powerpivot连接更改为csv文件 [英] Use Excel VBA to change Powerpivot connection to csv file

问题描述

如何使用 vba 修改与csv文件的 Powerpivot 连接？我有一个Excel工作簿与Powerpivot模型连接到csv文件。在Powerpivot窗口中，我可以导航到Home-> Existing Connections，然后根据需要编辑文件路径。我无法在vba中接近编辑。我发现此链接参数化Powerpivot连接。

不幸的是，我不知道如何修改以应用于csv文件连接而不是数据库连接。

另一种方法在此页面。此方法创建与Excel中的数据的连接。然后连接在Powerpivot中可用。在Powerpivot bc中创建连接至关重要，笔者认为在Powerpivot中创建的连接不能被vba修改。

我可以通过
Data->获取外部数据 - > ;从文本菜单选项。

我可以使用Workbooks（myWorkbook.xlsm）在vba中创建相同的连接。Connections.AddFromFiledata.csv我可以手动编辑此连接数据 - >连接 - >连接对话框。但是，当我尝试使用WorkbookConnections.TextConnection属性在vba中进行设置时，vba表示对象不支持此属性或方法。

上面的链接专注于数据库连接而不是csv文件连接，但是似乎有可能使用
Microsoft ACE OLEDB 12.0连接来设置csv文件，但是我无法完全掌握它。

在SO上有类似的问题，解决vba修改枢轴来源（如下所示：）。

但是，我不想将数据带入Excel中的表格，我想连接到我可以在Powerpivot中使用它。

我可以用Power Query来做这个，但是由于我已经有了所需格式的csv，我宁愿不必再介绍这个额外的步骤。

我在Windows 7上使用Excel 2013 64位。谢谢你的帮助。

解决方案

我可以通过将csv文件连接设置为Microsoft.ACE.OLEDB.12.0连接来修改文件位置。它在数据连接向导中实际上标有Microsoft Office 12.0 Access数据库引擎OLE DB提供程序。这个 link 帮我弄清楚了。您需要添加Microsoft ActiveX Data Objects 6.1作为参考。一旦手动创建了连接，这里是我用来修改连接的代码：

  Sub editConnection（）
 With ActiveWorkbook.Connections（myConnectionName）。OLEDBConnection 
 .Connection =OLEDB; Provider = Microsoft.ACE.OLEDB.12.0;& _ 
Data Source = \\something.com\shared\myDepartment\\\
ewDirectory;& _ 
Mode = Read; Extended Properties =Text; HDR = Yes; FMT = Delimited;
 End with 
 ActiveWorkbook.Connections（myConnectionName）刷新
 End Sub 
  

我没有弄清楚如何修改文件名。这也强制在PowerPivot中的表名称作为csv文件的名称。您无法修改该连接。

How can I use vba to modify a Powerpivot connection to a csv file? I have an Excel workbook with a Powerpivot model that connects to a csv file. In the Powerpivot window, I can navigate to the Home->Existing Connections and edit the file path as desired. I can't get close to editing this in vba.

• I found this link on parameterizing Powerpivot connections.
  Unfortunately, I couldn't figure out how to modify to apply to csv file connection rather than database connection.
• An alternate approach is described on this page. This approach creates a connection to the data in Excel itself. The connection is then available in Powerpivot. It is critical to not create the connection in Powerpivot bc the author says connections created in Powerpivot cannot be modified by vba.

I can create this connection manually through the Data->Get External Data->From Text menu option.

I can create the same connection in vba using "Workbooks("myWorkbook.xlsm").Connections.AddFromFile "data.csv". I can manually edit this connection in the Data->Connections->Connections dialog. However, when I try to set this in vba using the WorkbookConnections.TextConnection property, vba says "Object doesn't support this property or method".

The above link focuses on database connections rather than csv file connections. However, it seems possible to set up the csv file with an Microsoft ACE OLEDB 12.0 connection, but I can't quite grasp it.

There are similar questions on SO that address vba modifying pivot sources (like here:Changing pivot table external data source path with Excel macro).

However, I don't want to bring the data into a table in Excel, I want to connect to it so I can work with it in Powerpivot.

I could probably do this with Power Query, but since I already have the csv in the desired format, I'd rather not have to introduce this additional step.

I'm using Excel 2013 64 bit on Windows 7. Thank you for your help.

解决方案

I was able to modify the file location by setting up the csv file connection as a Microsoft.ACE.OLEDB.12.0 connection. It's actually labelled "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in the Data Connection Wizard. This link helped me figure it out. You need to have "Microsoft ActiveX Data Objects 6.1" added as a reference. Once the connection was created manually, here's the code I used to modify the connection:

Sub editConnection()
    With ActiveWorkbook.Connections("myConnectionName").OLEDBConnection
      .Connection = "OLEDB;Provider= Microsoft.ACE.OLEDB.12.0; " & _
         "Data Source=\\something.com\shared\myDepartment\newDirectory; " & _
         "Mode=Read;Extended Properties = ""Text;HDR=Yes;FMT=Delimited;"""
    End With
    ActiveWorkbook.Connections("myConnectionName").Refresh
End Sub

I didn't figure out how to modify the filename. This also forces the table name in PowerPivot to be the name of the csv file. You can't edit that without breaking the connection.

这篇关于使用Excel VBA将Powerpivot连接更改为csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！