Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
Macro to jump to today's date | Excel Discussion (Misc queries) | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |