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
|