Err 91 : Object variable or With block variable not set

[nelsonchang]

Dear all,
I wrote the VB code in a "button press event" as below in
which is retrieve data from SQL server and insert the data
into Excel. After that, I would call Excel "subtotal" API
to build a subtotal figures. For first time, it is working
fine. But in the second time, I try to execute the event
again. Whenever it runs the command "Selection.Subtotal
GroupBy:=2, Function:=xlSum, TotalList:=Array(3)", it
always raises the error "Err 91 : Object variable or With
block variable not set". Please advise.
Thanks & Regards
Public Function getPlatinum()
On Error GoTo ErrorHandler
Dim rstAP As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQL1 As String
Dim rowNo As Integer

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet

getPlatinum = False
Set oXLApp = New Excel.Application
Set oXLWBook = oXLApp.Workbooks.Add
Set oXLWSheet = oXLWBook.Worksheets("Sheet1")

oXLWSheet.Cells(1, 1).Value = "Customer"
oXLWSheet.Cells(1, 2).Value = "Currency"
oXLWSheet.Cells(1, 3).Value = "Amount"
oXLWSheet.Cells.Item(1, 1).Font.Size = 12
oXLWSheet.Cells.Item(1, 2).Font.Size = 12
oXLWSheet.Cells.Item(1, 3).Font.Size = 12
oXLWSheet.Cells.Item(1, 1).Font.Bold = True
oXLWSheet.Cells.Item(1, 2).Font.Bold = True
oXLWSheet.Cells.Item(1, 3).Font.Bold = True

Set Cnxn = New ADODB.Connection
Cnxn.ConnectionString = "Data Source=" & ServerId
& ";" & _
"User ID=" & UserId & ";Password=" & UserPassword
& ";Initial Catalog=" & CompanyId & ";"
Cnxn.Open

Journal_no = "ACSR0305001"
Set rstAP = New ADODB.Recordset

strSQL1 = "SELECT AMOUNT, ARTRXAGE.NAT_CUR_CODE,
ADDRESS_NAME FROM ARTRXAGE, ARMASTER " & _
" Where TRX_TYPE = 2031 And PAID_FLAG = 0 "
& _
" AND ARMASTER.CUSTOMER_CODE =
ARTRXAGE.CUSTOMER_CODE" & _
" ORDER BY ADDRESS_NAME"

rstAP.Open strSQL1, Cnxn, adOpenDynamic,
adLockPessimistic, adCmdText
rowNo = 2
Do Until rstAP.EOF
oXLWSheet.Cells(rowNo, 1).Value = rstAP!
ADDRESS_NAME
oXLWSheet.Cells(rowNo, 2).Value = rstAP!
ADDRESS_NAME & " -- " & rstAP!NAT_CUR_CODE
oXLWSheet.Cells(rowNo, 3).Value = rstAP!amount
rstAP.MoveNext
rowNo = rowNo + 1
Loop
oXLWSheet.Activate

Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3)
oXLWSheet.Range(oXLWSheet.Cells.Item(1, 1),
oXLWSheet.Cells.Item(rowNo - 1, 3)).Columns.AutoFit
oXLApp.Visible = True
oXLWBook.Close
oXLApp.Quit
Set oXLWSheet = Nothing
Set oXLWBook = Nothing
Set oXLApp = Nothing

rstAP.Close
Set rstAP = Nothing

Cnxn.Close
Set Cnxn = Nothing
getPlatinum = True
MsgBox ("Finished")
Exit Function

ErrorHandler:

If Not rstAP Is Nothing Then
If rstAP.State = adStateOpen Then rstAP.Close
End If
Set rstAP = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Number & " : " & Err.Source & "-->" &
Err.Description, , "Error"
End If

End Function

[Cor]

Hi Nelson,

Even for me this looks very much to VB6 code, either you have to change it
to more VB.net I think or else (if it is VB6) I think that it is better to
try to find help for this error in a classic VB newsgroup

Microsoft.public.vb* (there are a lot)

I hope this helps?

Cor
"ne*********@tristateww.com" <an*******@discussions.microsoft.com> schreef
in bericht news:07****************************@phx.gbl...

