Excel 2003 with SP" 1004 error

[eggpap]

Help me please.

In a workbook I have the following sub that retrieves data from a
Access table into tha "RFest" range of the "festivi" sheet. I have n
error using Office 2003 Professional with SP3. I get, instead, th
following error with Office 2003 Professional SP2

Applicatione or object defined error/1004

on the statements
TargetRange.cells(1, intColIndex+1).Value =
TargetRange.cells(2, 1).CopyFromRecordset rs

However, I cannot to update to SP£ since my Company has not released i
since incompatible with other applications.

Any tip to solve such error without the SP3 upgrade?

Sub ADOImport(TableName As String, drecup As String)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, appath A
String
Dim intColIndex As Integer,TargetRange As Range, ws as worksheet
Dim CID As Long, SQLstr As String, anno As Integer
On Error GoTo err_hnd
set ws=Worksheets("Festivi")
set TargetRange=ws.range("RFest")
Call ScopriRighe
appath = ThisWorkbook.Path & "\"
CID = Range("1Trim!H56").Value
anno = Range("1Trim!AO1").Value

' apre il database
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.jet.OLEDB.4.0"
.Properties("Data Source") = appath & "festivi.mdb"
.Properties("Jet OLEDBatabase Password") = PWORD
.Open
End With

Set rs = New ADODB.Recordset

SQLstr = ..omessa per semplicità

With rs
.Open SQLstr, cn, , , adCmdText
For intColIndex = 0 To rs.Fields.Count - 1 ' nomi dei campi
' l'errore avviene sull'istruzione seguente
' Errore definito dall'applicazione o dall'oggetto /1004
TargetRange.cells(1, intColIndex+1).Value
rs.Fields(intColIndex).Name
Next
TargetRange.cells(2, 1).CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Set TargetRange = Nothing
set ws=Nothing
Exit Sub
err_hnd:
MsgBox Err.Description & "/" & Err.Number & " Sub ADOImport"
Resume Next
End Sub

Thanks, Emilian

 

[Barb Reinhardt]

Not sure why I'm asking this, but are all of your references set in the SP2
instance?

 

[Barb Reinhardt]

Add Option Explicit at the beginning of the code and compile. That may help
you some. If that doesn't work, come back. I don't work with ADODB
connections so can't say for sure what's going on.

 

[Barb Reinhardt]

FWIW, I don't like using On Error GoTo err_hnd. I prefer to handle errors
as they occur and build the program to handle it.

I'd do something like this

Set WS = nothing
On error resume next
Set WS = Worksheets("Festivi")
on error goto 0
If WS is nothing then
Msgbox("Worksheet not found.")
end if

 

[eggpap]

FWIW, I don't like using On Error GoTo err_hnd. I prefer to handle
errors
as they occur and build the program to handle it.

I'd do something like this

Set WS = nothing
On error resume next
Set WS = Worksheets("Festivi")
on error goto 0
If WS is nothing then
Msgbox("Worksheet not found.")
end if

Thanks for your tips,
the references of the project are all set correctly in SP3 and I've
supposed they are the same for SP2, but I'm not sure. This is the
complete list of my references:

Visual Basic for Application
Microsoft Excel Office 11.0 object Library
Ole Automation
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Windows Common Control 6.0 (SP6)
Microsoft Office 11.0 Object Library
Microsoft Access 11.0 Object Library

I have just placed the Option Explicit at the beginning of the code and
compiled it, but the error remains. I think, however it is not related
with ADODB, but with the range selection.

Referring to your last message where you say you don't use the On error
goto method I'ld like to know where you place the code in the sub.

Set WS = nothing <--- is this my last statement in my sub?
On error resume next <---- then, in case of error, goes to Exit Sub?
Set WS = Worksheets("Festivi")
on error goto 0 <--- I've never used this
If WS is nothing then
Msgbox("Worksheet not found.")
end if

Thanks, Emiliano