Use Excel VBA to change Powerpivot connection to csv file

Question

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.

Answer 1

I've been playing around with something similar. Haven't succeeded yet, but maybe we can figure it out together...

One thing I've noticed is that it looks like these connections to CSV files aren't actually TextConnections like you'd expect, but rather OLEDBConnections.

EDIT: According to https://goo.gl/x17Nuj, it's just not possible:

Once you modify the connection inside PowerPivot, the link between the Excel and the PowerPivot connections is broken. In fact, you can no longer modify the connection properties in Excel and, if you want to load another table, then you have to use the PowerPivot add-in. Needing to use the add-in means that the option of modifying the connection is no longer available in VBA Because (as you have seen) there is no way to modify the PowerPivot connections using VBA.

(Emphasis is mine)

Answer 2

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.