vba to Open Workbook with Most Recent Date in File Name

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Code:
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"

 
Upvote 0
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.
 
Upvote 0
Can you post a copy of your code?
 
Upvote 0
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
 
Upvote 0
You 'forgot the backslash between FDIR and FNAME
Rich (BB 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.Open Filename:=FDIR &  "\" & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv"

and please use code tags
 
Upvote 0
I am still getting the same Compile Error on the 2nd row:
[Const FDIR As String = "Z:\Secondary Market\Pricing\Daily Index Levels"]
 
Upvote 0
Try this, it's a cvs file NOT a workbook

Rich (BB code):
        Workbooks.OpenText Filename:=FDIR &  "\" & FNAME & Format(Now() - 1, "yyyy-mm-dd") & ".csv", Origin:= _
            xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, TrailingMinusNumbers:=True
 
Upvote 0
Just in case the entire code
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
 
Upvote 0

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top