I am trying to import data from one Excel sheet to
another, using ADO, and it doesn't work with Excel 97 as
it does with Excel 2000. I am running Excel 97 at work on
Win NT 4. Can anyone help me? Here is part of the code:
-------------------------
Public Sub LoadList()
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

'Create for connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
& "Data Source=D:\Reports\Totals.xls;" & "Extended
Properties=Excel 8.0;"
'Query based on a specific range address
szSQL = "SELECT * FROM [Summary$a2:b4]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdText
'Check to make sure data was received
If Not rsData.EOF Then
Groups.Range("A2:C501").CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

'Clean up the Recordset object
rsData.Close
Set rsData = Nothing

End Sub

Hi Mike,
Whenever I've got that message, it is because I'm trying to do
something like delete or close the file that contains the code. Is
your code trying to do that somewhere?
regards
Paul

"Mike" wrote in message ...
I am trying to import data from one Excel sheet to
another, using ADO, and it doesn't work with Excel 97 as
it does with Excel 2000. I am running Excel 97 at work on
Win NT 4. Can anyone help me? Here is part of the code:
-------------------------
Public Sub LoadList()
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

'Create for connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
& "Data Source=D:\Reports\Totals.xls;" & "Extended
Properties=Excel 8.0;"
'Query based on a specific range address
szSQL = "SELECT * FROM [Summary$a2:b4]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly,
adLockReadOnly, adCmdText
'Check to make sure data was received
If Not rsData.EOF Then
Groups.Range("A2:C501").CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

'Clean up the Recordset object
rsData.Close
Set rsData = Nothing

End Sub