Dear all,
I wrote the VB code in a "button press event" as below in
which is retrieve data from SQL server and insert the data
into Excel. After that, I would call Excel "subtotal" API
to build a subtotal figures. For first time, it is working
fine. But in the second time, I try to execute the event
again. Whenever it runs the command "Selection.Subtotal
GroupBy:=2, Function:=xlSum, TotalList:=Array(3)", it
always raises the error "Err 91 : Object variable or With
block variable not set". Please advise.
Thanks & Regards
Public Function getPlatinum()
On Error GoTo ErrorHandler
Dim rstAP As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQL1 As String
Dim rowNo As Integer

Dim oXLApp As Excel.Application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet

getPlatinum = False
Set oXLApp = New Excel.Application
Set oXLWBook = oXLApp.Workbooks.Add
Set oXLWSheet = oXLWBook.Worksheets("Sheet1")

oXLWSheet.Cells(1, 1).Value = "Customer"
oXLWSheet.Cells(1, 2).Value = "Currency"
oXLWSheet.Cells(1, 3).Value = "Amount"
oXLWSheet.Cells.Item(1, 1).Font.Size = 12
oXLWSheet.Cells.Item(1, 2).Font.Size = 12
oXLWSheet.Cells.Item(1, 3).Font.Size = 12
oXLWSheet.Cells.Item(1, 1).Font.Bold = True
oXLWSheet.Cells.Item(1, 2).Font.Bold = True
oXLWSheet.Cells.Item(1, 3).Font.Bold = True

Set Cnxn = New ADODB.Connection
Cnxn.ConnectionString = "Data Source=" & ServerId
& ";" & _
"User ID=" & UserId & ";Password=" & UserPassword
& ";Initial Catalog=" & CompanyId & ";"
Cnxn.Open

Journal_no = "ACSR0305001"
Set rstAP = New ADODB.Recordset

strSQL1 = "SELECT AMOUNT, ARTRXAGE.NAT_CUR_CODE,
ADDRESS_NAME FROM ARTRXAGE, ARMASTER " & _
" Where TRX_TYPE = 2031 And PAID_FLAG = 0 "
& _
" AND ARMASTER.CUSTOMER_CODE =
ARTRXAGE.CUSTOMER_CODE" & _
" ORDER BY ADDRESS_NAME"

rstAP.Open strSQL1, Cnxn, adOpenDynamic,
adLockPessimistic, adCmdText
rowNo = 2
Do Until rstAP.EOF
oXLWSheet.Cells(rowNo, 1).Value = rstAP!
ADDRESS_NAME
oXLWSheet.Cells(rowNo, 2).Value = rstAP!
ADDRESS_NAME & " -- " & rstAP!NAT_CUR_CODE
oXLWSheet.Cells(rowNo, 3).Value = rstAP!amount
rstAP.MoveNext
rowNo = rowNo + 1
Loop
oXLWSheet.Activate

Selection.Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3)
oXLWSheet.Range(oXLWSheet.Cells.Item(1, 1),
oXLWSheet.Cells.Item(rowNo - 1, 3)).Columns.AutoFit
oXLApp.Visible = True
oXLWBook.Close
oXLApp.Quit
Set oXLWSheet = Nothing
Set oXLWBook = Nothing
Set oXLApp = Nothing

rstAP.Close
Set rstAP = Nothing

Cnxn.Close
Set Cnxn = Nothing
getPlatinum = True
MsgBox ("Finished")
Exit Function

ErrorHandler:

If Not rstAP Is Nothing Then
If rstAP.State = adStateOpen Then rstAP.Close
End If
Set rstAP = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Number & " : " & Err.Source & "-->" &
Err.Description, , "Error"
End If

End Function

[Herfried K. Wagner [MVP]]

* "Cor" <no*@non.com> scripsit:

Even for me this looks very much to VB6 code, either you have to change it

Seems to be VB6 code.

;->

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>

[nelsonchang]

I thought here is for VB code whatever which version.
Nelson

-----Original Message-----
* "Cor" <no*@non.com> scripsit:

Even for me this looks very much to VB6 code, either

you have to change it
Seems to be VB6 code.

;->

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>
.

[Herfried K. Wagner [MVP]]

* "ne*********@tristateww.com" <an*******@discussions.microsoft.com> scripsit:

I thought here is for VB code whatever which version.

As the name of this group says, it's a VB.NET group
("dotnet.languages.vb"). The VB1-6 groups can be found in the
microsoft.public.vb.* hierarchy.

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>