Check if file exists / open it
 

For some extra functions I created a second workbook (wb2.xls). In Sheet1!A1
(wb1.xls) is the filename (created with a formula). This works fine:

Sub Test1()
Workbooks.Open Filename:= _
Range("Sheet1!A1").Value
End Sub

But this doesn't work:

Sub Test2()
Option Explicit
Dim mywkbk As Workbook
Dim myfile As String
myfile = Range("sheet1!A1").Value
If Dir(myfile) = "" Then
MsgBox "that doesn't exist!"
Exit Sub
Else
Set mywkbk = Workbooks.Open(myfile)
End If
End Sub

Can someone help??

Check if file exists / open it
 

Try this:
If Not Len(Dir(myfile)) 0 Then
MsgBox "that doesn't exist"
Else....

Charles

Gert-Jan wrote:
For some extra functions I created a second workbook (wb2.xls). In Sheet1!A1
(wb1.xls) is the filename (created with a formula). This works fine:

Sub Test1()
Workbooks.Open Filename:= _
Range("Sheet1!A1").Value
End Sub

But this doesn't work:

Sub Test2()
Option Explicit
Dim mywkbk As Workbook
Dim myfile As String
myfile = Range("sheet1!A1").Value
If Dir(myfile) = "" Then
MsgBox "that doesn't exist!"
Exit Sub
Else
Set mywkbk = Workbooks.Open(myfile)
End If
End Sub

Can someone help??

Check if file exists / open it
 

There are a couple of problems I see in your code. Give this a try...

Option Explicit

Sub Test2()
Dim mywkbk As Workbook
Dim myfile As String

myfile = Worksheets("Sheet1").Range("A1").Value
If Dir(myfile) = "" Then
MsgBox "that doesn't exist!"
Else
Set mywkbk = Workbooks.Open(myfile)
End If
End Sub

--
HTH...

Jim Thomlinson


"Gert-Jan" wrote:

For some extra functions I created a second workbook (wb2.xls). In Sheet1!A1
(wb1.xls) is the filename (created with a formula). This works fine:

Sub Test1()
Workbooks.Open Filename:= _
Range("Sheet1!A1").Value
End Sub

But this doesn't work:

Sub Test2()
Option Explicit
Dim mywkbk As Workbook
Dim myfile As String
myfile = Range("sheet1!A1").Value
If Dir(myfile) = "" Then
MsgBox "that doesn't exist!"
Exit Sub
Else
Set mywkbk = Workbooks.Open(myfile)
End If
End Sub

Can someone help??