Excel 2003 with SP" 1004 error
 

Help me please.

In a workbook I have the following sub that retrieves data from an
Access table into tha "RFest" range of the "festivi" sheet. I have no
error using Office 2003 Professional with SP3. I get, instead, the
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 it
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 As
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 OLEDB:Database 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, Emiliano


--
eggpap

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77743

Excel 2003 with SP" 1004 error
 

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

"eggpap" wrote:


Help me please.

In a workbook I have the following sub that retrieves data from an
Access table into tha "RFest" range of the "festivi" sheet. I have no
error using Office 2003 Professional with SP3. I get, instead, the
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 it
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 As
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 OLEDB:Database 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, Emiliano


--
eggpap

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77743

Excel 2003 with SP" 1004 error
 

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.

"eggpap" wrote:


Help me please.

In a workbook I have the following sub that retrieves data from an
Access table into tha "RFest" range of the "festivi" sheet. I have no
error using Office 2003 Professional with SP3. I get, instead, the
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 it
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 As
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 OLEDB:Database 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, Emiliano


--
eggpap

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77743

Excel 2003 with SP" 1004 error
 

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" wrote:


Help me please.

In a workbook I have the following sub that retrieves data from an
Access table into tha "RFest" range of the "festivi" sheet. I have no
error using Office 2003 Professional with SP3. I get, instead, the
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 it
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 As
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 OLEDB:Database 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, Emiliano


--
eggpap

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77743

Excel 2003 with SP" 1004 error
 

Barb Reinhardt;278759 Wrote:
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


--
eggpap

Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77743