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

nareshk00

New Member
Joined
Sep 18, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,031
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You need this to replace the red bit:
Code:
&TEXT(TODAY(),"yyyymmdd")&
So, the whole thing would be:
Code:
=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:

nareshk00

New Member
Joined
Sep 18, 2019
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top