• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA CODE TO OPEN A CHANGING FILE

AhmedAbbas

New Member
I have a file extension like this

H:\Accounts\Mthly Report\FY 2017\07.2017\WESTPAC\JUL_2017 Westpac Bank Statement.xlsx
Lots of file directory remains the same but few things changes
For example Year 2017 Month 07 or Jul
I have used following formula to determine the variables
=TEXT(TODAY(),"mm") Placed in Cell A1
=TEXT(TODAY(),"yyyy") Placed in Cell B1
=TEXT(TODAY(),"mmm") Placed in Cell C1
Question is how can I create a string using these variables to reach the file named above. Your guidance is much appreciated
 
Hi, AhmedAbbas!

Try this:
="H:\Accounts\Mthly Report\FY "&B1&"\"&A1&"."&B1&"\WESTPAC\"&C1&"_"&B1&" Westpac Bank Statement.xlsx"

And here is the version without the helper columns in A1-B1-C1:
="H:\Accounts\Mthly Report\FY "&TEXTO(HOY();"yyyy")&"\"&TEXTO(HOY();"mm")&"."&TEXTO(HOY();"yyyy")&"\WESTPAC\"&TEXTO(HOY();"mmm")&"_"&TEXTO(HOY();"yyyy")&" Westpac Bank Statement.xlsx" -----> in English: ="H:\Accounts\Mthly Report\FY "&TEXT(TODAY(),"yyyy")&"\"&TEXT(TODAY(),"mm")&"."&TEXT(TODAY(),"yyyy")&"\WESTPAC\"&TEXT(TODAY(),"mmm")&"_"&TEXT(TODAY(),"yyyy")&" Westpac Bank Statement.xlsx"

Regards!
 
Hi, to all!

This option could work:
=TEXT(TODAY(),"""H:\Accounts\Mthly Report\FY ""yyy\\mm.yyy""\WESTPAC\""mmm\_yyy"" Westpac Bank Statement.xlsx""")

Blessings!
 
Thankyou very much both of you to give me a solution. I appreciate time and patience you put in for providing me solutions. Will update about results
 
Thanks for your valuable inputs, I found out I can do it multiple ways with guidance you both provided. Again my sincere thanks
 
Hi, AhmedAbbas!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Back
Top