Excel VBA in Access - Object variable or with variable not set

[CindyK]

I'm having a weird problem with Excel code bombing on me inside Access. Hoping someone can help.

I'm creating an excel file within Access 97, using the following code:

' Initializing Excel
Set ExcelObj = New Excel.Application
' Adding a workbook, saving it to a file :
ExcelObj.Workbooks.Add
ExcelObj.ActiveWorkbook.SaveAs strPath 'strPath is defined in other code

Code that follows take a handful of Access queries, dumping their contents to the same excel file. As it exports the information (I'm simply copying the recordset into the worksheet in Excel), I'm then doing excel formatting - adding headers, resizing, and totaling.

The code that is being used for the worksheet manipulation used to be an Excel macro - works fine in an Excel application. I've added the Excel Object libary reference, and have been able to accomplish the majority of the code in Access, running properly. (This is kludgy code in that there's lots of repeats rather than using loops or arrays - each dataset is different, requires different actions, and therefore I've got a heck of a long module - bad design - not sure of any way around it.)

Here's one subset of code that works fine: (This is just a chunk, but I think enough to see what's happening…). Of real importance in troubleshooting this is that all code that's not italicized works like a charm, and the italicized code works fine on the FIRST sheet.

------------
'add the zip sheet (this is one of several Excel sheets that get created)
Set dtbCurrent = CurrentDb
ExcelObj.Worksheets.Add
ExcelObj.ActiveSheet.Name = "Zip Codes"
Set rstSource = dtbCurrent.OpenRecordset("qryUWZip&quot
ExcelObj.ActiveSheet.Range("A1&quot.CopyFromRecordset rstSource

ExcelObj.ActiveSheet.Range("A1&quot.Select
ExcelObj.Selection.EntireRow.Insert Shift:=xlShiftDown
ExcelObj.Range("A1&quot.Value = "Count"
ExcelObj.Range("B1&quot.Value = "Zip Code"
ExcelObj.Range("A1&quot.Select
'add totals
Set myRange = ActiveCell.CurrentRegion
Set myTotal = myRange.Offset(myRange.Rows.Count).Rows(1).Columns(1)
myTotal.Formula = "=SUM(" & myRange.Columns(1).Address(False, False) & &quot"
totZip = myTotal.Cells(1).Value

rstSource.Close

------------
There are 6 sheets that get added, all using somewhat similar code that works just great AS LONG AS I DON'T DO the four lines after the 'add totals comment. When I keep those lines of code in, I get an "Object Variable or with block variable not set". Of interest is that it can run ONCE for the zip sheet, but not twice. Here's the next sheet of code and the place where the error kicks in:


'add the income sheet
ExcelObj.Worksheets.Add
ExcelObj.ActiveSheet.Name = "Income"
Set rstSource = dtbCurrent.OpenRecordset("qryUWIncome&quot
ExcelObj.Worksheets("Income&quot.Range("A1&quot.CopyFromRecordset rstSource
ExcelObj.ActiveSheet.Range("A1&quot.Select
ExcelObj.Selection.EntireRow.Insert Shift:=xlShiftDown
ExcelObj.Range("A1&quot.Value = "Count"
ExcelObj.Range("B1&quot.Value = "Income"
ExcelObj.Range("A1&quot.Select
'add totals 'somewhere right here is where the error comes into play
Set myRange = ActiveCell.CurrentRegion
Set myTotal = myRange.Offset(myRange.Rows.Count).Rows(1).Columns(1)
myTotal.Formula = "=SUM(" & myRange.Columns(1).Address(False, False) & &quot"
totIncome = myTotal.Cells(1).Value

rstSource.Close

I'm being inconsistent in referencing the Excel application, and I'm guessing it's something along those lines - but I'm also confused when I have to/don't have to. Of interest is that the above code won't even run once if I add the ExcelObj variable (for application) in the code as follows:

Set myRange = ExcelObj.ActiveCell.CurrentRegion
Set myTotal = ExcelObj.myRange.Offset(myRange.Rows.Count).Rows(1).Columns(1)
ExcelObj.myTotal.Formula = "=SUM(" & myRange.Columns(1).Address(False, False) & &quot"
totIncome = ExcelObj.myTotal.Cells(1).Value

Can anyone see what I'm doing wrong and/or have suggestions? It all works fine in an Excel macro - which if I have to, I'll resort to (for many reasons don't want to do that). Many thanks!

Also of interest - maybe connected, maybe not - when the code fails, I'm unable to double click the excel files that get created in Explorer. I can open the Excel application and THEN go get the file just fine - but the system hangs when I try to open the file direct. Quickview even works on the files….weird. I was having trouble with the files staying open when I bombed, but with the following error handling this cleared:
ExcelObj.Quit
Set ExcelObj = Nothing
rstSource.Close
Set rstSource = Nothing
Set dtbCurrent = Nothing
Yet even with this, the file won't allow me to double click and open….. a totally separate question… not as important, but if anyone can shed any light on this one too I'd appreciate it!

Cindy K

 

[CindyK]

Hi again. I revised the code without using the range object and all is well. BUT anyone wanting to tackle the concept - I'm wondering what's going on. Is it possible that I am unable to create an Excel object in Access, even with the Excel library? It appears the only object I'm able to create is the actual application object...????

Thanks!

Cindy

 

[CindyK]

Me again, answering my own posts. But if anyone else sees this with a similar problem, I just had to reference the Excelobj in front of the range. Also cleaned up the code to use the with statement for the Excel Object, and then had to move the range OUTSIDE the with statement. Weird.

I often send people to this site, and tell them that if they don't get an answer within a couple days, that they probably did a poor job of asking the question. Guess I'm sitting in the guilty seat. But thanks to all for the time that is put into this forum!

Cindy