当工作簿名称每天更改时，在VBA中引用工作簿的最佳方法

Question

当名称每天都会更改时，从另一个工作簿引用工作簿的最佳方式是什么？ 我有一个引用多个工作表的工作簿，我的目标是每天在下载报告时提取新数据，但报告的名称发生了变化(例如，发票销售报告2019-4-15 [兼容模式] 与发票销售报告2019-4-16 [兼容模式](。

Answer 1

如果您

知道工作簿名称中的日期仅更改，则可以在名称中动态包含日期作为字符串。喜欢：

Dim current_date as Date
Dim wb_name as string
Dim wb_open as Workbook
...
date = ...
wb_name = "InvoiceSoldReport"
Set wb_open = Workbooks.Open(wb_name & date) 'concatenate name and date
....

Answer 2

您可以在

工作中使用文件选择器。

Dim wb2 As Workbook
Dim fdl As FileDialog
Dim FileChosen As Integer
Set fdl = Application.FileDialog(msoFileDialogFilePicker)
fdl.Title = "Please Select the XXX file"
'Set the InitialFile Path
fdl.InitialFileName = "D:"
'Set the Folder View
fdl.InitialView = msoFileDialogViewSmallIcons
'Set the filter
fdl.Filters.Clear
fdl.Filters.Add "XLSX", "*.XLSX"
'Optional if you know the file type is constant, else no need of filter
FileChosen = fdl.Show
If FileChosen <> -1 Then
'Not choosen anything / Clicked on CANCEL
MsgBox "No file choosen"
Else
'fdl.SelectedItems(1) display name and complete path of file chosen
 Set wb2 = Workbooks.Open(fdl.SelectedItems(1))
End If