0

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
15
Views
2 Years
Discussion Span
Last Post by doncwilson_1
0

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)

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.