Data Import & Export

Search Excel Workbooks for a Date-Specific Cell Value

Anum MushtaqAnum Mushtaq Nov 20, 2026 869 views

Question details

Retrieve a value from a specific cell in one of many Excel workbooks based on a selected date.

Search Excel Workbooks for a Date-Specific Cell Value
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the master workbook

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.

2
Insert the macro

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

3
Update the file settings

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.

4
Run the macro

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.

Retrieve a daily workbook value with an Excel VBA macro
Macro and file access: Run macros only in workbooks you trust. The example disables macros in the daily workbook while opening it and restores the previous setting afterward; the folder must also be accessible to Excel.
Free Microsoft Office alternative

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.

Open and review daily workbook data in a familiar spreadsheet interfaceCompatible with Microsoft Excel formats such as XLSXFree and lightweight for everyday spreadsheet workMove existing Office documents into a familiar workspace
microsoft office alternative - wps office

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.