Reading File Name in VB Macro that contains today's date

D

dhstein

Is it possible to have a macro that opens a file that has a date in it.
Specifically I need to open yesterday's file that would be called
Sales_2008_11-10.csv. Obviously this name changes every day. Thanks for any
help.

David
 
S

Sheeloo

Following macro will give the date in System short date format

Sub getDate()
Dim dt As String
dt = Date
MsgBox dt
End Sub

You can use it to build your file name and open that file.
 
P

PCLIVE

Maybe something like this:

Workbooks.Open Filename:= _
"Path\Sales_" & WorksheetFunction.Text((Now() - 1), "yyyy-mm-dd") &
".csv"

Replace "Path" with your file path.

HTH,
Paul
 
D

Dave Peterson

VBA has its own Format() function that you can use:

dim myFileName as string
myfilename = "sales_" & format(date-1,"yyyy-mm-dd") & ".csv"

If you need to watch out for Mondays (or weekends), maybe you can modify
something like this:

Dim myFileName As String
Dim myAdj As Long

Select Case Weekday(Date)
Case Is = vbSunday: myAdj = -2
Case Is = vbMonday: myAdj = -3
Case Else
myAdj = -1
End Select
myFileName = "sales_" & Format(Date + myAdj, "yyyy-mm-dd") & ".csv "

MsgBox myFileName
 
D

dhstein

Thanks for the great answers. Reading the file works fine. But now in the
macro there is a point where I switch windows and then switch back again.
The switching back isn't working. Here is the Hard coded line commented out
and the calculated name that isn't working. What am I doing wrong?

'Windows("sales_daily_2008-11-10.csv").Activate
Windows(DailySalesName).Activate
 
D

Dave Peterson

Do you really have _Daily as part of the name?

It wasn't in your original post?

How are you opening the .csv file -- workbooks.open?

If you are, then you could use:

dim DailyWkbk as workbook
dim myFileName as string
myFileName = "sales_" & Format(Date ......

set dailywkbk = workbooks.open(filename:=myfilename)
....

dailywkbk.activate
 
D

dhstein

This is what I have so far. The onlt thing that isn't working is closing the
file after I've copied the data that I need.


Sub Get_Sales()
'
' Get_Sales Macro
'

'

Dim myFileName As String
Dim myAdj As Long
DailySalesName = "sales_" & Format(Date - 1, "yyyy-mm-dd") & ".csv"


Adjustment = -1

DailySalesName = "C:\MyPath\sales_daily_" & Format(Date + Adjustment,
"yyyy-mm-dd") & ".csv "


Workbooks.Open Filename:= _
DailySalesName


Columns("A:B").Select
Selection.Copy

Windows("Inventory Project - In Progress.xlsm").Activate
Sheets("Today's Sales").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C1").Select


Sheets("Inventory").Select
Range("H1").Select
'Windows("sales_daily_2008-11-10.csv").Activate
'Windows(DailySalesName).Activate
Application.DisplayAlerts = False
Workbooks.Close Filename:= _
DailySalesName
'ActiveWorkbook.Close
Application.DisplayAlerts = True



End Sub
 
D

Dave Peterson

Untested, but it did compile ok:

Option Explicit
Sub Get_Sales()
Dim DailySalesName As String
Dim myAdj As Long
Dim DailyWkbk As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

myAdj = -1

DailySalesName = "C:\MyPath\sales_daily_" _
& Format(Date + myAdj, "yyyy-mm-dd") _
& ".csv "

Set DailyWkbk = Workbooks.Open(Filename:=DailySalesName)

With DailyWkbk.Worksheets(1)
Set RngToCopy = .Range("A:B")
End With

With Workbooks("Inventory Project - In Progress.xlsm") _
.Worksheets("Today's Sales")
Set DestCell = .Range("A1")
End With

RngToCopy.Copy
DestCell.PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

DailyWkbk.Close savechanges:=False

End Sub


This is what I have so far. The onlt thing that isn't working is closing the
file after I've copied the data that I need.

Sub Get_Sales()
'
' Get_Sales Macro
'

'

Dim myFileName As String
Dim myAdj As Long
DailySalesName = "sales_" & Format(Date - 1, "yyyy-mm-dd") & ".csv"

Adjustment = -1

DailySalesName = "C:\MyPath\sales_daily_" & Format(Date + Adjustment,
"yyyy-mm-dd") & ".csv "


Workbooks.Open Filename:= _
DailySalesName


Columns("A:B").Select
Selection.Copy

Windows("Inventory Project - In Progress.xlsm").Activate
Sheets("Today's Sales").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C1").Select


Sheets("Inventory").Select
Range("H1").Select
'Windows("sales_daily_2008-11-10.csv").Activate
'Windows(DailySalesName).Activate
Application.DisplayAlerts = False
Workbooks.Close Filename:= _
DailySalesName
'ActiveWorkbook.Close
Application.DisplayAlerts = True



End Sub
Click to expand...
 
D

dhstein

Dave,

Thanks so much. It complained about the "Option explicit" - maybe
because of where I have the macros - in "General" - but after I commented
that out it worked great. Thanks for your help.

David
 
D

Dave Peterson

Option Explicit
goes at the top of the module.

It's a directive to the compiler that tells it that you (the developer) want to
be forced to declare every variable you use.

It's a good idea so that the compile can help you catch typos:

Dim DailySalesName As String
dim DailyWkbk as Workbook
....
DailySalesName = "C:\MyPath\sales_daily_" _
& Format(Date + myAdj, "yyyy-mm-dd") _
& ".csv "

then

Set DailyWkbk = Workbooks.Open(Filename:=DailySaleName)

This wouldn't even compile because of the mistyped variable (missing an S in the
filename).
Dave,

Thanks so much. It complained about the "Option explicit" - maybe
because of where I have the macros - in "General" - but after I commented
that out it worked great. Thanks for your help.

David
Click to expand...
 
D

dhstein

Thanks again

Dave Peterson said:
Option Explicit
goes at the top of the module.

It's a directive to the compiler that tells it that you (the developer) want to
be forced to declare every variable you use.

It's a good idea so that the compile can help you catch typos:

Dim DailySalesName As String
dim DailyWkbk as Workbook
....
DailySalesName = "C:\MyPath\sales_daily_" _
& Format(Date + myAdj, "yyyy-mm-dd") _
& ".csv "

then

Set DailyWkbk = Workbooks.Open(Filename:=DailySaleName)

This wouldn't even compile because of the mistyped variable (missing an S in the
filename).
Click to expand...
 
Top