How to change dynamically excel external file name change daily as the date


New Member
Sep 18, 2019
Hi all,
I am a beginner to this technology VBA Excel.

I faced a problem in the project

  • Read an external file and look for a Specific String (This external file name will change daily – as the Date – which will be part of the file name – will be dynamic)
  • Return a Value from the 8th Column for the Matching Text

When I try to hardcode the file name (with the Date) – there is a Value returned.

But when the file name is Dynamic (such as Today() – 1 etc.), then the function does not work.

See more details below:

  • I am looking for a DYNAMIC Value in the Quoted/RED highlighted area in the function below:

  • =IF(FileExists(),VLOOKUP("NET EFFECT ON TRIAL BALANCE",'\\internal-de1\DE-PHS-Collections\DB\Processing_Summary\[PROC_SUMMARY_DB_DLY_
    .xlsx]DB Processing_Summary_Report'!$A$4:$H$40,8,FALSE),2)

  • I am looking for the TEXT Match for “NET EFFECT ON TRIAL BALANCE” within the file – and then returning a Total Value (the Net Balance) as a result.
  • I am trying to have the FILENAME (Date) – Highlighted in Quoted/RED above – change on a Daily basis!

----Thank you to all


Well-known Member
Oct 5, 2015
Office Version
You need this to replace the red bit:
So, the whole thing would be:
=IF(FileExists(),VLOOKUP("NET EFFECT ON TRIAL BALANCE",'\\internal-de1\DE-PHS-Collections\DB\Processing_Summary\[PROC_SUMMARY_DB_DLY_"&TEXT(TODAY(),"yyyymmdd")&".xlsx]DB Processing_Summary_Report'!$A$4:$H$40,8,FALSE),2)
Last edited:


New Member
Sep 18, 2019
Thank you Jmacleary for your response.

But what I am looking exactly is I have 2 excel file(Book1.xls and 2. Proc_summary_DB_Dly_20190918.xlsx under that DB Processing_Summary_Report there) Now i trying to display the value presented in Proc_summary_DB_Dly_20190918.xlsx file have a row contain "NET EFFECT ON TRIAL BALANCE" and in that row in 8 column contain value, the value that i want to return in Book1.xls file. The function i wrote for this is
=IF(FileExists(),VLOOKUP("NET EFFECT ON TRIAL BALANCE", CONCATENATE("'", "\\internal-de1\DE-PHS-Collections\DB\Processing_Summary\[PROC_SUMMARY_DB_DLY_"&TEXT(TODAY()-1,"YYYYMMDD")&".xlsx]DB Processing_Summary_Report", "'","!$A$4:$H$40"),8,FALSE),2)

I have a problem with single quotations when i am using the evaluate formula under formula tab

Can you help me if you understand the problem.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...