Need VB Code Help

[mattc]

Hello,

I need some help writing code that will allow me to determine if a file
exists in a directory, or not. I have actually figured out how I can do
this by specifying a single cell. However, I need to have this go
through multiple rows and return a “yes” or “no” for each row.

The amount of rows in this worksheet can vary, so it would have to be
able to determine how many rows are in the sheet also. So, starting in
cell B8 I have a path with the filename that I want to determine its
existence. In cell G8, I want “yes” or “no” to be displayed (depending
on whether the file exists or not). I want the program to then move to
the next row and use cell B9 to determine what value will be placed in
cell G9. This would continue until the last row of the sheet is
reached.

The code below will return a “yes” or “no” in cell G8 depending on
whether the file name in cell B8 exists in the directory path in cell
B5.


Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function

Function GetFileName(FullPath As String)
Dim StrFind As String
Do Until Left(StrFind, 1) = "\"
iCount = iCount + 1
StrFind = Right(FullPath, iCount)
If iCount = Len(FullPath) Then Exit Do
Loop
GetFileName = Right(StrFind, Len(StrFind) - 1)
End Function

Sub test1()
FileName = GetFileName(Range("B8").Value)
Path = Range("B5").Value
If FileThere(Path & FileName) Then
Range("G8").Select
ActiveCell.FormulaR1C1 = "Yes"
Else
Range("G8").Select
ActiveCell.FormulaR1C1 = "No"
End If
End Sub

I do not have much experience with VB code, so any assistance with this
would be much appreciated.

Thanks,
Matt

 

[Francis Brown]

assuming the path stays the same this may work.

Change the ref's to yourbook and yoursheet to the name of the sheet you are
using.

Sub test1()
endrow = workbooks("yourbook").sheets("yoursheet").range("B65536")_
..end(xlup).row
for each filetest in workbooks("yourbook").sheets("yoursheet")._
range("B8:B" & endrow)
FileName = GetFileName(filetext.Value)
Path = Range("B5").Value
If FileThere(Path & FileName) Then
filetest.offset(0, 5).value = "Yes"
Else
filetest.offset(0, 5).value = "No"
End If
next filetest
End Sub

 

[Bruno Campanini]

Hello,

I need some help writing code that will allow me to determine if a file
exists in a directory, or not. I have actually figured out how I can do
this by specifying a single cell. However, I need to have this go
through multiple rows and return a "yes" or "no" for each row.

The amount of rows in this worksheet can vary, so it would have to be
able to determine how many rows are in the sheet also. So, starting in
cell B8 I have a path with the filename that I want to determine its
existence. In cell G8, I want "yes" or "no" to be displayed (depending
on whether the file exists or not). I want the program to then move to
the next row and use cell B9 to determine what value will be placed in
cell G9. This would continue until the last row of the sheet is
reached.

The code below will return a "yes" or "no" in cell G8 depending on
whether the file name in cell B8 exists in the directory path in cell
B5.

[...]

Just to have a view into a different approach:
==============================
Sub FileSearchInDir()
Dim FS As Object, TargetRange As Range
Dim SourceRange As Range, i, j As Long
Dim File_Name As String, File_Dir As String

' Definitions
' --------------------------------
Set TargetRange = [Sheet10!BZ36]
Set SourceRange = [Sheet10!BY36]
' --------------------------------
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize _
(SourceRange.End(xlDown).Row - SourceRange.Row + 1)
End If

Set FS = Application.FileSearch

For Each i In SourceRange
File_Name = Mid(i, InStrRev(i, "\") + 1)
File_Dir = Mid(i, 1, Len(i) - Len(File_Name) - 1)
With FS
.NewSearch
.FileName = File_Name
.LookIn = File_Dir
.SearchSubFolders = False
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
j = j + 1
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) Then
TargetRange(j) = "Yes"
Else
TargetRange(j) = "No"
End If
End With
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
============================

Ciao
Bruno

 

[mattc]

Thank you for your help Francis and Bruno.

I have one additional question regarding your code, Bruno. How could I
modify the code to search for the filename in the directory, regardless
of the file extension? An example would be if I had a file named
"test.psm" and in the search directory I had a file named "test.gcd", I
would want the code to place a "yes" in the destination cell. Can
wildcard searches be done?

Thanks for your help.
Matt

 

[Bruno Campanini]

Thank you for your help Francis and Bruno.

I have one additional question regarding your code, Bruno. How could I
modify the code to search for the filename in the directory, regardless
of the file extension? An example would be if I had a file named
"test.psm" and in the search directory I had a file named "test.gcd", I
would want the code to place a "yes" in the destination cell. Can
wildcard searches be done?

Yes mattc!
You can use wildcard in .FileName =
Something like:
..FileName = "test.*"

But remember, if more than one file is found,
you'll find them in .FoundFiles (see FileSearch Help).

Let me know if you need any modification to the routine.

Ciao
Bruno

 

[mattc]

Bruno,

I am having trouble figuring out how to apply this wildcard logic t
the routine that you sent previous. Any help in modifying the routin
with this logic would be appreciated.

Thanks,
Mat

 

[Bruno Campanini]

Bruno,

I am having trouble figuring out how to apply this wildcard logic to
the routine that you sent previous. Any help in modifying the routine
with this logic would be appreciated.

Thanks,
Matt

Ok Matt, but first of all you must consider that you can
have more than one file matching a criterion like "*test*.*"

Suppose you have in your list something like "C:\Documents\*test*.*"
i.e. one input but, as the result, you can have e.g. 6 outputs.
Now, what must the routine do?
- create a list in a range for the matches (file names) met?
- put the list in an Array?
- or what?

Let me know what you prefer and then I'll modify the code.

Ciao
Bruno