Error: application defined or object defined error

[JFKJr]

Hi! I am a new beginner to MS Access VBA, I wrote the following VBA code in Access which deletes blank columns and rows in excel file. But, the code works fine for sometimes and sometimes it displays "applicatio n defined or object defined error".

And, when I step into the code, error is occuring at the following Bold line. Please help me with this problem. Thank you in advance.

Sub RunMacro()
On Error GoTo Err_RunMacro
Dim XL As Object
Set XL = CreateObject("E xcel.Applicatio n")
XL.Workbooks.Op en "C:\Test_file.x ls"

With XL.Application
Dim Col As Long, ColCnt As Long, Rng As Range, lastrow As Long, lastcol As Long
XL.ScreenUpdati ng = False
XL.Calculation = xlCalculationMa nual

On Error GoTo Exits:
lastcol = Range("A:A").Sp ecialCells(xlLa stCell).Column
Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCell s(xlCellTypeBla nks).EntireColu mn.Delete

lastrow = Range("A:A").Sp ecialCells(xlLa stCell).Row
Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCell s(xlCellTypeBla nks).EntireRow. Delete

Exits:
XL.ScreenUpdati ng = True
XL.Calculation = xlCalculationAu tomatic
If Err.Description = "" Then
'MsgBox "" & lastcol
Else
MsgBox Err.Description
'MsgBox "" & lastcol
End If
End With

XL.Workbooks("T est_file.xls"). SaveAs FileName:="C:\E xportFile\Test_ file.xls"
XL.Quit
XL.Workbooks.Cl ose
Set XL = Nothing
Exit_RunMacro:
Exit Sub
Err_RunMacro:
MsgBox Err.Description
End Sub

[Stewart Ross]

Hi. The problem is that you are using implicit references to worksheet ranges, which works fine in Excel itself (as the code is running within a specific workbook) but not when running VBA code using Excel as an automation server.

You will need to qualify all such implicit references to the active worksheet or activeworkbook explictly, using the Excel object or a sub-object (a range, for example) defined from it.

There is more than one line in your code which has such implicit references to the current active worksheet. To refer to these explicitly it is easiest to define a range object, set it to the relevant range, then use it as shown below. (I have removed the With statement which was not providing much in the way of simplification of your code, and adjusted the Excel object references in the first lines.)

Sub RunMacro()
On Error GoTo Err_RunMacro
    Dim XL As Excel.Application
    Dim xlRange as Range
    Set XL = New Excel.Application 
    XL.Workbooks.Open "C:\Test_file.xls"
 
    Dim Col As Long, ColCnt As Long
    Dim Rng As Range
    Dim lastrow As Long, lastcol As Long
 
    XL.ScreenUpdating = False
    XL.Calculation = xlCalculationManual
 
On Error GoTo Exits
    set xlRange = .ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
    lastcol = xlRange.Column
    XL.Activesheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
 
    lastrow = xlRange.Row
    XL.Activesheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Exits:
    XL.ScreenUpdating = True
    XL.Calculation = xlCalculationAutomatic
    If Err.Description = "" Then
        'MsgBox "" & lastcol
    Else
        MsgBox Err.Description
        'MsgBox "" & lastcol
    End If
 
    XL.Workbooks("Test_file.xls").SaveAs 
    FileName:="C:\ExportFile\Test_file.xls"
    XL.Quit
    XL.Workbooks.Close
    Set XL = Nothing
Exit_RunMacro:
    Exit Sub
Err_RunMacro:
    MsgBox Err.Description
End Sub

It is helpful if you use the code tags to enclose your code samples - it makes seeing the structure of your code much easier.

-Stewart

[JFKJr]

Hi. The problem is that you are using implicit references to worksheet ranges, which works fine in Excel itself (as the code is running within a specific workbook) but not when running VBA code using Excel as an automation server.

You will need to qualify all such implicit references to the active worksheet or activeworkbook explictly, using the Excel object or a sub-object (a range, for example) defined from it.

There is more than one line in your code which has such implicit references to the current active worksheet. To refer to these explicitly it is easiest to define a range object, set it to the relevant range, then use it as shown below. (I have removed the With statement which was not providing much in the way of simplification of your code, and adjusted the Excel object references in the first lines.)

Sub RunMacro()
On Error GoTo Err_RunMacro
    Dim XL As Excel.Application
    Dim xlRange as Range
    Set XL = New Excel.Application 
    XL.Workbooks.Open "C:\Test_file.xls"
 
    Dim Col As Long, ColCnt As Long
    Dim Rng As Range
    Dim lastrow As Long, lastcol As Long
 
    XL.ScreenUpdating = False
    XL.Calculation = xlCalculationManual
 
On Error GoTo Exits
    set xlRange = .ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
    lastcol = xlRange.Column
    XL.Activesheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
 
    lastrow = xlRange.Row
    XL.Activesheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Exits:
    XL.ScreenUpdating = True
    XL.Calculation = xlCalculationAutomatic
    If Err.Description = "" Then
        'MsgBox "" & lastcol
    Else
        MsgBox Err.Description
        'MsgBox "" & lastcol
    End If
 
    XL.Workbooks("Test_file.xls").SaveAs 
    FileName:="C:\ExportFile\Test_file.xls"
    XL.Quit
    XL.Workbooks.Close
    Set XL = Nothing
