By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Error: application defined or object defined error

100+
P: 126
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 "application 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("Excel.Application")
XL.Workbooks.Open "C:\Test_file.xls"

With XL.Application
Dim Col As Long, ColCnt As Long, Rng As Range, lastrow As Long, lastcol As Long
XL.ScreenUpdating = False
XL.Calculation = xlCalculationManual

On Error GoTo Exits:
lastcol = Range("A:A").SpecialCells(xlLastCell).Column
Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.De lete

lastrow = Range("A:A").SpecialCells(xlLastCell).Row
Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e

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_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
Jul 10 '08 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
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.)

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro()
  2. On Error GoTo Err_RunMacro
  3.     Dim XL As Excel.Application
  4.     Dim xlRange as Range
  5.     Set XL = New Excel.Application 
  6.     XL.Workbooks.Open "C:\Test_file.xls"
  7.  
  8.     Dim Col As Long, ColCnt As Long
  9.     Dim Rng As Range
  10.     Dim lastrow As Long, lastcol As Long
  11.  
  12.     XL.ScreenUpdating = False
  13.     XL.Calculation = xlCalculationManual
  14.  
  15. On Error GoTo Exits
  16.     set xlRange = .ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
  17.     lastcol = xlRange.Column
  18.     XL.Activesheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  19.  
  20.     lastrow = xlRange.Row
  21.     XL.Activesheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  22.  
  23. Exits:
  24.     XL.ScreenUpdating = True
  25.     XL.Calculation = xlCalculationAutomatic
  26.     If Err.Description = "" Then
  27.         'MsgBox "" & lastcol
  28.     Else
  29.         MsgBox Err.Description
  30.         'MsgBox "" & lastcol
  31.     End If
  32.  
  33.     XL.Workbooks("Test_file.xls").SaveAs 
  34.     FileName:="C:\ExportFile\Test_file.xls"
  35.     XL.Quit
  36.     XL.Workbooks.Close
  37.     Set XL = Nothing
  38. Exit_RunMacro:
  39.     Exit Sub
  40. Err_RunMacro:
  41.     MsgBox Err.Description
  42. 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
Jul 10 '08 #2

100+
P: 126
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.)

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro()
  2. On Error GoTo Err_RunMacro
  3.     Dim XL As Excel.Application
  4.     Dim xlRange as Range
  5.     Set XL = New Excel.Application 
  6.     XL.Workbooks.Open "C:\Test_file.xls"
  7.  
  8.     Dim Col As Long, ColCnt As Long
  9.     Dim Rng As Range
  10.     Dim lastrow As Long, lastcol As Long
  11.  
  12.     XL.ScreenUpdating = False
  13.     XL.Calculation = xlCalculationManual
  14.  
  15. On Error GoTo Exits
  16.     set xlRange = .ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
  17.     lastcol = xlRange.Column
  18.     XL.Activesheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  19.  
  20.     lastrow = xlRange.Row
  21.     XL.Activesheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  22.  
  23. Exits:
  24.     XL.ScreenUpdating = True
  25.     XL.Calculation = xlCalculationAutomatic
  26.     If Err.Description = "" Then
  27.         'MsgBox "" & lastcol
  28.     Else
  29.         MsgBox Err.Description
  30.         'MsgBox "" & lastcol
  31.     End If
  32.  
  33.     XL.Workbooks("Test_file.xls").SaveAs 
  34.     FileName:="C:\ExportFile\Test_file.xls"
  35.     XL.Quit
  36.     XL.Workbooks.Close
  37.     Set XL = Nothing
  38. Exit_RunMacro:
  39.     Exit Sub
  40. Err_RunMacro:
  41.     MsgBox Err.Description
  42. 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 ".ActiveSheet" on line number 16.

Please let me know what to do. Thanks.
Jul 10 '08 #3

Expert Mod 2.5K+
P: 2,545
Ahh, one of the WITH elements that I overlooked... change it to XL.Activesheet. ... (rest as before)

