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!
Excel COM - Get Workfile path
Moderators: EViews Gareth, EViews Moderator
-
- EViews Developer
- Posts: 799
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
Re: Excel COM - Get Workfile path
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:
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.
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.
Re: Excel COM - Get Workfile path
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?
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 Developer
- Posts: 799
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
Re: Excel COM - Get Workfile path
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.
Re: Excel COM - Get Workfile path
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.
Return to “Any Other Business”
Who is online
Users browsing this forum: No registered users and 2 guests