Exit_RunMacro:
    Exit Sub
Err_RunMacro:
    MsgBox Err.Description
End Sub

It is helpful if you use the code tags to enclose your code samples - it makes seeing the structure of your code much easier.

-Stewart

Hello Stewart, thanks a lot for the reply

I have used the above modified code as it is, but it is giving me "Compile error: Invalid or unqualified reference". When I step into the code, the error is occurring at ".ActiveShe et" on line number 16.

Please let me know what to do. Thanks.

[Stewart Ross]

Ahh, one of the WITH elements that I overlooked... change it to XL.Activesheet. ... (rest as before)

-Stewart

[JFKJr]

Ahh, one of the WITH elements that I overlooked... change it to XL.Activesheet. ... (rest as before)

-Stewart

Hello Stewart, the error is repeating again

When I run the following code, it worked fine for the first time and during the second time it is generating "Error application defined or object defined error ".

But, this time when I step into the code, it is entering into "Exits" case after 15th line below.
Please help me what to do. Thanks.

Sub RunMacro()
On Error GoTo Err_RunMacro
    Dim XL As Excel.Application
    Dim xlRange As Range
    Set XL = New Excel.Application
    XL.Workbooks.Open "C:\Test_file3.xls"
 
    Dim lastrow As Long, lastcol As Long
    XL.ScreenUpdating = False
    XL.Calculation = xlCalculationManual
 
On Error GoTo Exits
    Set xlRange = XL.ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
    lastcol = xlRange.Column
    XL.ActiveSheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
 
    lastrow = xlRange.Row
    XL.ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Exits:
    XL.ScreenUpdating = True
    XL.Calculation = xlCalculationAutomatic
    If Err.Description = "" Then
        'MsgBox "" & lastcol
    Else
        MsgBox Err.Description
        'MsgBox "" & lastcol
    End If
 
    XL.Workbooks("Test_file3.xls").SaveAs FileName:="C:\ExportFile\Test_file3.xls"
    XL.Quit
    XL.Workbooks.Close
    Set XL = Nothing
Exit_RunMacro:
    Exit Sub
Err_RunMacro:
    MsgBox Err.Description
End Sub

By the way, I am using MS Access 2000 format.

[Stewart Ross]

HI. Its a domino chain of errors caused by implicit references. This time the Cells references are causing the failure, because there is no explicit range object for the cells object to refer to. (As a side note you have reversed the row and column references in the second cells part of line 15).

The easiest way to resolve this is to surround that part of the code with a WITH and use the '.' property to refer to the active sheet for the Cells object as part of the WITH:

With XL.ActiveSheet
    .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
 
lastrow = xlRange.Row
.Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With 
 

[JFKJr]

HI. Its a domino chain of errors caused by implicit references. This time the Cells references are causing the failure, because there is no explicit range object for the cells object to refer to. (As a side note you have reversed the row and column references in the second cells part of line 15).

The easiest way to resolve this is to surround that part of the code with a WITH and use the '.' property to refer to the active sheet for the Cells object as part of the WITH:

With XL.ActiveSheet
    .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
 
lastrow = xlRange.Row
.Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With 
 

Sorry to bother you once again!

But, when I run the following code, it is generating "Object Required" error at 17th line below and entering into "Exits" case.

Sub RunMacro()
On Error GoTo Err_RunMacro
    Dim XL As Excel.Application
    Dim xlRange As Range
    Set XL = New Excel.Application
    XL.Workbooks.Open "C:\Test_file1.xls"
 
    Dim lastrow As Long, lastcol As Long
    XL.ScreenUpdating = False
    XL.Calculation = xlCalculationManual
 
    With XL.ActiveSheet
    On Error GoTo Exits
    Set xlRange = .Range("A:A").SpecialCells(xlLastCell)
    lastcol = xlRange.Column
    .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    lastrow = xlRange.Row
    .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Exits:
    XL.ScreenUpdating = True
    XL.Calculation = xlCalculationAutomatic
    If Err.Description = "" Then
        'MsgBox "" & lastcol
    Else
        MsgBox Err.Description
        MsgBox "" & lastcol
    End If
    End With
 
    XL.Workbooks("Test_file1.xls").SaveAs FileName:="C:\ExportFile\Test_file1.xls"
    XL.Quit
    XL.Workbooks.Close
    Set XL = Nothing
Exit_RunMacro:
    Exit Sub
Err_RunMacro:
    MsgBox Err.Description
End Sub

Hello Stewart!

I got the code working, I just reversed the row and column references in the second cell part of line 15 as following (as we did before):

.Range(.Cells(1, 1), .Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete

Because with the following line all columns in the excel file are getting deleted and there are no rows left.

.Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete

Thanks a loooooooot for your help. Thank You very much :). Your forum rocks!

[Stewart Ross]

Hi JFKjr. Thanks for your kind words. I am very glad you got your code to work out for you, and pleased to have been able to help.

Welcome to Bytes!

Regards

Stewart