Link to home
Start Free TrialLog in
Avatar of Megin
Megin

asked on

Access VBA: Object Variable Not Set error occurs only after running code once before

I have a piece of code that runs beautifully the first time I run it. The second time I click the button that runs it, I get Run-time error 91. "Object variable or With block variable not set"

If I debug, it highlights
Selection.Range(C, C.Offset(0, 1)).Merge

Open in new window


Once I reset everything and click the button that runs the code again, it works fine again.  I DON'T UNDERSTAND!

Here is the whole code. The line is 166:
Private Sub cmdExport_Click()
'On Error GoTo Error_Handler
Dim Db As Database, Rs As Recordset, sSQL As String, iW As Integer, iRow As Integer
'dim statements for new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
Dim TheTO As String
Dim TheSTOname As String
Dim TheStaffName As String
Dim TheActDesc As String
Dim C As Object
Dim Qd As QueryDef

'Initialize variables (this resets them so they do not hold old values)
sSQL = ""
iW = 0
iRow = 0
'oApp =
'oBook = Nothing
'oSheet = Nothing
'oRange = Nothing
i = 0
iNumCols = 0
TheTO = ""
TheSTOname = ""
TheStaffName = ""
TheActDesc = ""
'C = ""
'Qd = Null

SQL = "qry_report1"
Set Db = CurrentDb
Set Qd = Db.QueryDefs("qry_report1")

Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
Rs.MoveLast
Rs.MoveFirst

If Rs.RecordCount = 0 Then
    GoTo Bail
End If

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oApp.Visible = True
oApp.UserControl = True


'ok We want each task order to be on a row by itself in column A
'Headers
oSheet.Cells(1, 1).Value = "Task Order/Sub Tasks"

'oSheet.Cells(1, 2).Value = "Sub Tasks"

oSheet.Cells(1, 2).Value = "Staff"

oSheet.Cells(1, 3).Value = "Details"

TheTO = Rs!to
oSheet.Cells(2, 1).Value = TheTO
    Set C = oSheet.Cells(2, 1)
    With C
        
        .Font.Name = "Arial"
        .Font.Bold = True
        .Font.Size = 12
        .Interior.ColorIndex = 15
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).ColorIndex = xlAutomatic
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).ColorIndex = xlAutomatic
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).ColorIndex = xlAutomatic
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
     End With

TheSTOname = Rs!STO
oSheet.Cells(3, 1).Value = TheSTOname
TheStaffName = Rs!TeamName
oSheet.Cells(3, 2).Value = TheStaffName
TheActDesc = Rs!ActDesc
oSheet.Cells(4, 3).Value = TheActDesc

'this is the last row I just wrote to   
iRow = 4

Rs.MoveNext

'what are these for? when are these used?
iNumCols = 3


Do Until Rs.EOF

    If Rs!to = TheTO Then
        If Rs!STO = TheSTOname Then
            If TheStaffName = Rs!TeamName Then
                If TheActDesc = Rs!ActDesc Then
                    Rs.MoveNext
                Else
                    iRow = iRow + 1
                    oSheet.Cells(iRow, 3).Value = Rs!ActDesc
                    Rs.MoveNext
                End If
            Else
                'iRow = iRow + 1
                oSheet.Cells(iRow, 2).Value = Rs!TeamName
                TheStaffName = Rs!TeamName
                Rs.MoveNext
            End If
        Else
            iRow = iRow + 1
            oSheet.Cells(iRow, 1).Value = Rs!STO
           TheSTOname = Rs!STO
            TheStaffName = ""
            
            Rs.MoveNext
        End If
    Else
        iRow = iRow + 1
        oSheet.Cells(iRow, 1).Value = Rs!to
       'FormatTO (oSheet.Cells(iRow,1),"Arial")
        Set C = oSheet.Cells(iRow, 1)

        
        With C
            .Font.Name = "Arial"
            .Font.Bold = True
            .Font.Size = 12
            .Interior.ColorIndex = 15
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic

