CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

Excel VBA - Append .xls to filename to open file

I have code to open a file with a variable date, as shown below. This code will not work without entering m.d.y.xls into the input box. I want to only have to enter m.d.y into the input box. Please take a look and let me know what I am missing. Thanks!

Dim wbkOpen As Workbook
Dim strFilePath As String
Dim strFileName As String
strFilePath = "D:\Users\stefan.bagnato\Desktop\Daily Performance Summary\Agent Group Daily Summary "
strFileName = InputBox("Enter last Friday's date in the format M.D.Y", "Friday's Date")
Set wbkOpen = Workbooks.Open(strFilePath & strFileName, False, True)

Upvotes: 0

Views: 294

Answers (1)

David Zemens
David Zemens

Reputation: 53623

This is basic string concatentation:

strFilePath & strFileName & ".xls"

You should probably check to ensure the file exists, otherwise there will be an error:

Dim fullFileName As String
strFilePath & strFileName & ".xls"
If Dir(fullFileName) = "" Then
    MsgBox "Invalid filename!"
    Exit Sub
End If
Set wbkOpen = Workbooks.Open(fullFileName, False, True)

Ideally, you can avoid user-input (which is prone to error) altogether:

Const strFilePath As String = "D:\Users\stefan.bagnato\Desktop\Daily Performance Summary\Agent Group Daily Summary "
Dim wbkOpen As Workbook
Dim LastFridayDate As String
Dim fullFileName As String
Dim fdlg as FileDialog
LastFridayDate = Format(Date - (Weekday(Date, vbFriday) - 1), "m.d.yy")
fullFileName = strFilePath & LastFridayDate & ".xls"

If Dir(fullFileName) = "" Then
    If MsgBox("The file named " & fullFileName & " doesn't exist. Would you like to manually locate the file?", vbYesNo) = vbNo Then
        Exit Sub
    Else
        Set fdlg = Application.FileDialog(msoFileDialogOpen)
        '## Opens the fileDialog in the normal folder where these files should exist
        fdlg.InitialFileName = strFilePath
        '## Display the fileDialog to the user
        fdlg.Show
        '## Validate the fileDialog hasn't been canceled
        If fdlg.SelectedItems.Count <> 0 Then
            '## Return the value of the item selected by the user
            fullFileName = fdlg.SelectedItems(1)
        Else:
            MsgBox "No file selected, exiting procedure..."
        End If
    End If
End If
Set wbkOpen = Workbooks.Open(fullFileName, False, True)

Of course allowing the user to manually select the file may ultimately require additional validation and/or error-handling (i.e., what if they select the wrong file? How can the program know which date is the correct date [I'd wager that it can't, without doing an ugly brute force loop which still makes a lot of assumptions which might not always hold] What if they select a PDF or a PPT file instead of an XLS, etc. but those points are entirely out of scope for this question.)

If you have additional follow-ups, please follow proper site etiquette and ask a new question :)

Upvotes: 3

Related Questions