CODEWITHSUNDEEP
ms-accessvba
Akshay Kumar
Akshay Kumar

Reputation: 5

Error:- Method 'range' of object '_global' failed while running 2 functions together

I have made 2 functions "WrongEntries" & "Duplicates". When I run it individually it runs perfectly. But when I run it one after the other it reflects Method 'range' of object '_global' failed.

I am not sure is it because of the objects still in the memory of the previous run function but i did use different objects & variable in both functions still same error was there. Also tried putting objects as nothing.

Stange part is if I shuffle their run-first one always runs perfectly doesn't matter which one it is. 

Function WrongEntries()
Dim dbs As Database Dim lRow As Long Dim lCol As Long Dim rg As Range

Set dbs = CurrentDb
Set rgQuery = dbs.OpenRecordset("5G High Cycle Times")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.Workbooks.Open("\5G Wrong             
Entries_Blank.xlsx")
targetWorkbook.Worksheets("Cycle Times >5 
weeks").Range("A2").CopyFromRecordset rgQuery
targetWorkbook.Worksheets("Cycle Times >5 weeks").Activate

lRow = Range("b" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
excelApp.Quit
Set excelApp = Nothing
End Function

''' Problems is when I run second one together with first'''

Function Duplicates()

Dim dbs As Database Dim lastRw As Long Dim lastCl As Long Dim rnge As 
Range Dim wks As Worksheet
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set dbs = CurrentDb
Set rdQuery = dbs.OpenRecordset("5G Duplicates Check")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.Workbooks.Open("\5G Duplicates_Blank.xlsx")
Set wks = targetWorkbook.Worksheets("Duplicates")
targetWorkbook.Worksheets("Duplicates").Range("A2").CopyFromRecordset 
rdQuery

Here the error is reflecting while calculating last row count

lastRw = wks.Range("a" & Rows.Count).End(xlUp).Row
lastCl = wks.Cells(1, Columns.Count).End(xlToLeft).Column
End Function

Upvotes: 0

Views: 56

Answers (1)

Gustav
Gustav

Reputation: 55841

You close the application:

excelApp.Quit
Set excelApp = Nothing

leaving the workbook and all objects orphaned. So:

targetWorkbook.Close
Set targetWorkbook = Nothing
excelApp.Quit
Set excelApp = Nothing

Further: Always use specific objects. Not

Range("b" & Rows.Count).End(xlUp).Row
Cells(1, Columns.Count).End(xlToLeft).Column

but:

Set range = SomeWorksheet.Range ...
set cell = SomeWorksheet.Cells ...

and terminate these as the first:

Set cell = Nothing
Set range = Nothing
Set wks = nothing
targetWorkbook.Close
Set targetWorkbook = Nothing
excelApp.Quit
Set excelApp = Nothing

Upvotes: 2

Related Questions