Transfer data to excel

[Precips1]

Hi - Can anybody please help with this?

Im trying to transfer some Access table values into an excel spreadsheet - Its been working for a good few years but all of a sudden and I think since converting from Access/Excel 97 to 2003 this part of the transfer has stopped working.

Error msg says "Application-defined or Object-defined error

The code below opens the excel sheet OK, works fine until it comes to the Add Conditions

Any help is much appreciated

Mike
*************************************************************************

Dim xlApp As Excel.Application, xlWKB As Excel.Workbook, xlSht As Excel.Worksheet, rng As Excel.Range
Dim db As DAO.Database, rsConditions As DAO.Recordset, strSQL As String
Set xlApp = New Excel.Application
'On Error Resume Next
xlApp.Visible = True

Set xlWKB = xlApp.Workbooks.Open("Excel spreadsheet goes here.xls")
strSQL = "SELECT tblActOpCons.ProdID, tblOpConDefs.TRCName, tblActOpCons.NumValue, tblActOpCons.TxtValve, tblOpConDefs.TypeNum " & _
"FROM tblOpConDefs INNER JOIN tblActOpCons ON tblOpConDefs.OpConDefID = tblActOpCons.OpConDefID " & _
"WHERE (((tblActOpCons.ProdID)=" & Me.cbxProduct.Value & ") AND ((tblOpConDefs.TRCName) Is Not Null)); "

Set db = CurrentDb
Set rsConditions = db.OpenRecordset(strSQL)
'Adds Names
xlWKB.Names("ProdMin").RefersToRange.Value = Me.cbxProductName.Column(0) & " - Min"
xlWKB.Names("ProdTarget").RefersToRange.Value = Me.cbxProductName.Column(0) & " - Target"
xlWKB.Names("ProdMax").RefersToRange.Value = Me.cbxProductName.Column(0) & " - Max"

'Add conditions
If rsConditions.RecordCount > 0 Then
Do Until rsConditions.EOF
Set rng = xlWKB.Names(rsConditions!TRCName).RefersToRange

If rsConditions!TypeNum = True Then
rng.Value = rsConditions!NumValue
Else
rng.Value = rsConditions!TxtValve
End If
rsConditions.MoveNext
Loop
End If
xlWKB.Sheets("RCS").PrintOut
xlWKB.Close False
xlApp.Quit
End Sub

 

[Precips1]

c'on people - can't be impossible surely?

I just want to copy some Access table data into excel named cells

Just don't understand this Error msg "Application-defined or Object-defined error"

Mike

 

[DCrake]

Sundays are quiet. Have you referenced the correct Excel Object in your Tools and References to accommodate 2003?

 

[DJkarl]

If rsConditions.RecordCount > 0 Then
Do Until rsConditions.EOF

Try getting rid of the record count condition, it really is of no benefit given the next line of code, and it can throw an error if there are no records.

 

[Precips1]

DJKarl - Thanks for the reply...

Its all working ok now - lines removed and your right made no differance.

Thanks

Mike