Check a workbook exists before opening
 

Hi all,

I have a workbook that opens another workbook that can be stored in any
location (however the filename will always be the same). I have managed to
write the code to allow the user to select the location of the second
workbook. However, in the interests of completeness I have attempted to
write a function to check the second workbook exists in the selected folder
(this was based on a function that checks a worksheet exists)

Function wbExists(ByRef wbname As String) As Boolean
Dim wb As Workbook
wbExists = False
For Each wb In sfoldername
If wb.Name = wbname Then wbExists = True
Next
End Function


and within my code for workbook 1

If wbExists(sfoldername & "\test2.xls") = True Then
Workbooks.Open sfoldername & "\test2.xls"
Else
MsgBox "Test2 data sheet not found in selected folder"
Exit Sub
End If

the variable sfoldername is a general declaration and contains the file path
eg. C:\folder1

When I run the code I get a compile error which states For Each may only
iterate over a collection object or an array.

Can anyone help me out of this little hole please.

Regards and Thanks

Mick

Check a workbook exists before opening
 

The following function checks for the existance of a file in the current
path. Simply copy both the Sub and Function into a module and set the
parameter (parm1) to be passed to the function:-

Sub If_File_Exists()

parm1 = "Book1.xls"

If Not FileExists(parm1) Then ' Check if file/folder exists .....
MsgBox "File " & parm1 & " does not exist", vbOKCancel
Exit Sub
Else
MsgBox "File exists"
End If

End Sub


Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x < "" Then
FileExists = True
Else
FileExists = False
End If
End Function


Regards,

OssieMax



"Symbiosis" wrote:

Hi all,

I have a workbook that opens another workbook that can be stored in any
location (however the filename will always be the same). I have managed to
write the code to allow the user to select the location of the second
workbook. However, in the interests of completeness I have attempted to
write a function to check the second workbook exists in the selected folder
(this was based on a function that checks a worksheet exists)

Function wbExists(ByRef wbname As String) As Boolean
Dim wb As Workbook
wbExists = False
For Each wb In sfoldername
If wb.Name = wbname Then wbExists = True
Next
End Function


and within my code for workbook 1

If wbExists(sfoldername & "\test2.xls") = True Then
Workbooks.Open sfoldername & "\test2.xls"
Else
MsgBox "Test2 data sheet not found in selected folder"
Exit Sub
End If

the variable sfoldername is a general declaration and contains the file path
eg. C:\folder1

When I run the code I get a compile error which states For Each may only
iterate over a collection object or an array.

Can anyone help me out of this little hole please.

Regards and Thanks

Mick

Check a workbook exists before opening
 

Hi Mick

How about this:

Function ClasseurExiste(ByRef NomComplet As String) As Boolean
ClasseurExiste =
CreateObject("Scripting.FileSystemObject").GetFile (NomComplet) = Err = 0
End Function

HTH
Cordially
Pascal

"Symbiosis" a écrit dans le message de news:
...
Hi all,

I have a workbook that opens another workbook that can be stored in any
location (however the filename will always be the same). I have managed
to write the code to allow the user to select the location of the second
workbook. However, in the interests of completeness I have attempted to
write a function to check the second workbook exists in the selected
folder (this was based on a function that checks a worksheet exists)

Function wbExists(ByRef wbname As String) As Boolean
Dim wb As Workbook
wbExists = False
For Each wb In sfoldername
If wb.Name = wbname Then wbExists = True
Next
End Function


and within my code for workbook 1

If wbExists(sfoldername & "\test2.xls") = True Then
Workbooks.Open sfoldername & "\test2.xls"
Else
MsgBox "Test2 data sheet not found in selected folder"
Exit Sub
End If

the variable sfoldername is a general declaration and contains the file
path eg. C:\folder1

When I run the code I get a compile error which states For Each may only
iterate over a collection object or an array.

Can anyone help me out of this little hole please.

Regards and Thanks

Mick

Check a workbook exists before opening
 

simpler

Function FileExists(fname) As Boolean
FileExists = Dir(fname) < ""
End Function

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"OssieMac" wrote in message
...
The following function checks for the existance of a file in the current
path. Simply copy both the Sub and Function into a module and set the
parameter (parm1) to be passed to the function:-

Sub If_File_Exists()

parm1 = "Book1.xls"

If Not FileExists(parm1) Then ' Check if file/folder exists .....
MsgBox "File " & parm1 & " does not exist", vbOKCancel
Exit Sub
Else
MsgBox "File exists"
End If

End Sub


Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x < "" Then
FileExists = True
Else
FileExists = False
End If
End Function


Regards,

OssieMax



"Symbiosis" wrote:

Hi all,

I have a workbook that opens another workbook that can be stored in any
location (however the filename will always be the same). I have managed
to
write the code to allow the user to select the location of the second
workbook. However, in the interests of completeness I have attempted to
write a function to check the second workbook exists in the selected
folder
(this was based on a function that checks a worksheet exists)

Function wbExists(ByRef wbname As String) As Boolean
Dim wb As Workbook
wbExists = False
For Each wb In sfoldername
If wb.Name = wbname Then wbExists = True
Next
End Function


and within my code for workbook 1

If wbExists(sfoldername & "\test2.xls") = True Then
Workbooks.Open sfoldername & "\test2.xls"
Else
MsgBox "Test2 data sheet not found in selected folder"
Exit Sub
End If

the variable sfoldername is a general declaration and contains the file
path
eg. C:\folder1

When I run the code I get a compile error which states For Each may only
iterate over a collection object or an array.

Can anyone help me out of this little hole please.

Regards and Thanks

Mick

Check a workbook exists before opening
 

Thank you very much... Works a treat.


"OssieMac" wrote in message
...
The following function checks for the existance of a file in the current
path. Simply copy both the Sub and Function into a module and set the
parameter (parm1) to be passed to the function:-

Sub If_File_Exists()

parm1 = "Book1.xls"

If Not FileExists(parm1) Then ' Check if file/folder exists .....
MsgBox "File " & parm1 & " does not exist", vbOKCancel
Exit Sub
Else
MsgBox "File exists"
End If

End Sub


Function FileExists(fname) As Boolean
' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x < "" Then
FileExists = True
Else
FileExists = False
End If
End Function


Regards,

OssieMax



"Symbiosis" wrote:

Hi all,

I have a workbook that opens another workbook that can be stored in any
location (however the filename will always be the same). I have managed
to
write the code to allow the user to select the location of the second
workbook. However, in the interests of completeness I have attempted to
write a function to check the second workbook exists in the selected
folder
(this was based on a function that checks a worksheet exists)

Function wbExists(ByRef wbname As String) As Boolean
Dim wb As Workbook
wbExists = False
For Each wb In sfoldername
If wb.Name = wbname Then wbExists = True
Next
End Function


and within my code for workbook 1

If wbExists(sfoldername & "\test2.xls") = True Then
Workbooks.Open sfoldername & "\test2.xls"
Else
MsgBox "Test2 data sheet not found in selected folder"
Exit Sub
End If

the variable sfoldername is a general declaration and contains the file
path
eg. C:\folder1

When I run the code I get a compile error which states For Each may only
iterate over a collection object or an array.

Can anyone help me out of this little hole please.

Regards and Thanks

Mick