CODEWITHSUNDEEP
excelvbaexcel-formula
Connor Smith
Connor Smith

Reputation: 3

Best way to reference a workbook in VBA when the workbook name changes everyday?

What is the best way to reference a workbook, when the name will change everyday, from another workbook? I have a workbook that references multiple sheets and I aim to pull in new data every day when I download a report, but the name of the report changes (e.g. InvoiceSoldReport2019-4-15 [Compatibility Mode] vs. InvoiceSoldReport2019-4-16 [Compatibility Mode] ).

Upvotes: 0

Views: 554

Answers (2)

LOKE2707
LOKE2707

Reputation: 312

you can use the file picker in your working.

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

Upvotes: 0

K&#252;rsat Aydinli
K&#252;rsat Aydinli

Reputation: 121

If you know, that only the date changes in the workbook name, then you could dynamically include the date in the name as a string. Like:

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

Upvotes: 1

Related Questions