Search Excel Workbooks for a Date-Specific Cell Value
Question details
Retrieve a value from a specific cell in one of many Excel workbooks based on a selected date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A master workbook uses daily plant data stored in separate workbooks in a folder.
- Observed behavior
- Standard file search does not reliably identify a value at a particular cell location across the workbooks.
Check how the daily files are named, which worksheet contains the data, and which cell holds the value. The example below assumes files such as 2025-01-31.xlsx, a worksheet named Data, and the value in cell D4.
Retrieve a daily workbook value with an Excel VBA macro
Use this method when each daily workbook has a predictable filename based on its date.
The macro constructs the filename from the date in the master workbook, opens that file as read-only, and copies the value from Data!D4 into the master workbook. Change the folder, filename format, worksheet name, and cell references in the code to match your files.
In desktop Excel, create a worksheet named Master and enter the required date in cell A2. Save the master workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As.
Open Developer > Visual Basic, select Insert > Module, and paste the following code: Sub GetDailyValue() Dim master As Worksheet, daily As Workbook Dim filePath As String, oldSecurity As Long Dim errorText As String Set master = ThisWorkbook.Worksheets("Master") If Not IsDate(master.Range("A2").Value) Then MsgBox "Enter a valid date in Master!A2." Exit Sub End If filePath = "C:\DailyData\" & Format$(CDate(master.Range("A2").Value), "yyyy-mm-dd") & ".xlsx" If Dir$(filePath) = "" Then MsgBox "Daily workbook not found: " & filePath Exit Sub End If oldSecurity = Application.AutomationSecurity On Error GoTo Failed Application.AutomationSecurity = 3 Set daily = Workbooks.Open(Filename:=filePath, UpdateLinks:=0, ReadOnly:=True) master.Range("B2").Value = daily.Worksheets("Data").Range("D4").Value daily.Close SaveChanges:=False Set daily = Nothing Application.AutomationSecurity = oldSecurity MsgBox "Value retrieved into Master!B2." Exit Sub Failed: errorText = Err.Description On Error Resume Next If Not daily Is Nothing Then daily.Close SaveChanges:=False Application.AutomationSecurity = oldSecurity MsgBox "Could not retrieve the value: " & errorText End Sub
In the VBA editor, replace C:\DailyData\ with the folder containing your files. If needed, change yyyy-mm-dd, .xlsx, Data, D4, or B2 to match your filename pattern, source worksheet, source cell, and destination cell.
Return to Excel, select Developer > Macros, choose GetDailyValue, and click Run. Check Master!B2 for the retrieved value; change A2 and run the macro again for another date.

Manage Daily Spreadsheet Data with WPS Office
WPS Office is a free, lightweight alternative for opening and editing daily spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files into your workflow. The Excel VBA macro above is an Excel-specific solution and is not guaranteed to run in WPS.

Frequently Asked Questions
How does the macro choose the daily workbook?
It reads the date in Master!A2 and builds a filename such as 2025-01-31.xlsx. Change the Format$ pattern and file extension in the VBA code if your files use a different naming convention.
What if my source value is not in Data!D4?
In the VBA editor, change daily.Worksheets("Data").Range("D4") to the correct worksheet name and cell address. The source worksheet name must match the name in the daily workbook.
Why does Excel say the daily workbook was not found?
Check the folder path and confirm that the filename produced from the date in Master!A2 exactly matches an existing file, including its extension.
Can I retrieve a value for a different date?
Enter the new date in Master!A2, then run GetDailyValue again from Developer > Macros. The result replaces the previous value in B2.




