Member Avatar for Papa_Don

Hi group,

I've written some VBA code behind an Excel spreadsheet to check to see if another workbook is open. If it isn't open, I'm having the code open it for me. This works fine the first time the macro runs. However this same spreadsheet has a cell where a property number is changed. When this is done and it is time to run the macro for the second time, the second worbook never opens and I go into some kind of endless loop. Can someone suggest why this may be happening?

Here's the code I'm running to check to see if it's open and then to open it if it is not:

    wbFile = "O:\Revenue Management\RM -- Specialty Select Brands\Weekly - 14 Day Forecast\" & scoreCard
    On Error Resume Next
    Set wBook = Workbooks("14D Scorecard.xlsx")
    If wBook Is Nothing Then
        Exit Sub
    End If

FYI: "14D Scorecard.xlsx" is the second workbook that I've referred to above.

If you have some thoughts, I will greatly appreciate them. I've search the internet high and low and I can't find a reason why it would do this. Further, I'm not getting any kind of error message.

In advance, Thanks!

Don

  • 2 Contributors
  • 2 Replies
  • 308 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Papa_Don

Dani AI

Generated

The symptom described by (opens first time, closed, then never opens on the second run) and ’s note about error masking point to two likely causes: the check that detects whether the other workbook is open is inverted, and using On Error Resume Next hides the real failure. The snippet shown calls Workbooks("14D Scorecard.xlsx") and then exits if the result is Nothing — that will return immediately instead of opening the file. Hiding errors with On Error Resume Next makes it hard to see why the code never reaches the open step.

A safer pattern is an explicit “is open” routine plus a clear open-or-attach flow and proper error handling. Example:

Function WorkbookIsOpen(ByVal wbName As String) As Boolean
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        If StrComp(wb.Name, wbName, vbTextCompare) = 0 Then
            WorkbookIsOpen = True
            Exit Function
        End If
    Next
    WorkbookIsOpen = False
End Function

' Usage (replace filePath with the real path)
Dim target As String, filePath As String
target = "14D Scorecard.xlsx"
filePath = "FULL_PATH_TO_FOLDER\" & target

If Not WorkbookIsOpen(target) Then
    Set wBook = Workbooks.Open(filePath)
Else
    Set wBook = Workbooks(target)
End If

Additional practical notes and checks: remove or replace On Error Resume Next with On Error GoTo ErrHandler while debugging so errors surface; ensure the name and extension exactly match (.xlsm vs .xlsx, no trailing spaces); always wBook.Close SaveChanges:=... and Set wBook = Nothing when finished; be aware the Application.Workbooks collection only sees workbooks in the same Excel instance (a workbook opened in a separate Excel process won’t be found). Adding a few Debug.Print lines or breakpoints will quickly show whether the code exits early or an error is being swallowed. Applying these changes typically prevents the “second run does nothing / endless loop” behavior.

Member Avatar for Alicera Nz

The code does not look clear for me.
But "The endless loop" is caused by On Error Resume Next
Try to change it into: on error goto ..... to check if there is something wrong in your code (why it(2nd) can't open)

Member Avatar for Papa_Don

Alicera Nz, I may have not be clear enough, so let me restate the issue:

The macro that opens "14D Scorecard.xlsx" works perfectly the first time. This same macro further down in the code closes this workbook after it is updated with new information.

The user will then make some changes to the primary document (this is the document the macro is in). The user will then start this macro again for the second time to open "14D Scorecard.xlsx", but this time the "14D Scorecard.xlsx" document does not open.

I'm not sure why "On Error Resume Next" is the problem. I'm using this to check to see if the report is open. If it is not open, I then want to open the report. Is there a better way to check to see if the report is open?

Don

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.