Variable Directory

[bjurney]

I am trying to get this code (or a code like it, or any code for that matter!) to find a certain file based on the user input. The end result will import data from that certain file but for test purposed, a message box is good enough to tell me that it works. The format of the file will always be along the lines of My File 2011-02-28. Depending on the date the user puts in, it will need to find that file. Every time I run the follwing code though I get an Object Required error. Im at a loss where I should go from here.

Sub import()
result = InputBox("What is the date of the file you are importing? Date Must be in MM/DD/YYYY format", _
    "Enter Date", Date)
    mydate = Format(result, "MM/DD/YYYY")
    mydir = ("C:\My File " & Year(mydate) & "-" & Month(mydate) & "-" & Day(mydate).xls)
If Dir(mydir) <> "" Then
MsgBox ("Exists")
End If
End Sub

 

[Norie]

Is this a typo?

Day(mydate).xls

 

[bjurney]

Im not sure how to format the .xls. I need it to to specify that it is a .xls file though.

 

[Norie]

Isn't it the date you want to format, rather than the filename itself?

Option Explicit

Sub MyImport()
Dim result
Dim strFileName
Dim strDir As String
 
    strDir = "C:\"
 
    result = InputBox("What is the date of the file you are importing? Date Must be in MM/DD/YYYY format", _
                      "Enter Date", Date)

    If IsDate(result) Then
 
        strFileName = "My File " & Format(DateValue(result), "yy-mm-dd") & ".xls"

        If Dir(strDir & strFileName) <> "" Then
            MsgBox "Exists"
        Else
            MsgBox "Doesn't exist"
        End If

    Else
        MsgBox "Please enter a valid date."
    End If
 
End Sub

 

[bjurney]

Works perfect, thanks for the help.