I am trying to preapare excel file which would download data from many sources with one button.
Sadly, this file would be used by people, which have not any shared folder or something, i cant connect datafeed file in place where they all can access it.
This connection is created in PowerPivot.
Thats why i am trying to prepare macro, which would update path to file for each user.
How i can update this path with vba?
I am able to get current path by using Debug.print Activeworkbook.Connections(1).Datafeedconnection.Connection
But i cant update this which using Activeworkbook.Connections(1).Datafeedconnection.Connection = "..."
It dont work with puting the result of debug.print with small changes, also it dont work when i am trying set this connection to something like = "Test"
If it's needed, i would give exact result of debug.print when i get access to this file (probably tomorrow), but maybe if i am doing something wrong (like choosing wrong command), you can help me before that?
With little help from another place i found solution for this problem.
When i am creating connection with PowerPivot, i am not able to edit datefeedconnection.connection property (it returns error 1004).
However, when the connection is created in excel directly i am able to update this by command: Activeworkbook.Connections(1).Datafeedconnection.Connection = "..."
To create connection in excel directly, we have to go into: - Data tab - Existing connnection - Browse for more (bottom left corner of box) - Choose your .atomsvc file