Excel COM - Get Workfile path

For posts that don't quite fit into any of the other forums, including posts about these forums themselves.

Moderators: EViews Gareth, EViews Moderator

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Excel COM - Get Workfile path

Postby DPA » Wed Sep 14, 2016 11:13 am

I have Excel data that is linked to a workfile, which was done using the Eviews Addin (Get Data - Import + Link). Is it possible to get the path of the workfile that is linked to the data using Excel COM VBA code??

Thanks in advance! :D

EViews Steve
EViews Developer
Posts: 448
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Excel COM - Get Workfile path

Postby EViews Steve » Wed Sep 14, 2016 11:46 am

The Excel data links that our add-in creates are simply OLEDB data connections. The add-in just helps you create these. Once created, the link is maintained and updated by Excel.

If you access the connection string property of your OLEDB connection object, you can parse it to find the full path to the workfile the data is coming from. I believe there is a Connections collection on the Workbook object. You can loop thru it and then look at the OLEDBConnection.Connection property.

So something like this:

Code: Select all

ActiveWorkbook.Connections("my connection name").OLEDBConnection.Connection


Replace the name with your connection name, or simply loop thru all of your workbook connections and print out the connection property and you'll see the full path to the workfile in the string.

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Re: Excel COM - Get Workfile path

Postby DPA » Wed Sep 14, 2016 12:44 pm

Thank you Steve!! I really appreciate the help.

Using the connection, I am trying to update the imported data (through vba). When the the data is refreshing, does it take into account the time range of the workfile?
For example when I initially import/linked the data in excel, the year range of the workfile was 1989 to 2015. If the workfile was later changed to include 2016 data as well, would the refresh add the extra data at the bottom? Or would I have to re-import the data from the workfile?

EViews Steve wrote:The Excel data links that our add-in creates are simply OLEDB data connections. The add-in just helps you create these. Once created, the link is maintained and updated by Excel.

If you access the connection string property of your OLEDB connection object, you can parse it to find the full path to the workfile the data is coming from. I believe there is a Connections collection on the Workbook object. You can loop thru it and then look at the OLEDBConnection.Connection property.

So something like this:

Code: Select all

ActiveWorkbook.Connections("my connection name").OLEDBConnection.Connection


Replace the name with your connection name, or simply loop thru all of your workbook connections and print out the connection property and you'll see the full path to the workfile in the string.

EViews Steve
EViews Developer
Posts: 448
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Excel COM - Get Workfile path

Postby EViews Steve » Wed Sep 14, 2016 1:00 pm

If you resize the source workfile, add more data, and save the workfile, the linked data column in Excel should also grow the next time it performs an update.

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Re: Excel COM - Get Workfile path

Postby DPA » Wed Sep 14, 2016 1:10 pm

Perfect. Thank you so much! :-)

EViews Steve wrote:If you resize the source workfile, add more data, and save the workfile, the linked data column in Excel should also grow the next time it performs an update.

bikithalee
Posts: 1
Joined: Thu Nov 03, 2016 12:04 am

Re: Excel COM - Get Workfile path

Postby bikithalee » Thu Nov 03, 2016 12:06 am

Try this one.....Excel OLEDB

Lee


Return to “Any Other Business”

Who is online

Users browsing this forum: No registered users and 1 guest