我有代码可以打开一个带有可变日期的文件,如下所示。如果没有在输入框中输入 mdyxls,此代码将无法工作。我只想在输入框中输入 mdy 。请看一看,让我知道我缺少什么。谢谢!
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)
这是基本的字符串连接:
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 等,但这些点完全超出了范围这个问题。)
如果您有其他后续跟进,请遵循适当的网站礼仪并提出新问题:)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句