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
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:
Help!
If I debug, it highlights
Selection.Range(C, C.Offset(0, 1)).Merge
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
Help!
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(dbOpenDyn aset, 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.
Also, couple other comments:
1. On this, this is wasteful:
Set Rs = Qd.OpenRecordset(dbOpenDyn
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.
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.
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:
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.
Range(C, C.Offrset(0,1)).select
Selection.merge
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
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
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.
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.
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.
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
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)
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)
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
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
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
OM Gang
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.
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
'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!!
Thank you for the help and the hand holding!!
Not a problem. Glad it's working for you.
OM Gang
OM Gang
OM Gang