2

I have a program that looks at a list of .dwg files in an excel file, opens the drawings one at a time, pulls the properties for given block references, then dumps those properties into a new sheet in the original excel file.

This code runs perfectly fine roughly half the time, with all the data accounted for and formatted properly, but the other half of the time I get Run-time Errors 9, 91 or 462.

I put the errors in parentheses in the line they're thrown.

What in my code is causing these errors, and if it's not a programming issue then what's the best way to write an ErrorHandler for these errors?

Set ExcelApp = GetObject(, "Excel.Application")
If ExcelApp Is Nothing Then
    Set ExcelApp = CreateObject("Excel.Application")
    If Not ExcelApp Then
        ExcelApp.Workbooks.Open (tempName)
    End If
End If

ExcelApp.DisplayAlerts = False
ExcelApp.Visible = False
Set wBook = ExcelApp.ActiveWorkbook
DoEvents

shtCount = wBook.Worksheets.Count ' ' (Error 91)
If shtCount <> 1 Then
    DoEvents
    wBook.Worksheets("Dump").Delete ' ' (Error 9, Despite that sheet existing)
    DoEvents
End If

Set wSheet = wBook.Worksheets.Add(After:=Worksheets("DrawSheets")) ' ' (Error 462)
DoEvents
wSheet.Name = "Dump"
DoEvents
John
  • 21
  • 4
  • Where is `shtCount` defined? That might be throwing your error 91. A quick google search also gives [a possible cause of your error 462](http://www.vbforums.com/showthread.php?667363-RESOLVED-VBA-Word-Excel-Error-462-when-running-the-code-twice) – SmrtGrunt Apr 10 '19 at 19:51
  • shtCount is DIM'd as an integer and defined in the same like Error 91 is thrown. I've already been through that thread and made the changes. Haven't helped – John Apr 11 '19 at 14:12
  • I would guess then that the error is coming from `Set wBook = ExcelApp.ActiveWorkbook`. If any window (clipboard, messagebox, etc) is open on top of Excel, then this will "fail" silently by setting `wBook` to `Nothing`. Then, when you go to count sheets in `wBook`, you'll get the Error 91. – SmrtGrunt Apr 11 '19 at 14:36
  • As I said below the Excel workbook is already open, so I'll have to test if the Set ExcelApp = GetObject(, "Excel.Application") in the first line brings Excel "to the front," so to speak, or if it does get set to Nothing. I'll check it out – John Apr 11 '19 at 21:12
  • Did that solve it, or is this one still unresolved? Now you've got me curious. – SmrtGrunt Apr 12 '19 at 15:57
  • I checked whether Excel gets brought "to the front" with the GetApplication command, and it seems it does. So sadly no, that didn't fix the issue. – John Apr 15 '19 at 12:23

1 Answers1

1

On first review, there are several issues with this block of code:

Set ExcelApp = GetObject(, "Excel.Application")
If ExcelApp Is Nothing Then
    Set ExcelApp = CreateObject("Excel.Application")
    If Not ExcelApp Then
        ExcelApp.Workbooks.Open (tempName)
    End If
End If

The statement:

Set ExcelApp = GetObject(, "Excel.Application")

Will raise a runtime error if Excel is not open; presumably, you have On Error Resume Next present in your code to bypass this error, or perhaps you have Excel open in your testing.

If Excel is already open, your code will never open your target workbook since the code will not reach the statement:

ExcelApp.Workbooks.Open (tempName)

Furthermore, the if statement surrounding the above expression makes no sense:

If Not ExcelApp Then
    ExcelApp.Workbooks.Open (tempName)
End If

Here, you are saying: "if the ExcelApp evaluates to False then use it to open the Workbook"

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • Thank you for your reply, I appreciate the help. You're correct that Excel is already open when this bit runs, and the source Excel workbook and target workbook are the same file, so my program opens Excel workbook -> .dwg file info removed -> drawing files scraped for data (with excels still open) -> data placed in open Excel workbook. I removed the "no sense" if statement. – John Apr 11 '19 at 14:02
  • @John You're welcome. Did my suggestion resolve the errors you were encountering? – Lee Mac Apr 11 '19 at 17:35
  • Unfortunately no, neither your suggestions or SG's above have helped. I made a workaround by using a basic .txt file instead of excel for now, but I'll try out what I mentioned in my last above and report back – John Apr 11 '19 at 21:14