Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check to see if a sheet with a particular name exists? | Excel Worksheet Functions | |||
Help - How to check if value exists in closed workbook? | Excel Programming | |||
Check if a sheet exists in a file, without opening that file | Excel Programming | |||
Check if sheet exists in a closed workbook | Excel Programming | |||
check if worksheet exists | Excel Worksheet Functions |