Excel的VBA - 附加的.xls为filename打开文件

Question

我有码开具有可变日期的文件，如下图所示。如果不在输入框中输入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) 

Answer 1

这是基本的字符串连接：

strFilePath & strFileName & ".xls" 

你或许应该检查以确保该文件存在，否则会出现错误：

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) 

理想情况下，你能避免用户 - 输入（这是容易出错）共：

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) 

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

如果您有其他跟进，请按照适当的场地礼仪和提出新问题:)