If wbk open do nothing, if not open it

Question

can someone tell me what's wrong with my code? I'm getting a runtime error 424 - object required here:

If MasterList Is Nothing Then

On Error Resume Next

Set MasterList = Workbooks("c:test.xls")

On Error GoTo 0

If MasterList Is Nothing Then

Set MasterList = Workbooks.Open("c:test.xls")

Else: End If

i'm trying to define c:test.xls as MasterList but the workbook may not necessarily be open. if it's not open, i want the macro to open the file. If it is already open then i want the macro to define it as MasterList.

Any ideas?

have you tried "C:\test.xls"? usually a file reference won't work without the backslash, — DeanOC, Feb 27 '15 at 02:46
Use the function in [this answer](http://stackoverflow.com/a/22309698/2548721). — WGS, Feb 27 '15 at 02:46

score 0 · Answer 1 · answered Feb 27 '15 at 17:59

I believe these macros demonstrate what you need to know.

Demo1 outputs the paths and names of the workbooks open within the current copy of Excel.

In Demo2, I test for a particular name and open it if it is not found. Note how I use the path of the workbook holding the macro. I normally keep related macro in the same folder so this can be convenient.

There may be a problem with "c:test.xls" although not quite the problem mentioned in the comment.

"c:test.xls" references the file "test.xls" within the current directory of drive C.

"c:\test.xls" references the file "test.xls" within the root directory of drive C.

Try Debug.Print CurDir. You will probably get C:\Users\YourUserName\Documents. Is this the location of "test.xls"

Option Explicit
Sub Demo1()

  Dim InxWbk As Long

  For InxWbk = 1 To Workbooks.Count
    Debug.Print "Path=[" & Workbooks(InxWbk).Path & "] Name=[" & Workbooks(InxWbk).Name & "]"
  Next

End Sub
Sub Demo2()

  Dim Found As Boolean
  Dim InxWbk As Long
  Dim MasterList As Workbook

  Found = False
  For InxWbk = 1 To Workbooks.Count
    If Workbooks(InxWbk).Name = "Fruit.xls" Then
      Set MasterList = Workbooks(InxWbk)
      Found = True
      Exit For
    End If
  Next

  If Not Found Then
    Set MasterList = Workbooks.Open(ThisWorkbook.Path & "\Fruit.xls")
  End If

  Call Demo1

End Sub

If wbk open do nothing, if not open it

1 Answers1