Dynamic file path

  • Hi guys


    I have to questions regarding a dynamic file path in VBA.


    This code loops through the folder Queries and opens the youngest .xlsx file.


    My first question:


    I think I made a dynamic folder in the Queries folder. The folders in queries are "2013" and "2014". For now it loops only through "2014" and opens the youngest file in "2014". I created a "2015" and tested whether it works for 2015 but it doesn't work in that folder. Is that because 2015 is in the future? For 2014 it works fine!


    My second question:


    Sometimes files are in .xls instead of .xlsx. Maybe sometimes .xlsm. How should I add this code to open multiple extensions?


    Thank you in advance!

  • Re: Dynamic file path


    Cross posted here


    Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!


    Read this to understand why we ask you to do this


    Answered before I found the cross post.

  • Re: Dynamic file path


    Quote from royUK;698881

    Answered before I found the cross post.


    That's why I tried to adjust it in this part:


    Code
    ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest 
        On Error Resume Next
        Debug.Print "Trying to open: " & _ 
        sPath & "File_Name_" & Format(dtTestDate, "dMMMyy") & ".xlsx" 
        Workbooks.Open sPath & Format(dtTestDate, "YYYY") & "\" & Format(dtTestDate, "YYYYMMDD") & "_Fondsen per klasse" & ".xlsx" 
        dtTestDate = dtTestDate - 1 
        On Error Goto 0 
    Wend


    Workbooks.Open sPath & Format(dtTestDate, "YYYY") & "\" & Format(dtTestDate, "YYYYMMDD") & "_Fondsen per klasse" & ".xlsx"


    this part should open the dynamic folder with "format(dtTestdate, "YYYY"). Will it open the folder "2015" from the first of JAN 2015?

  • Re: Dynamic file path


    Done. DOn't think it really matters for the question though. Only thing I need to know is how to adjust it so it opens multiple file extensions and that it's not limited to .xlsx

  • Re: Dynamic file path


    I bet you 'copied' this 'code' from somewhere and you have no idea what it is doing.
    You'd better start grasping the fundamentals of VBA (like any other language) before writing poems in that language.

  • Re: Dynamic file path


    SNB, the part I posted is part of a bigger code which runs fine. The only finetuning part that has to be done is the adjustment to let it open multiple extensions.


    If you don't know the answer, that's fine, but please, don't give these senseless comments about not knowing what I'm doing.

  • Re: Dynamic file path


    For those who utilise the forum as a source of information and education would you like to share your solution? Sharing knowledge I like to think is a foundation principle of Ozgrid.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Dynamic file path


    Very happy to share:


    Code
    Workbooks.Open sPath & Format(dtTestDate, "YYYY") & "\" & Format(dtTestDate, "YYYYMMDD") & "_Fondsen per klasse" & ".xlsx"


    change this line to


    Code
    Workbooks.Open sPath & Format(dtTestDate, "YYYY") & "\" & Format(dtTestDate, "YYYYMMDD") & "_Fondsen per klasse" & "*.xl*"
  • Re: Dynamic file path


    I resolved at least half of the topic. Still trying to figure out how to make the path dynamic. I can open both extensions now though.

  • Re: Dynamic file path


    You never mentioned extensions and you said this was solved, so what error are you getting. I've twice said that you cannot declare a constant then change it within the code.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!