Excel VBA - 将.xls附加到文件名以打开文件

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!

我有代码打开一个带有可变日期的文件,如下所示。如果没有在输入框中输入m.d.y.xls,此代码将无法运行。我只想将m.d.y输入到输入框中。请看一下,让我知道我错过了什么。谢谢!

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)

1 个解决方案

#1

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.)

当然允许用户手动选择文件可能最终需要额外的验证和/或错误处理(即,如果他们选择了错误的文件怎么办?程序如何知道哪个日期是正确的日期[我打赌它是不能做一个丑陋的暴力循环仍然做出许多可能并不总是持有的假设]如果他们选择PDF或PPT文件而不是XLS等等,但这些点完全超出范围这个问题。)

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

如果您有其他后续行动,请遵循适当的网站礼仪并提出新问题:)

#1