Run-time error

[izzyq]

I'm getting a Run-time error '91': Object Variable or with block variable not set.
Could anyone give me some insight on why this is occurring.

The line that it is occuring on, I've put stars beside.

Thank You



Option Explicit


Sub GetData2()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlWs As Object


Dim recArray As Variant

Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Set the string to the path of your database
strDB = " \\Quartz\Common\Comptrol\Corp_Rep\Monthend\2002\Fi

nancial Operating Results\Working Copies\Costbudget.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Cost and Budget query
rst.Open "Select * from qryCostBudget", cnt

' Set the sheet where data will be copied
Sheets("2002&quot.Select


' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
slWS.Cells(1,iCol).Value = rst.Fields(iCol - 1).Name*******
Next

' Check version of Excel
If Val(Mid(Excel.Application.Version, 1, InStr(1, Excel.Application.Version, ".&quot - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

Else
'EXCEL 97 or earlier: Use GetRows then copy array to Excel

' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array


' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Take care of OLE object fields or array fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field

' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If Help Me Obi Wan Kenobi, You're My Only Hope.

 

[xlbo]

Hi Izzy, coupla things spring to mind.
1st you've declared xlWS as an object but then you reference slWS on your error line. Also, if xlWS is a worksheet, you can dim it as a worksheet but then you have to use SET to declare it
eg Set xlWS = sheets("2002&quot
then you can use
xlWS.select to select the sheet

The error you're getting is, I think because slWS has not been Set.
HTH
Geoff

 

[rmikesmith]

izzyq,

A see a couple of problems. The offending line,

slWS.Cells(1,iCol).Value = rst.Fields(iCol - 1).Name

contains a variable name slWS that is not declared. Assuming this is actually xlWS, which you've declared as an object variable, I see no assignment of value to this. Based on the context, this is probably meant as a worksheet or sheet object. In any case, you must use the "Set" command to assign an object variable. Example:

Set xlWS = Workbooks("myWorkbook").Worksheets("Sheet1")

or

For Each xlWS In Workbooks("myWorkbook")

...
...
Next xlWS

Hope this helps.
M. Smith

 

[xlbo]

Hey Mike - guess we posted at the same time ;-)
G

 

[izzyq]

Thanks Geoff and Mike, works like a charm. Although, I had just assumed that this would bring in the titles of the columns from the access database and paste them in the excel sheet. In this case it's not happening. Am I missing something. Help Me Obi Wan Kenobi, You're My Only Hope.

 

[rmikesmith]

izzyq,

Not sure on this (I seem to have lost my MS Access during a migration to a new workstation/networking environment) but maybe the property you should reference is "Caption". Could you post what you are getting on the Excel worksheet?

Geoff -- You actually beat me on this one by a least a few seconds

Mike