Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Is there an easy way in Excel to check if a file exists? I can concatenate
strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Here is a 1 instructtion VBA function that you can use.
Install as follows from speadsheet menu 1) from spreadsheet: Tools - Macro - Visual Basic Editor 2) from VBA: Insert - Module 3) Copy code below and paste into VBA window 4) Add code to cell: =ifexist("C:\abc.xls") You can use any string or cell reference. The code will work with wildcards as well. Function will return the filename if it exists or "" if file is not found. =if(ifexist("C:\abc.xls")= "","File Not found","File Found") Function ifexist(Target As String) As String ifexists = Dir(Target) End Function "Jon" wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
You could use a macro:
Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Dave: What happens if some cells have filenames that exists and others
don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Each cell in A1:Axx will have a corresponding message in column B saying if the
file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
I didn't look carefully. Thougt you wrote a function. For somebody who
doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
It seemed to do what the OP wanted:
I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I
simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
I think you're stretching the "excel formulas" portion of the original post to
include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
CAn you tell me what is the difference between a spreadsheet formula and a
VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
I think it comes down to what the OP meant by an excel formula. For someone who
wrote that he didn't know how to do it in VBA, I would think that he meant using functions built into excel--no VBA allowed. Once the line of VBA is crossed, I don't see too much distinction between a UDF and a subroutine in a case like this. Joel wrote: CAn you tell me what is the difference between a spreadsheet formula and a VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
The difference is one is automatic and one is manual. For somebody who
doesn't know VBA it is better to make the function automatic. If Jon comes back a year from know and makes changes will he remember that he has to run a macro to get updates? Lets supply people who request help "Fool-Proof solutions where possible"! "Dave Peterson" wrote: I think it comes down to what the OP meant by an excel formula. For someone who wrote that he didn't know how to do it in VBA, I would think that he meant using functions built into excel--no VBA allowed. Once the line of VBA is crossed, I don't see too much distinction between a UDF and a subroutine in a case like this. Joel wrote: CAn you tell me what is the difference between a spreadsheet formula and a VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Will he remeber to push the button? the formula can be copy like any other
formula. "Peo Sjoblom" wrote: It's hardly a fool proof solution is it? If Jon comes back a year from now will he remember what to put in the formula? Is there a help file? Wouldn't it be easier to create a button and just push that button instead of typing in a formula? For some things a macro is much better and this is one of them. Dave's macro will not need any path input either Do you think it is easier to put in a path like ifexist("C:\Documents and Settings\MySelf\My Documents\Excel Documents\arrays2.xls") than pushing a button? -- Regards, Peo Sjoblom "Joel" wrote in message ... The difference is one is automatic and one is manual. For somebody who doesn't know VBA it is better to make the function automatic. If Jon comes back a year from know and makes changes will he remember that he has to run a macro to get updates? Lets supply people who request help "Fool-Proof solutions where possible"! "Dave Peterson" wrote: I think it comes down to what the OP meant by an excel formula. For someone who wrote that he didn't know how to do it in VBA, I would think that he meant using functions built into excel--no VBA allowed. Once the line of VBA is crossed, I don't see too much distinction between a UDF and a subroutine in a case like this. Joel wrote: CAn you tell me what is the difference between a spreadsheet formula and a VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Another problem is when your function recalcs.
If you delete an existing file named in column A, when does your function get reevaluated. Or if you add a file to the folder that wasn't there before. With things like this, I wouldn't want to make the function volatile--especially with a large list. I would much rather just run the macro when I knew that I needed a refreshed list. On top of that, I'm not sure I'd use your function without some changes: Function ifexist(Target As String) As String ifexists = Dir(Target) End Function The typo is the first thing I'd fix. Second, if you pass a non-existing UNC path, what's returned? I don't think "automatic" is always better. Joel wrote: The difference is one is automatic and one is manual. For somebody who doesn't know VBA it is better to make the function automatic. If Jon comes back a year from know and makes changes will he remember that he has to run a macro to get updates? Lets supply people who request help "Fool-Proof solutions where possible"! "Dave Peterson" wrote: I think it comes down to what the OP meant by an excel formula. For someone who wrote that he didn't know how to do it in VBA, I would think that he meant using functions built into excel--no VBA allowed. Once the line of VBA is crossed, I don't see too much distinction between a UDF and a subroutine in a case like this. Joel wrote: CAn you tell me what is the difference between a spreadsheet formula and a VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if file exists
Thanks Joel and Dave,
I'll admit that Dave's original post was pretty intimidating to a non-VBA person like myself, but I do appreciate the discussion in all your posts and I understand the pro's Dave's solution. I think they both would work for me. I think the best option is for me to learn at least the basics of VBA. I'll read through Dave's suggested VBA link and some of the suggested reading materials. "Dave Peterson" wrote: Another problem is when your function recalcs. If you delete an existing file named in column A, when does your function get reevaluated. Or if you add a file to the folder that wasn't there before. With things like this, I wouldn't want to make the function volatile--especially with a large list. I would much rather just run the macro when I knew that I needed a refreshed list. On top of that, I'm not sure I'd use your function without some changes: Function ifexist(Target As String) As String ifexists = Dir(Target) End Function The typo is the first thing I'd fix. Second, if you pass a non-existing UNC path, what's returned? I don't think "automatic" is always better. Joel wrote: The difference is one is automatic and one is manual. For somebody who doesn't know VBA it is better to make the function automatic. If Jon comes back a year from know and makes changes will he remember that he has to run a macro to get updates? Lets supply people who request help "Fool-Proof solutions where possible"! "Dave Peterson" wrote: I think it comes down to what the OP meant by an excel formula. For someone who wrote that he didn't know how to do it in VBA, I would think that he meant using functions built into excel--no VBA allowed. Once the line of VBA is crossed, I don't see too much distinction between a UDF and a subroutine in a case like this. Joel wrote: CAn you tell me what is the difference between a spreadsheet formula and a VBA function? "Dave Peterson" wrote: I think you're stretching the "excel formulas" portion of the original post to include a UDF. Joel wrote: Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I simply found a way to do it witth a formula (excel function). "Dave Peterson" wrote: It seemed to do what the OP wanted: I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. Joel wrote: I didn't look carefully. Thougt you wrote a function. For somebody who doesn't know VBA, I thought giving a simple function was the best approach. Your code could be a problem because it writes the results in a 2nd cell which could overwrite other data. "Dave Peterson" wrote: Each cell in A1:Axx will have a corresponding message in column B saying if the file exists. Why doesn't looping through the cells make sense? Joel wrote: Dave: What happens if some cells have filenames that exists and others don't? Looping through cells in this case doesn't make sense. "Dave Peterson" wrote: You could use a macro: Option Explicit Sub testme01() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Dim TestStr As String Set wks = Worksheets("sheet1") With wks Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With On Error Resume Next For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else TestStr = "" TestStr = Dir(myCell.Value) If TestStr = "" Then myCell.Offset(0, 1).Value = "Doesn't exist" Else myCell.Offset(0, 1).Value = "It currently exists" End If End If Next myCell On Error GoTo 0 End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Jon wrote: Is there an easy way in Excel to check if a file exists? I can concatenate strings and build the full path and name of the file I want to check. I will have a few hundred file names in column A, and in column B I just want to have a formula that equals "Yes" if the file exists on my LAN or "No" if it doesn't. I can't seem to find a way to do it with Excel formulas. I'm sure this is trivial in VBA, but I don't know it. Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if text exists within a cell range and return logical vaule - possible? | Excel Discussion (Misc queries) | |||
How to check if a file exists in an ftp folder | Excel Discussion (Misc queries) | |||
check if the sheet/tag exists | Excel Worksheet Functions | |||
check if worksheet exists | Excel Worksheet Functions | |||
Check if a number exists in a range? | Excel Discussion (Misc queries) |