Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.office.developer.automation,microsoft.public.office.developer.vba
|
|||
|
|||
Repost: Update querytable connections and refresh data
I've cross posted this to several of the Excel and Office automation groups
in hopes of getting a quick response. I've got an Excel spreadsheet that uses info from an Acccess database to generate a series of charts. In the Excel spreadsheet, are a number of querytables that are the data source for the charts. Unfortunately, I'm having to carry these files around with me, and move them to new locations on the network (or my USB drive) on a regular basis. I'm trying to write a procedure to update the querytable connection strings to point to the copy of the Access database which resides in the same folder as the Excel workbook. I get the old path from the connection, then determine the newpath based on the workbooks fullname, then replace the oldpath with the newpath in the connection string. Most of that process appears to be working. qt.Connection = Replace( qt.connection, strOldPath, strNewPath) But then I want to refresh the connection and refresh the data. I'm currently using the following line, which is generating an error message. qt.Refresh False Run-time error '1004' General ODBC Error If I don't put this line in the procedure, I get the proper connection string when I type debug.print the connection in the Immediate window, but when I put my cursor in one of the cells of the query table results, and select 'Refresh Data' I get the following message: [microsoft][odbc microsoft access driver] 'P:\...\...\filename.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. and the filename that is listed in the message is the filename that was in the old connection string, not the new one. Ideas would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.office.developer.automation,microsoft.public.office.developer.vba
|
|||
|
|||
Repost: Update querytable connections and refresh data
Disregard. Got it figured out.
-- Don''t forget to rate the post if it was helpful! Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I've cross posted this to several of the Excel and Office automation groups in hopes of getting a quick response. I've got an Excel spreadsheet that uses info from an Acccess database to generate a series of charts. In the Excel spreadsheet, are a number of querytables that are the data source for the charts. Unfortunately, I'm having to carry these files around with me, and move them to new locations on the network (or my USB drive) on a regular basis. I'm trying to write a procedure to update the querytable connection strings to point to the copy of the Access database which resides in the same folder as the Excel workbook. I get the old path from the connection, then determine the newpath based on the workbooks fullname, then replace the oldpath with the newpath in the connection string. Most of that process appears to be working. qt.Connection = Replace( qt.connection, strOldPath, strNewPath) But then I want to refresh the connection and refresh the data. I'm currently using the following line, which is generating an error message. qt.Refresh False Run-time error '1004' General ODBC Error If I don't put this line in the procedure, I get the proper connection string when I type debug.print the connection in the Immediate window, but when I put my cursor in one of the cells of the query table results, and select 'Refresh Data' I get the following message: [microsoft][odbc microsoft access driver] 'P:\...\...\filename.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. and the filename that is listed in the message is the filename that was in the old connection string, not the new one. Ideas would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.office.developer.automation,microsoft.public.office.developer.vba
|
|||
|
|||
Repost: Update querytable connections and refresh data
I am experiencing a similar problem with the same error message. How can I
change the drive letter of the query or OLAP cube? It seems that my former driver letter is embedded in the cube and/or query. Please reply with any suggestions. "Dale Fye" wrote: Disregard. Got it figured out. -- Don''t forget to rate the post if it was helpful! Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: I've cross posted this to several of the Excel and Office automation groups in hopes of getting a quick response. I've got an Excel spreadsheet that uses info from an Acccess database to generate a series of charts. In the Excel spreadsheet, are a number of querytables that are the data source for the charts. Unfortunately, I'm having to carry these files around with me, and move them to new locations on the network (or my USB drive) on a regular basis. I'm trying to write a procedure to update the querytable connection strings to point to the copy of the Access database which resides in the same folder as the Excel workbook. I get the old path from the connection, then determine the newpath based on the workbooks fullname, then replace the oldpath with the newpath in the connection string. Most of that process appears to be working. qt.Connection = Replace( qt.connection, strOldPath, strNewPath) But then I want to refresh the connection and refresh the data. I'm currently using the following line, which is generating an error message. qt.Refresh False Run-time error '1004' General ODBC Error If I don't put this line in the procedure, I get the proper connection string when I type debug.print the connection in the Immediate window, but when I put my cursor in one of the cells of the query table results, and select 'Refresh Data' I get the following message: [microsoft][odbc microsoft access driver] 'P:\...\...\filename.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. and the filename that is listed in the message is the filename that was in the old connection string, not the new one. Ideas would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update querytable connections and refresh data | Excel Discussion (Misc queries) | |||
Update querytable connections and refresh data | Charts and Charting in Excel | |||
Repost: Disabling Update Links dialog box | Excel Discussion (Misc queries) | |||
Lost External Data Connections | Excel Discussion (Misc queries) | |||
Security Issue: Data Connections Disabled | Excel Discussion (Misc queries) |