vba to Open Workbook with Most Recent Date in File Name

[Small Paul]

Hi All

Firstly, many apologies, I know this has been asked and answered previously. Unfortunately I am not experienced (or intelligent) enough to make those solutions work for me! I am therefore asking about my specific situation.

I save a daily .csv file in Sharesync with the previous working day in the file name e.g. "Index Levels 2018-06-27.csv" was saved today (28th).

The file is in Sharesync - Z:\Secondary Market\Pricing\Daily Index Levels\[filename]".

I need a macro to open this file (on the "present" tab).

Please could somebody help.

Many thanks
Small Paul.

 

[Small Paul]

Hi Keebellah
I get the same error at the same place. How do you mean 'outside the macro'?

 

[Small Paul]

Hi Keebellah
I still get the error at the same place. What do you mean by 'outside the macro'

 

[Keebellah]

You added the Sub Macro4
Did'nt mean to say nemmi69 mixed it up.
YOU entirely misused his code

 

[gallen]

The code is muddled up. Going off what you posted it would be something like this:

Const FNAME As String = "Index Levels "
Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels[COLOR=#ff0000][B]\[/B][/COLOR]"

Sub OpenPrevFile()

    Workbooks.Open Filename:=FDIR & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"
    
End Sub

Constant declarations must be made at modular level. You can't have them within the sub.

If you wanted these within the sub it would be:

Sub OpenPrevFile()


Dim FNAME As String
Dim FDIR As String


    FNAME = "Index Levels"
    FDIR = "Z:\Secondary Market\Pricing\Daily Index Levels\"
    
    Workbooks.Open Filename:=FDIR & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"
    
End Sub

 

[Keebellah]

@gallen: Good one
Now the OP will surely want to know how to invoke it using a keyboard macro
Thta's what muddled it up in the first place

 

[gallen]

Easy enough. Press [Alt] + [F8], highlight the macro and click 'Options' enter the shortcut you need in there.

 

[Small Paul]

Hi gallen
Many thanks for YOUR invaluable help. That works a treat.
With that, I have now also been able to set a file to save with yesterday's date too.
Cheers
Small Paul.

* I thought the Dutch would be in a good mood with the Germans heading home *

 

[Keebellah]

@gallen was good, you messed up, but I can't hold that against you.
And the good mood, no difference, win or lose, but I assume you learned something today about macro's

 

[Small Paul]

I certainly have learnt something about macros.
But in fairness, you say I messed up - your first comment stated I had missed the "". If you look at the original code (which I copy/pasted) it was not there!

 

[Keebellah]

True, but that wasn't the messed up, that is what I saw directly, then I saw that you ;'integrated' gallen's code in you macro4 and THAT was the mess up with the Const in the wrong place.
Who cares, VBA is unforgiving, syntax or dimesion error and then it's back to the drawing-board.
We're never too old to learn.