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

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

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

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.

"dhstein" wrote:

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

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

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

--

"dhstein" wrote in message
...
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

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

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




dhstein wrote:

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

--

Dave Peterson

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

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson

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

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson

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

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



"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson

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

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



dhstein wrote:

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

"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

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
"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

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).

dhstein wrote:

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
"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

Thanks again

"Dave Peterson" wrote:

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).

dhstein wrote:

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
"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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



dhstein wrote:

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

"Dave Peterson" wrote:

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




dhstein wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson