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

[nareshk00]

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 8^th 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_

  20190912

  .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

 

[jmacleary]

You need this to replace the red bit:

&TEXT(TODAY(),"yyyymmdd")&

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)

 

[nareshk00]

Thank you for you reply

 

[nareshk00]

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.

Thanks