Get External Data

[Chuck]

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.

 

[Joel]

Try opening up the access database directly from access and make usre the
file isn't corrupted and that the data still exists.

 

[FSt1]

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

 

[Joel]

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.

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

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.

 

[FSt1]

i saw that. but he said he remove 2000. I guess the OP will have to clairfy
for us.

Regards
FSt1

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.

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

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.

 

[Chuck]

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.

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.

 

[Chuck]

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
--

 

[Chuck]

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.

 

[Chuck]

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
--

 

[Joel]

Try adding the line velow to stop automatic timed refresh. Not sure if this
will work.

.RefreshPeriod = 0

 

[Chuck]

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