Checking Directory for User Based File Names

[Ark68]

A user enters a date, m/dd/yy (eg. 6/07/09) into cell A1.
How can I go about checking the disk directory to see if an .xls file exists or not? The file name will be based on the re-formatted date entered in A1. In our example, I want the local directory checked to see if a file 7-Jun-09.xls exists.

Also, is it possible to check a directory and determine the last file name in that directory?

Jenn

 

[Kenneth Hobson]

Right click the sheet's tab, View Code, and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sFile As String
  If Target.Address <> "$A$1" Then Exit Sub
  sFile = ThisWorkbook.Path & Application.PathSeparator & Format(Target, "d-mmm-yy") & ".xls"
  If Dir(sFile) = "" Then
    MsgBox sFile & vbCrLf & "File does not exist.", vbCritical, "Error"
  End If
End Sub

For the last file, do you mean the one last modified, last created, last alphabetical sorted descending, last..?

 

[Ark68]

Thank you Kenneth.
I'm uncertain as to how to represent the specific path for this code to reference. Where/hopw do I specify which folder to check to see if the file exists.

And, to clarify your second question ... I would be looking for the last alphabetically (in descended order) listed file in the directory referenced above.

In essence, one folder will hold exclusively, files labelled d-mmm-yy.xls (which when additional are added will show up in descending order, earliest date to latest date). The code will check to see if the date (file) exists in the folder, and if not, will advise the user what the last date entered (in folder) is.

Jenn

 

[Kenneth Hobson]

If you want to hard code a path change:

sFile = ThisWorkbook.Path & Application.PathSeparator & Format(Target, "d-mmm-yy

to something like:

sFile = "x:\Excel\Inventory\" & Format(Target, "d-mmm-yy

 

[Ark68]

Hi Kenneth, thanks for helping me out. I've adapted your code suggestion to fit my needs, however, irregardless of the date entered, it always returns the error message that the file does not exist (even if it does).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sFile As String
  If Target.Address <> "$E$15" Then Exit Sub '* cell in which user enters date. Cell is formatted as d-mmm-yy *
  sFile = "E:\SportsOps 2009\SportsOps 2009\Data" & Format(Target, "d-mmm-yy") & ".xls" ' * directory where date xls files are stored *
  If Dir(sFile) = "" Then
    MsgBox sFile & vbCrLf & "File does not exist.", vbCritical, "Error"
  End If
End Sub

Any suggestions?

Jenn

 

[Kenneth Hobson]

sFile must contain the same characters as your file. So, "if" your path is right then your filename must be something like "Data7-Jun-09.xls". If your filename is really "Data 7-Jun-09.xls" then the code thinks the file does not exist.

So, you may need to add a space character after Data or maybe a backslash after Data if Data is a subfolder.

 

[Ark68]

Found the bug ... "Data" is actually part of the path, not the file name.
I had to include a backslash after data and all works fine.
Thanks!!!

Jenn