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
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
778
Office Version
2007
Platform
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
 

Forum statistics

Threads
1,081,576
Messages
5,359,729
Members
400,545
Latest member
Damntheman30

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...
Top