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

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
>
>
>

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" <(E-Mail Removed)> a écrit dans le message de news:
6MrGi.29311$(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:F53039A6-2D31-4D0E-AA1A-(E-Mail Removed)...
> 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
>>
>>
>>

Thank you very much... Works a treat.


"OssieMac" <(E-Mail Removed)> wrote in message
news:F53039A6-2D31-4D0E-AA1A-(E-Mail Removed)...
> 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
>>
>>
>>