Accessing ranges in Excel from Access VBA errors every other run

[nevits]

I’ve written a VBA function in Access that parses a form with 25 or so controls, builds a SQL expression and fetches the data. I thought that would be the hard part.

Then I got the bright idea of sending the data to Excel, formatting it and then adding a filter. I haven’t used the Excel objects, but it looked fairly straight forward and it was or so I thought…

What is causing me the grief is that every other time I run the code it generates either error “1004 Method ‘Rage’ of object ‘_worksheet’ failed or error “91 object variable or With block variable not set”. The errors pop up randomly in any of the 5 sections of code that access the worksheet object.

This week’s theory is that the range objects have random data left in them from the previous run and need to be initialized, but I don’t see how to initialize them.

Any thoughts?

Here is the relevant section of code.

' --------- Get Data
    Set DB = CurrentDb()
     Set qs = DB.CreateQueryDef("", Select_str & From_str & Where_str & Group_by_str & ";")
     Set RS = qs.OpenRecordset
      
'--------- get number of rows returned
    RS.MoveFirst
    RS.MoveLast
    num_recs = RS.RecordCount
    num_fields = qs.Fields.Count
    RS.MoveFirst

'---------- set up Excel
   Set appexcel = New Excel.Application
   Set WB = appexcel.Workbooks.Add
   Set ws = WB.worksheets.Add
   appexcel.Visible = True
   
'--------- So far so good.  The error will pop up anywhere in the next five sections of code every other run

    
'---------  #1 set up column heading and set range for formating

output_row = 6
   For x = 0 To num_fields - 1
        ws.Cells(output_row, x + 1).Value = qs.Fields(x).NAME
       
   Next
        Set Col_Heading_range = ws.Range(ws.Cells(output_row, 1), ws.Cells(output_row, num_fields + 1))
        With Col_Heading_range
            .Cells.Font.Bold = True
            .Select
             With Selection.Interior
                .ColorIndex = 34
                .Pattern = xlSolid
            End With
        End With

        
       
 '---------- #2 set range and output data
 
   Set out_range = ws.Range(ws.Cells(output_row + 1, 1), ws.Cells(output_row + num_recs + 1, num_fields))
   out_range.CopyFromRecordset RS
   ws.Columns.AutoFit

'---------- #3 set up range for numberic data and format (need to address percents)

   Set number_range = ws.Range(ws.Cells(output_row + 1, num_fields - 9), ws.Cells(output_row + num_recs + 1, num_fields + 1))
   number_range.Style = "comma"
   
  
'---------- #4 set up range for all data and turn on auto filter
  Set row_header_range = ws.Range(ws.Cells(output_row, 1), Cells(num_recs + 1, num_fields - 9))
  row_header_range.AutoFilter
  
'---------- #5 create subtotals for selected data
    For x = 1 To 8
        ws.Cells(output_row - 1, num_fields - x).Activate
        With ActiveCell
            Set formula_range = Range(.Offset(1), .Offset(1).End(xlDown))
            .Formula = "=subtotal(9," & formula_range.Address & ")"
            .Style = "comma"
            .Font.Bold = True
        End With
     Next
     
    ws.Columns.AutoFit
End Function

 

[RoyVidar]

I find two occurences of implicit referencing, which is cause of such symptoms, the first is

[tt] With Col_Heading_range
.Cells.Font.Bold = True
.Select
With Selection.Interior[/tt]

where the selection isn't "anchored" anywhere. Without testing, I assume someting like the following should work

[tt] With Col_Heading_range
.Cells.Font.Bold = True
With .Interior[/tt]

the other is

[tt] ws.Cells(output_row - 1, num_fields - x).Activate
With ActiveCell[/tt]

where Activecell isn't anchored either, try

[tt] With ws.Cells(output_row - 1, num_fields - x)[/tt]

see for instance

http://support.microsoft.com/default.aspx?kbid=178510

and

http://support.microsoft.com/default.aspx?scid=kb;EN-US;319832

for more info

Roy-Vidar

 

[PHV]

You have to always use full qualified excel objects.

Replace this:
With Selection.Interior
with this:
With appexcel.appSelection.Interior

and this:
Set row_header_range = ws.Range(ws.Cells(output_row, 1), Cells(num_recs + 1, num_fields - 9))
with this:
Set row_header_range = ws.Range(ws.Cells(output_row, 1), ws.Cells(num_recs + 1, num_fields - 9))

and this:
With ActiveCell
with this:
With appexcel.ActiveCell

and this:
Set formula_range = Range(.Offset(1), .Offset(1).End(xlDown))
with this:
Set formula_range = ws.Range(.Offset(1), .Offset(1).End(xlDown))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RoyVidar]

Two more

[tt] Set row_header_range = ws.Range(ws.Cells(output_row, 1), Cells(num_recs + 1, num_fields - 9))[/tt]

needs to be

[tt] Set row_header_range = ws.Range(ws.Cells(output_row, 1), ws.Cells(num_recs + 1, num_fields - 9))[/tt]

[tt]Set formula_range = Range(.Offset(1), .Offset(1).End(xlDown))[/tt]

needs to be

[tt]Set formula_range = ws.Range(.Offset(1), .Offset(1).End(xlDown))[/tt]


Roy-Vidar

 

[nevits]

Brilliant, thanks to you both.