Checking if a workbooks is open

[Carly]

Please could someone look at the code below as it doesn't seem to work:

If (Windows("MarginRecoveryPlan - TEMPLATE.xlsx").Activate Is Nothing) Then

Else

Workbooks.Open "\\Stoke\analytic$\Price Increase\MarginRecoveryPlan " & vMonth & "\Spreadsheets\MarginRecoveryPlan - TEMPLATE.xlsx"

End If

I am trying to check if the workbook is open & if not, open it.

Any help would be greatly received.

Kind Regards

Carly

 

[Richard Schollar]

Hi Carly

You need something like this:

Dim wb As Workbook
 
On Error Resume Next
Set wb = Workbooks("YourWorkbookName.xls")
On Error Goto 0
 
If wb Is Nothing Then _
  Set wb = Workbooks.Open("C:\Folder\YourWorkbookName.xls")

 

[Boller]

An internet search for "is workbook open vba" provides a lot of solutions.

 

[Carly]

The above code helped, thank you very much...

I had already searched & got the code I had, which is why I needed some help as it didnt work.

 

[Carly]

Hi,

I was wondering if someone could help me with another problem I'm having with this code:

Dim wb1 As Workbook
On Error Resume Next

Set wb1 = Workbooks("MarginRecoveryPlan - TEMPLATE.xlsx")

If wb1 Is Nothing Then
GoTo Z
End If

This code works fine for ages (as it is within a loop) checking that the spreadsheet is not there & jumping to Z, then twice in a row it finds the workbook & ignores the Go To Z bit fine, but when the next loop comes around the workbook is not there again but it won't go into the IF statement.

Is there anything else I need to go with it, anything else that could be stopping it setting it to nothing?

I appreciate this is only a very small piece of my code, but was just hoping someone would have a bright idea before I continued with my debugging.

 

[Carly]

This is now sorted...Needed 'Set wb1 = Nothing' adding into the code.