0

I am facing issues in VBA while modifying the path of a data connection used in a Power Pivot. I am working on Office 365 with Windows 10 and I am sharing my file on a business Sharepoint.

Through Power Pivot I connect to an Access Data base (on a Sharepoint). Our team need to update the data daily to get the latest results. Since I created the file and the data connection, the path for the Data Source is set with my username. If a colleague wants to update the data, he/she needs to manually go into the Power Pivot Tab and make change to the Existing Connection. Thereofre, I am trying to write a VBA code to automate the data connection depending ont the user.

I have read many posts here and on Office forum but I didn't manage to get pass the following error:

Run Time Error '1004'Application-defines or object-defines error

I have 4 simple steps:
1. Get the username from the Office Environment
2. Get the data connection detail (with the path of the Data Source)
3. Modify the connection detail with the new username in the path
4. Update the connection *Where I get the error

Here is a reproduction of my code:

Sub Connection_Change()

Dim ConnectString As String
Dim NewConnectString As String
Dim U As String

    U = Environ$("UserName")    
    ConnectString = ThisWorkbook.Connections("My Data Connection").OLEDBConnection.Connection
    NewConnectString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" & U & "\OurCompanySharepoint\OurTeamFile\OurFodler\OurDatabase.accdb;Persist Security Info=false"    
    ThisWorkbook.Connections("My Data Connection").OLEDBConnection.Connection = NewConnectString

End Sub

Any idea what causes the error?

BigBen
  • 46,229
  • 7
  • 24
  • 40
Catherine Gladu
  • 97
  • 2
  • 9
  • 1
    Is the data actually located on the `C:` drive of every user's machine? I would think that you'd need some sort of network path (or URL, if you're using Sharepoint online) in your connection string. You're getting the error because the `.accdb` file cannot be found at `C:\Users\Smith\...` or `c:\Users\Jones\...` – FreeMan Mar 09 '20 at 19:53
  • @FreeMan, thanks for replying. The data is located on our Sharepoint which is the ```C:\``` drive for all user. The Data Source parameter in the code is an URL, which I am modyfying in the `NewConnectString`. I still get the error running the last step, with the original path. Which make me think that the problem comes from re-writing back the connection parameters in the `OLEDBConnection.Connection` and not from finding database with the path/URL itself.. – Catherine Gladu Mar 09 '20 at 20:29
  • https://stackoverflow.com/questions/31374729/using-vba-to-add-or-change-power-pivot-data-connection – RADO Mar 10 '20 at 11:06

0 Answers0