Selection.Range(C, C.Offset(0, 1)).Merge

            .WrapText = True
            .RowHeight = 54.75
            
         End With

        TheTO = Rs!to
        Rs.MoveNext
        
    End If


skip:
Loop

iNumCols = IIf(w > 0, w, iNumCols)
'Format the header row as bold
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
'Size the columns
.Columns("A:A").ColumnWidth = 40
.Columns("B:B").ColumnWidth = 22
.Columns("C:C").ColumnWidth = 73.44
End With

Bail:
    Rs.Close
    Set Rs = Nothing
    Set oApp = Nothing
    Db.Close
    Set Db = Nothing
    Qd.Close
   Set Qd = Nothing
   
    Exit Sub
    
Error_Handler:
    MsgBox Error$
    Resume Bail
  
End Sub

Open in new window


Help!
Avatar of omgang
omgang
Flag of United States of America image

I just took a quick glance but I notice you're destroying object variables at the end of the routine but neither C nor oSheet get destroyed.  Perhaps this is causing a conflict the second time through?   Try destroying those along with the other object variables and see if the problem goes away.

OM Gang
Not sure about the error....I would step through with F8 (put a STOP at the top of the procedure) and see if you are executing the procedure the same way each time.

Also, couple other comments:

1. On this, this is wasteful:

Set Rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
Rs.MoveLast
Rs.MoveFirst

If Rs.RecordCount = 0 Then
    GoTo Bail
End If

  No need to movelast/first unless you want an accurate count.  If all you need to know is if there are records, you can either:

a. check for BOF and EOF
b. That .RecordCount >0

2. On Clean-up, make sure you close anything you open, and set all object variables = nothing.   I don't see "C" in there.  You should be taking care of all your object variables as well.

3. Add an option explicit to the top of the module.  Do a compile and make sure you have no errors.

Jim.
And on the error:

Selection.Range(C, C.Offset(0, 1)).Merge

  have you done a select prior to this (don't see one)?  What is it your trying to do there?  

Jim.
Avatar of Megin
Megin

ASKER

Jim: I have not done a select prior to this. When I try to, I get a different error the second time I run it. When I have:
Range(C, C.Offrset(0,1)).select
Selection.merge

Open in new window


I get Run-time error 1004 "Method 'Range' of object'_Global' failed"

But still only the second time I try to run it. The first time it works great.

OmGang: I just ran it with osheet and C both set to nothing at the end and the code still has the same problem. Is that what you meant by destroying them? I am not fluent in vba yet.
<<Is that what you meant by destroying them?>>
Yes.

OM Gang
You have to be very strict closing the Excel objects in reverse order:

Bail:
    Rs.Close
    Set Rs = Nothing
    Qd.Close
    Set Qd = Nothing  
    Db.Close
    Set Db = Nothing
    Set oRange = Nothing
    Set oSheet = Nothing
    Set oBook = Nothing
    Set oApp = Nothing

/gustav
<<Range(C, C.Offrset(0,1)).select>>

 You need to name an object there that your selecting the range through (a sheet for example).  This might be of help:

How to select cells/ranges by using Visual Basic procedures in Excel
http://support2.microsoft.com/kb/291308

  There are a number of examples there that show the various ways you can select cells and ranges.  

 Since your early bound, intellisense should be working and you should be getting clean compiles.    When you type that statement, does it resolve and do you have any compile errors?

 The other approach is to put a STOP above the offending statement and execute.   Now when you hit that stop, you can call up the debug window (CTRL/G), and execute commands to figure out what's allowed or not (that's usually how I feel my way though an object model).   You can also use things like the locals window to see exactly what has and has not been instantiated at that point.

 And again, it would be helpful if you explained what your attempting to do at that point.

<<But still only the second time I try to run it. The first time it works great.>>

  Which suggests either:

