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!


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)

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?


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.