Results 1 to 4 of 4

Thread: How to change dynamically excel external file name change daily as the date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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_
      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

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    628
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

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

    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 by jmacleary; Sep 19th, 2019 at 05:17 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you for you reply

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •