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.

 

[nemmi69]

Const FNAME As String = "Index Levels "
Const FDIR As String = "C:\Temp\"
Sub OpenPrevFile()
 Workbooks.Open Filename:=FDIR & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"


End Sub

Just change Const FDIR to "Z:\Secondary Market\Pricing\Daily Index Levels"

 

[Small Paul]

Hi nemmi69

Many thanks for the fast response.
I have changed as you stated and have "Compile Error: Expected End Sub" at the 2nd "Const"
Sorry, I really am a beginner!
Small Paul.

 

[nemmi69]

Can you post a copy of your code?

 

[Small Paul]

Hi nemmi69
The code I have is:

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+p
Const FNAME As String = "Index Levels "
Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels"
Sub OpenPrevFile()
Workbooks.Open Filename:=FDIR & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"




End Sub

 

[Keebellah]

You 'forgot the backslash between FDIR and FNAME

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+p
Const FNAME As String = "Index Levels "
Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels"
Sub OpenPrevFile()
Workbooks.Open Filename:=FDIR &  "\" & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"

and please use code tags

 

[Small Paul]

I am still getting the same Compile Error on the 2nd row:
[Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels"]

 

[Keebellah]

Try this, it's a cvs file NOT a workbook

        Workbooks.OpenText Filename:=FDIR &  "\" & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv", Origin:= _
            xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, TrailingMinusNumbers:=True

 

[Keebellah]

Just in case the entire code

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+p
Const FNAME As String = "Index Levels "
Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels"
Sub OpenPrevFile()
Workbooks.OpenText Filename:=FDIR &  "\" & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv", Origin:= _
            xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, TrailingMinusNumbers:=True
End Sub

 

[Keebellah]

I see what's wrong the Const must be OUTSIDE the macro