-Stewart
Jul 10 '08 #4

100+
P: 126
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.

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro()
  2. On Error GoTo Err_RunMacro
  3.     Dim XL As Excel.Application
  4.     Dim xlRange As Range
  5.     Set XL = New Excel.Application
  6.     XL.Workbooks.Open "C:\Test_file3.xls"
  7.  
  8.     Dim lastrow As Long, lastcol As Long
  9.     XL.ScreenUpdating = False
  10.     XL.Calculation = xlCalculationManual
  11.  
  12. On Error GoTo Exits
  13.     Set xlRange = XL.ActiveSheet.Range("A:A").SpecialCells(xlLastCell)
  14.     lastcol = xlRange.Column
  15.     XL.ActiveSheet.Range(Cells(1, 1), Cells(lastcol, 1)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  16.  
  17.     lastrow = xlRange.Row
  18.     XL.ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  19.  
  20. Exits:
  21.     XL.ScreenUpdating = True
  22.     XL.Calculation = xlCalculationAutomatic
  23.     If Err.Description = "" Then
  24.         'MsgBox "" & lastcol
  25.     Else
  26.         MsgBox Err.Description
  27.         'MsgBox "" & lastcol
  28.     End If
  29.  
  30.     XL.Workbooks("Test_file3.xls").SaveAs FileName:="C:\ExportFile\Test_file3.xls"
  31.     XL.Quit
  32.     XL.Workbooks.Close
  33.     Set XL = Nothing
  34. Exit_RunMacro:
  35.     Exit Sub
  36. Err_RunMacro:
  37.     MsgBox Err.Description
  38. End Sub
By the way, I am using MS Access 2000 format.
Jul 10 '08 #5

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. With XL.ActiveSheet
  2.     .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  3.  
  4. lastrow = xlRange.Row
  5. .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  6. End With 
  7.  
Jul 10 '08 #6

100+
P: 126
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:
Expand|Select|Wrap|Line Numbers
  1. With XL.ActiveSheet
  2.     .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  3.  
  4. lastrow = xlRange.Row
  5. .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  6. End With 
  7.  
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.

Expand|Select|Wrap|Line Numbers
  1. Sub RunMacro()
  2. On Error GoTo Err_RunMacro
  3.     Dim XL As Excel.Application
  4.     Dim xlRange As Range
  5.     Set XL = New Excel.Application
  6.     XL.Workbooks.Open "C:\Test_file1.xls"
  7.  
  8.     Dim lastrow As Long, lastcol As Long
  9.     XL.ScreenUpdating = False
  10.     XL.Calculation = xlCalculationManual
  11.  
  12.     With XL.ActiveSheet
  13.     On Error GoTo Exits
  14.     Set xlRange = .Range("A:A").SpecialCells(xlLastCell)
  15.     lastcol = xlRange.Column
  16.     .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
  17.     lastrow = xlRange.Row
  18.     .Range(.Cells(1, 1), .Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  19.  
  20. Exits:
  21.     XL.ScreenUpdating = True
  22.     XL.Calculation = xlCalculationAutomatic
  23.     If Err.Description = "" Then
  24.         'MsgBox "" & lastcol
  25.     Else
  26.         MsgBox Err.Description
  27.         MsgBox "" & lastcol
  28.     End If
  29.     End With
  30.  
  31.     XL.Workbooks("Test_file1.xls").SaveAs FileName:="C:\ExportFile\Test_file1.xls"
  32.     XL.Quit
  33.     XL.Workbooks.Close
  34.     Set XL = Nothing
  35. Exit_RunMacro:
  36.     Exit Sub
  37. Err_RunMacro:
  38.     MsgBox Err.Description
  39. 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):

Expand|Select|Wrap|Line Numbers
  1. .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.

Expand|Select|Wrap|Line Numbers
  1. .Range(.Cells(1, 1), .Cells(1, lastcol)).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
Thanks a loooooooot for your help. Thank You very much :). Your forum rocks!
Jul 10 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Jul 10 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.