Accessing open workbook in a sub generates Error 1004 "Method of 'Sheets' of Object '_Global' not failed" sometimes

Question

I am getting inconsistent results when I try to refer to an active workbook. About half the time I get the "Method of 'Sheets' of Object '_Global' not failed" error and other times the code works fine. I don't see a pattern.

The VBA code is part of a Word document that allows the user to open a template Excel file and select/copy text from the Word doc into rows on the Excel file.

In a previous sub I successfully open an Excel template file (I call it a RTM template). In the code below I want to activate the "RTM" worksheet, select the first cell where the template could already have data in it from a previous execution and if there is, then count how many rows of data exist. In this way the new data will be posted in the first row which does not have any data. I am using named ranges in my Workbook to refer to the starting cell ("First_Cell_For_Data").

When I run my code sometimes it runs without error and other times it stops on the "Sheets("RTM").Activate" and gives me the "Method...." error. The same result occurs when I change the variable definition of wb_open to Object. I have also tried using "wb_open.Sheets("RTM").Activate" with the same results.

As suggested in the comments below I added "If wb_open is nothing ...." to debug the issue. I also added the sub List_Open_Workbooks which enumerates the open workbooks (of which there is only 1) and activates the one that matches the name of the one with the correct filename. This is successful. But upon returning to Check_Excel_RTM_Template I still get the Method error on the "Sheets("RTM").Activate" line.

Second Update: after more time diagnosing the problem (which still occurs intermittently) I have added some code that may help getting to the root of the problem. In the "List_Open_Workbooks" sub I test for xlApp.Workbooks.Count = 0. So all references to an open Excel workbook will fail. At this point my template workbook is open in Windows. Am I drawing the correct conclusion?

Third Update: I tried Set wb_open = GetObject(str_filename) where str_filename contains the name of the Excel template file I just opened.

I get the following error message.

Also, I noticed that if I start with a fresh launch of Word and Excel it seems to run just fine.

    Sub Check_Excel_RTM_Template(b_Excel_File_Has_Data As Boolean, i_rows_of_data As Integer)
    Dim i_starting_row_for_data As Integer
    Dim wb_open As Object
    Set wb_open = ActiveWorkbook
    i_rows_of_data = 0
    If wb_open Is Nothing Then
        MsgBox "RTM Workbook not open in Check_Excel_RTM_Template"
        Call List_Open_Workbooks(b_Excel_File_Has_Data, i_rows_of_data)
    Else
 '   On Error GoTo Err1:
 '       Sheets("RTM").Activate
 '      range("First_Cell_For_Data").Select
        Workbooks(wb_open.Name).Worksheets("RTM").range("First_Cell_For_Data").Select
        If Trim(ActiveCell.Value) <> "" Then
           b_Excel_File_Has_Data = True
          Do Until Trim(ActiveCell.Value) = ""
               ActiveCell.Offset(1, 0).Select
               i_rows_of_data = i_rows_of_data + 1
           Loop
           Else
                b_Excel_File_Has_Data = False
        End If
    End If
    Exit Sub
Err1:
    MsgBox getName(str_Excel_Filename) & "  is not a RTM template file."
    b_abort = True
End Sub

Sub to enumerate all open workbooks

Sub List_Open_Workbooks(b_Excel_File_Has_Data As Boolean, i_rows_of_data As Integer)
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
Dim str_filename As String

Dim xlWB As Excel.Workbook
If xlApp.Workbooks.Count = 0 Then
    MsgBox "Error: Windows thinks there are no workbooks open in List_Open_Workbooks"
    b_abort = True
    Exit Sub
End If
For Each xlWB In xlApp.Workbooks
    Debug.Print xlWB.Name
    str_filename = getName(str_Excel_Filename)
    If Trim(xlWB.Name) = Trim(str_filename) Then
        xlWB.Activate
        If xlWB Is Nothing Then
            MsgBox "Workbook still not active in List_Open_Workbooks"
            b_abort = True
            Exit Sub
        Else
 '               Sheets("RTM").Activate
                Workbooks(xlWB.Name).Worksheets("RTM").range("First_Cell_For_Data").Select
                range("First_Cell_For_Data").Select
                If Trim(ActiveCell.Value) <> "" Then
                    b_Excel_File_Has_Data = True
                    Do Until Trim(ActiveCell.Value) = ""
                        ActiveCell.Offset(1, 0).Select
                        i_rows_of_data = i_rows_of_data + 1
                    Loop
                Else
                    b_Excel_File_Has_Data = False
                End If
        End If
    End If
Next xlWB

Set xlApp = Nothing
Set xlWB = Nothing
End Sub

Function to extract filename from path/filename

Function getName(pf)
getName = Split(Mid(pf, InStrRev(pf, "\") + 1), ".")(0) & ".xlsx"
End Function

Set a conditional breakpoint on the Sheets("RTM") line with the condition that Sheets Is Nothing, that will help you diagnose the issue. I suspect your macro is being invoked when there's no single active workbook. — Dai
@Dai - How do you get more than a single active workbook? — user4039065
@Jeeped It could be that no workbook is open, or a workbook is open but it's not active in Excel - or a different workbook is open that doesn't have a worksheet named "RTM". — Dai
@Dai that last case would be index out of bounds (9), not object or with block variable not set (91). Kaiser, Sheets(...) is implicitly referring to ActiveWorkbook, you should qualify it with an explicit Workbook object, e.g. wb_open.Sheets(...) - otherwise wb_open serves no purpose. Also verify whether it's Nothing before you make member calls against it. If wb_open Is Nothing Then Exit Sub would bail out if there's no active workbook. — Mathieu Guindon
If you have multiple instances of Excel open, GetObject will return the first matching process. This may or may not be the instance that you called List_Open_Workbooks from. If this code is called from inside Excel, there is no reason to use GetObject - just use the Application object where you need it. — Comintern

Kaiser Otto Kaiser Otto · Accepted Answer · 2018-09-05T05:12:27

I am hoping I found the source of my problem and solved it.

I believe that referring to an open workbook in sub using Dim wb_open As Object & Set wb_open = ActiveWorkbook in the Check_Excel_RTM_Template sub is causing my inconsistent problems....perhaps this is an anomoly (bug) in the VBA implementation in Word.

In the revised code I posted below I am passing the o_Excel object from the calling routine and using oExcel.Activesheet.xxx to reference ranges and values.

Now I next problem is that I am having errors on the form control button code which also uses the Dim wb_open As Object & Set wb_open = ActiveWorkbook approach to referring to the open workbook. But I'll post that as a new question.

Thanks to all who commented and provided suggestions.

Sub Check_Excel_RTM_Template(oExcel As Object)
    Dim i_starting_row_for_data As Integer
    Dim str_filename As String
    i_rows_of_data = 0
    On Error GoTo Err1:

        oExcel.ActiveSheet.range("First_Cell_For_Data").Select
        If Trim(oExcel.ActiveCell.Value) <> "" Then
           b_Excel_File_Has_Data = True
          Do Until Trim(oExcel.ActiveCell.Value) = ""
               oExcel.ActiveCell.Offset(1, 0).Select
               i_rows_of_data = i_rows_of_data + 1
           Loop
           Else
                b_Excel_File_Has_Data = False
        End If
    Exit Sub
Err1:
    Documents(str_doc_index).Activate
    MsgBox getName(str_Excel_Filename) & "  is not a RTM template file."
    b_abort = True
End Sub

Accessing open workbook in a sub generates Error 1004 "Method of 'Sheets' of Object '_Global' not failed" sometimes

1 Answers