a. You don't fall through that ELSE section on the first time around, but do on the second.

b. That your destroying something before you hit the else section.

  Again, stepping through with F8 will help you figure it out.   While doing that, you may want to limit the records in the record set by adding some criteria to qry_report1 so that it is not a long process.   Get it working with a few records, step through and verify your logic, then open it back up and see if it works.

 You have a number of things going on in that routine, and without understanding exactly what your trying to do and have the data, it's difficult to offer specifics, which is why I'm giving you general techniques that you can use to debug.

 That also helps you not only with this, but will in the future as well.

Jim.
Avatar of Megin

ASKER

Jim: I tried all of that and I am still lost.

If the error happened the second time the loop ran, I could see why some of that would be an issue. But it happens after I run the code completely the first time. I click on my "Run Report" button in my form once and everything goes great. The report is created and everything happened as it should. I try to run the report again and the error happens.

The trouble piece (Range (C, C.Offset(0,1)).select    Selection.merge) is an addition to the formatting. It is just supposed to take the cell that is being formatted in that part, and merge it with the cell next to it.

Gustav: I made those changes and am still getting the same error.
Possible to upload a sample db with all necessary components and some sample data?
OM Gang
You also need this:

Dim C As Excel.Range

Bail:
     Rs.Close
     Set Rs = Nothing
     Qd.Close
     Set Qd = Nothing  
     Db.Close
     Set Db = Nothing
     Set C = Nothing
     Set oRange = Nothing
     Set oSheet = Nothing
     Set oBook = Nothing
     Set oApp = Nothing

And as Jim mentioned, get rid of the selection stuff. Replace by using a Range.

/gustav

Set C = oSheet.Cells(iRow, 1)
Avatar of Megin

ASKER

Okay. Attached is a copy of the database. The form to go to is frm_Reports and click the button "Full Report."

Thank you for looking at this!
ForEE.accdb
Changes.

1) How you instantiate the Excel App
'Dim oApp As New Excel.Application
Dim oApp As Excel.Application

2)This doesn't work so we need to change it
'Range(C, C.Offrset(0, 1)).Select
Range(C.Cells, C.Cells.Offset(0, 1)).Select

3)Additional declarations and instantiate Excel
'Dim C As Object
Dim C As Excel.Range
Dim Qd As QueryDef

'added by OMGang  -- I added Option Explicit at the top of the module (as suggested by  Jim in the second post) so we can be sure all variables are declared
Dim SQL As String
Dim w As Integer

Set oApp = Excel.Application

4)better job of cleaning up as recommended by gustav
Bail:
    Rs.Close
     Set Rs = Nothing
     Qd.Close
     Set Qd = Nothing
     Db.Close
     Set Db = Nothing
     
     Set C = Nothing
     Set oRange = Nothing
     Set oSheet = Nothing
     
     Set oBook = Nothing
     Set oApp = Nothing
   
   
    Exit Sub


OM Gang
Works as you want it to now.
OM Gang
Avatar of Megin

ASKER

Nope. Still doesn't work for me.
Maybe there is a problem with my version of Access. I am getting run time error 1004: Method 'range' of object'_Global' failed when I run the code the second time.

Also, when I changed "New Excel.application" to just "Excel.Application", I ended up with run time error 91 - "Object variable or With block variable not set"

I am going to take this home and see if it works on a different computer.
Re. the just Excel.Application, notice that I instantiate Excel later after I added some additional declarations.


 'added by OMGang  -- I added Option Explicit at the top of the module (as suggested by  Jim in the second post) so we can be sure all variables are declared
 Dim SQL As String
 Dim w As Integer

 Set oApp = Excel.Application

OM Gang
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Megin

ASKER

Sorry I was so dense about this! I just went through and compared your code to mine, line by line, and finally found what you were talking about.

Thank you for the help and the hand holding!!
Not a problem.  Glad it's working for you.
OM Gang