Excel VBA - 将.xls附加到文件名以打开文件 [英] Excel VBA - Append .xls to filename to open file

问题描述

我有代码打开一个变量日期的文件，如下所示。如果在输入框中输入m.d.y.xls，此代码将无法正常工作。我只想在输入框中输入m.d.y。请看看，让我知道我失踪了什么。谢谢！

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)

推荐答案

这是基本的字符串连接：

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)

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

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

这篇关于Excel VBA - 将.xls附加到文件名以打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！