Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
Office 97 Pro
Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
Try opening up the access database directly from access and make usre the
file isn't corrupted and that the data still exists. "Chuck" wrote: Office 97 Pro Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
hi
adding a new query and refreshing an existing query are two different things. the code you posted is for created a new query each time it's run. you only need to do that once. after that all you need to do is refresh. a sub like this is what i use and is all that is needed to refresh. Private Sub CommandButton1_Click() Range("A1").Select Range("A1").QueryTable.Refresh BackgroundQuery = False MsgBox "refresh complete" End Sub you say you ran this code for a year???? i don't see how you didn't get this message a year ago unless you are creating a new query in a new file each time. if your are creating a new query each time, remove all lines in the code that refer to "refresh". creating and refreshing in the same sub is double duty and redundent and may be the source of your error. Regards FSt1 "Chuck" wrote: Office 97 Pro Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
I susspect the problem is with the version of Acxcess tha tis being used.
Access files are not backwards compatible. The file was converted from Access 97 to Access 2000. Not that Access 2000 is not installed the file can no longer be opened in Access 97. "FSt1" wrote: hi adding a new query and refreshing an existing query are two different things. the code you posted is for created a new query each time it's run. you only need to do that once. after that all you need to do is refresh. a sub like this is what i use and is all that is needed to refresh. Private Sub CommandButton1_Click() Range("A1").Select Range("A1").QueryTable.Refresh BackgroundQuery = False MsgBox "refresh complete" End Sub you say you ran this code for a year???? i don't see how you didn't get this message a year ago unless you are creating a new query in a new file each time. if your are creating a new query each time, remove all lines in the code that refer to "refresh". creating and refreshing in the same sub is double duty and redundent and may be the source of your error. Regards FSt1 "Chuck" wrote: Office 97 Pro Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
i saw that. but he said he remove 2000. I guess the OP will have to clairfy
for us. Regards FSt1 "Joel" wrote: I susspect the problem is with the version of Acxcess tha tis being used. Access files are not backwards compatible. The file was converted from Access 97 to Access 2000. Not that Access 2000 is not installed the file can no longer be opened in Access 97. "FSt1" wrote: hi adding a new query and refreshing an existing query are two different things. the code you posted is for created a new query each time it's run. you only need to do that once. after that all you need to do is refresh. a sub like this is what i use and is all that is needed to refresh. Private Sub CommandButton1_Click() Range("A1").Select Range("A1").QueryTable.Refresh BackgroundQuery = False MsgBox "refresh complete" End Sub you say you ran this code for a year???? i don't see how you didn't get this message a year ago unless you are creating a new query in a new file each time. if your are creating a new query each time, remove all lines in the code that refer to "refresh". creating and refreshing in the same sub is double duty and redundent and may be the source of your error. Regards FSt1 "Chuck" wrote: Office 97 Pro Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
On Sat, 4 Oct 2008 18:44:00 -0700, Joel wrote:
No help. Chuck Try opening up the access database directly from access and make usre the file isn't corrupted and that the data still exists. "Chuck" wrote: Office 97 Pro Excel file written 2005 and run daily for about a year. The Excel file gets external data from an Access data base. Tried to run the Excel file today. Error message: [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call. Macro in Excel file: Sub GetMDBdata() ' ' GetMDBdata Macro ' Macro recorded 2/2/2005 by Chuck ' ' Keyboard Shortcut: Ctrl+o ' With ActiveSheet.QueryTables.Add(Connection:= _ "FINDER;C:\Program Files\Microsoft Office\Queries\Get Data from mdb.dqy", _ Destination:=Range("A1")) .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False { *** The line above is where the sub hangs.} .SavePassword = True .SaveData = True End With End Sub I made a new blank Excel file and established a new get external query. Same error message as before. Examined the original Get Data from mdb.dqy file and the new Get Data2 from mdb.dqy. They were identical. Re-installed Office97. Did not help If it makes any difference - I had Office 2000 pro installed (in a different directory from Office97 so I could run either program) for some time. Didn't like Access 2000 so I removed all of Office 2000. Cleaned up registry and directories. I'll be very greatful for any suggestion for getting any Access data into any Excel file. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
On Sun, 5 Oct 2008 05:24:00 -0700, Joel wrote:
I susspect the problem is with the version of Acxcess tha tis being used. Access files are not backwards compatible. The file was converted from Access 97 to Access 2000. Not that Access 2000 is not installed the file can no longer be opened in Access 97. This particular Access file was never converted to A2000. It still exists and runs very well in A97. I tried Compact and Repair but that didn't help the problem. Chuck -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
On Sat, 4 Oct 2008 20:36:01 -0700, FSt1 wrote:
In line comments. hi adding a new query and refreshing an existing query are two different things. the code you posted is for created a new query each time it's run. you only need to do that once. after that all you need to do is refresh. a sub like this is what i use and is all that is needed to refresh. Private Sub CommandButton1_Click() Range("A1").Select Range("A1").QueryTable.Refresh BackgroundQuery = False MsgBox "refresh complete" End Sub It's been a very long time now, but I believe all the previous data was deleted and replaced with new data each time the Excel file was opened. At this point I'm really confused. Why does this macro run just by opening the Excel file? I don't have an autoexec macro. The sub is Sub GetMDBdata(). I do not click a command button. Open the file and it runs. you say you ran this code for a year???? i don't see how you didn't get this message a year ago unless you are creating a new query in a new file each time. I'm running the query each time the Excel file is opened. I am not creating a new query. if your are creating a new query each time, remove all lines in the code that refer to "refresh". creating and refreshing in the same sub is double duty and redundent and may be the source of your error. I commented out all lines with "refresh" The macro runs without any error messages, but it doesn't get any data either. Regards FSt1 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
On Sun, 5 Oct 2008 05:57:02 -0700, FSt1 wrote:
i saw that. but he said he remove 2000. I guess the OP will have to clairfy for us. Access file is still A97. I use it almost ever day. It was never converted to A2000. However, a working copy of VB6 is installed in the same boot partition as Office 97 pro. Could this be a problem? A full version of VB6 is installed in a different boot partition. Chuck -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
Try adding the line velow to stop automatic timed refresh. Not sure if this
will work. .RefreshPeriod = 0 "Chuck" wrote: On Sat, 4 Oct 2008 20:36:01 -0700, FSt1 wrote: In line comments. hi adding a new query and refreshing an existing query are two different things. the code you posted is for created a new query each time it's run. you only need to do that once. after that all you need to do is refresh. a sub like this is what i use and is all that is needed to refresh. Private Sub CommandButton1_Click() Range("A1").Select Range("A1").QueryTable.Refresh BackgroundQuery = False MsgBox "refresh complete" End Sub It's been a very long time now, but I believe all the previous data was deleted and replaced with new data each time the Excel file was opened. At this point I'm really confused. Why does this macro run just by opening the Excel file? I don't have an autoexec macro. The sub is Sub GetMDBdata(). I do not click a command button. Open the file and it runs. you say you ran this code for a year???? i don't see how you didn't get this message a year ago unless you are creating a new query in a new file each time. I'm running the query each time the Excel file is opened. I am not creating a new query. if your are creating a new query each time, remove all lines in the code that refer to "refresh". creating and refreshing in the same sub is double duty and redundent and may be the source of your error. I commented out all lines with "refresh" The macro runs without any error messages, but it doesn't get any data either. Regards FSt1 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get External Data
On Sun, 5 Oct 2008 13:43:00 -0700, Joel wrote:
Try adding the line velow to stop automatic timed refresh. Not sure if this will work. .RefreshPeriod = 0 Doesn't support property or method. Chuck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External Data Warning Message - I have No External Data in wrkbk | Excel Discussion (Misc queries) | |||
Getting External Data based on criteria insde of the external data | Excel Discussion (Misc queries) | |||
Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data | Excel Programming | |||
External Data Queries - Data Range Properties v Spreadsheet Format | Excel Discussion (Misc queries) | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming |