Range Global error if run same code second time

[Rx_]

Situation: Using Range object in VBA code. It runs perfect the first time. The second or more times, there is a Global error. Close Access, and it runs the first time, and errors on the second or more times.

Set Excel into an object - and destroying all objexts (i.e. set objExcel = Nothing) hs no effect.

Found an article that showe this to be a known problem for the Range object (including Charts) when the report is run over and over again.

This fixed it for me: Add ActiveSheet. in front of Range.

From now on, like eating chicken soup when your sick ... " it couldn't hurt"
this cost me an entire morning. Found the solution elsewhere and wanted to make it avilable to our community.
If you find this kind of tip useful, please add to my reputation. It will encourage me to add more.

Running code from inside Access.
Remember to set a Reference to the Excel Application.

[B]Set objXL = New Excel.Application[/B]
[B]The code below then calls methods of the objXL object[/B]
Code fragment sorts through Excel column " M " returned by CopyRecordset method. The code removes the hidden quotes that make the data "text" which prevents the SubTotal(101, <range>) from working.
The Excel cell format will not convert the values to numbers in this case.
 
Before adding "AceiveSheet." this would run perfectlly once.. then fail every time afterwards. 
 
' the ActiveSheet.   in the next statement prevents a global error if 
' this code is run a second time
Set rnOmrade = NothingSet rnOmrade = objXL.ActiveSheet.Range(objXL.Range("M5"), objXL.Range("M6536").End(xlUp))vaData = rnOmrade.Value
For i = 1 To UBound(vaData) ' data base record set returns "Pending", blank or a number
    If (vaData(i, 1) = "Pending") Then
         vaData(i, 1) = "Pending"
    ElseIf ((vaData(i, 1) * 1) = 0) Then 
          vaData(i, 1) = ""
     Else
         vaData (i, 1) = vaData(i, 1) * 1 
    End If
'vaData(i, 1) = IIf(IsNumeric(vaData(i, 1) * 1), vaData(i, 1) * 1, vaData(i, 1))
Next i
arrayrnOmrade.Value = vaData

 

[gypsyjoe11]

Thanks for the advice. I've found out the same thing the hard way also.

You need to use strong naming conventions unlike inside Excel.

 

[SOS]

Actually, the problem is not that you are using Activesheet. You can use a specific worksheet as well. But the problem is that each object MUST be tied somehow to the application object and if not then it will actually instantiate another Excel application object (hidden from you) and it will stay open until you either close Access or kill it in the Task Manager. So, you never want to use any Excel objects that are not explicitly tied to the main instantiation of Excel that you create.

For example, if you have objXL as an Application object and then you have a rng As Range object, you must set it using something from objXL - like Set rng = objXL.Worksheets("Sheet1").Range("B2:C5") and NOT like

Set rng = Activesheet.Range("B2:C5")

because that Activesheet in that context was not tied to objXL, you then end up with the hidden instantiated object.

If you use

Set rng = ObjXL.ActiveSheet.Range("B2:C5")

Then it will be fine.

Hope that actually helps explain it a bit more.