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 hightlighted in red.

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.

 

[pvw]

Hi izzyq

I can't see a line highlighted in red :-(. Which line gives the error? Does the code fail in XL97 or XL2000/XL2002?

Hope this can help you:
- You're using "Excel.Application.Version" to check the version. I prefer the check with "Application.VBE.Version".
- Maybee the import in XL97 can be simplified. Instead of using an array this code does the import (single line):
Range(Cells(1, 1), Cells(rst.RecordCount, rst.Fields.Count)).Value = Application.Transpose(rst.GetRows(rst.RecordCount))

Philipp

 

[Kaah]

Either declare the slWS worksheet variable or turn off option explicit.

dim slWS as worksheet

is the line you need to add...

Kaah.

BTW: By first glances, once you fix this one you'll have a few others with the same problem pop up...