-
Solved: Check that an excel file exists
I have a folder with excel files named by date. Instead of opening each of these files to check the data manually I have some code to generate the filename based on the date and return the value of a cell. The code loops through a range of dates and checks each file in the folder, this works great but every now and then a file does not exist and then the code is interupted and a box pops up to locate the missing file.
After generating the filename, how can I check that an excel file actually exists in a directory?
-
-
Not getting it to work, it's stopped returning the values from the excel files.
This is what I have:
[VBA]
FileName = "Cost_" & Format(theDate, "YYYY_MM_DD") & "_7-3_Fin.xls"
FileName2 = "'C:\Documents and Settings\xxx\My Documents\costs\budgetLogs\" & yearlyDir & "\" & monthlyDir & yearlyDir & "\[" & FileName & "]"
If FileFolderExists(FileName2) Then
.Range("e2").Offset(NextRow, 0).Formula = "=" & FileName2 & "Spend Log'!c19"
Else
NextRow = NextRow + 3
NextRow1 = NextRow1 + 2
theDate = theDate + 1
End If
Loop
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function
[/VBA]
-
[VBA]
sub M_snb()
c00="C:\Documents and Settings\xxx\My Documents\costs\budgetLogs\" & yearlyDir & "\" & monthlyDir & yearlyDir & "\"
c01 = "Cost_" & Format(theDate, "YYYY_MM_DD") & "_7-3_Fin.xls"
if dir(c00 & c01)<>"" then
thisworkbook.sheets("Sheet1").Range("e2").Offset(NextRow, 0)= "=" & c00 & ""'[" & c01 & "]Spend Log'!c19"
Else
NextRow = NextRow + 3
NextRow1 = NextRow1 + 2
theDate = theDate + 1
End If
End sub
[/VBA]
PS. you can be advised more effectively if you post a sample workbook.
-
SNB/Gary,
Thanks for the assist.
SNB your code did the trick.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules