Object variable or With block variable not set

[kfschaefer]

I have a Database and Spreadsheet that I am attempting to update the
xls from mdb. and I need to be able to copy more than the 255
characters into 1 cell.

I
have found this code however I am running into a "Object variable or
With block variable not set (Error 91)" issue on the following.

xlWS.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name

here is the code.

Thanks,

Karen

Sub Transfer_1()
Dim xlApp As Excel.Application
Dim xlWkbk As Workbook, xlWS As Worksheet
Dim rs As DAO.Recordset
Dim iCols As Integer


Set rs = CurrentDb.OpenRecordset("SELECT * FROM ZA002_TIRLData")
Set xlApp = CreateObject("Excel.Application")

Set xlWkbk = xlApp.Workbooks.Open("C:\Documents and Settings\user\My Documents\FunctionalTest\ZA002_FunctionalCheck.xls")
xlWkbk.Sheets(SheetName).Cells(1, 1).Activate
xlApp.Visible = True
For iCols = 0 To rs.Fields.Count - 1
xlWS.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
xlWS.Range(xlWS.Cells(1, 1), _
xlWS.Cells(1, rs.Fields.Count)).Font.Bold = True
xlWS.Range("A2").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set xlApp = Nothing
End Sub

 

[pbaldy]

xlWS is never set.

 

[kfschaefer]

They were set as a Public Const.

Public Const FName = "ZA002_FunctionalCheck.xls"
Public Const SheetName = "TIRLData"

The object type is Worksheet - not sure proper syntax

I tried - Set xlWS = "TIRLData"

no Luck.

 

[pbaldy]

Those don't set that variable. This sets a sheet variable to a specific sheet:

Set xl = CreateObject("Excel.application")
strFile = "C:\PresResTemplate.xls"
xl.Workbooks.Open (strFile)
Set xlSheet = xl.Worksheets("Limo")

 

[kfschaefer]

Thanks for the assist, this what I have so far - now I have an issue with the following portion of my code - hope you can help me with it.
xlWS.Range("A2").CopyFromRecordset rs

I am getting the following error.

Method 'CopyFromRecordset' of object 'Range' Failed.


Sub Transfer_1()
Dim xlApp As Excel.Application
Dim xlWkbk As Workbook, xlWS As Worksheet
Dim rs As DAO.Recordset
Dim iCols As Integer

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ZA002_TIRLData")
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open ("C:\Documents and Settings\bd645c\My Documents\FunctionalTest\ZA002_FunctionalCheck.xls")
Set xlWS = xlApp.Worksheets("TIRLData")
xlWS.Cells(1, 1).Activate
xlApp.Visible = True
For iCols = 0 To rs.Fields.Count - 1
xlWS.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
xlWS.Range(xlWS.Cells(1, 1), _
xlWS.Cells(1, rs.Fields.Count)).Font.Bold = True
xlWS.Range("A2").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set xlApp = Nothing
End Sub

 

[pbaldy]

Not sure what's happening. I just tested (Access & Excel 2000), and that worked fine after I commented out this line:

xlWS.Cells(1, 1).Activate

Are you sure the recordset returned records?

 

[gemma-the-husky]

i often seem to find find problems similar to the ones you describe, with the syntax

set rst= currentdb.openrecordset


so i always now do

set dbs = currentdb
set rst = dbs.openrecordset etc

and I never then have the reference problems you describe

 

[pbaldy]

I just happened to stumble onto this, in case it's appropriate to your situation:

When using CopyFromRecordset, you should be aware that the ADO or DAO recordset you use cannot contain OLE object fields or array data such as hierarchical recordsets. If you include fields of either type in a recordset, the CopyFromRecordset method fails with the following error:

Run-time error -2147467259:
Method CopyFromRecordset of object Range failed.