Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
file naming during auto-saveas
Hello,
I am using the following code to automatically do a saveas process to have my file save as filename_date_time.xls. Sub SaveAs2() ' Dim irdate Dim irtime Application.ScreenUpdating = False Application.DisplayAlerts = False irdate = Month(Now) & Day(Now) & Year(Now) 'Format(Date,"mmddyy") irtime = FormatDateTime(Round(Time * 24, 0.1) / 24, vbShortTime) 'Format(Time,"hhmm") irtime = Hour(Now) & Minute(Now) 'could this line be the problem? On Error Resume Next ActiveWorkbook.SaveAs "H:\" & "PassCounts_" & irdate & "_" & irtime & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub The problem I am having is in the time format. For 12:00 it should show _1200.xls but I am getting _120.xls instead. Can someone help me with properly formating the time for doing this file saveas process? I'm sure it is somthing simple but it just keeps illuding me. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
file naming during auto-saveas
Without looking at the details, I'd use something like:
ActiveWorkbook.SaveAs "H:\" & "PassCounts_" _ & format(now,"mmddyy_hhmmss") & ".xls" Actually, I'd use a format of yyyymmdd_hhmmss. I think it looks nicer and is easier to sort by in windows explorer. SJW_OST wrote: Hello, I am using the following code to automatically do a saveas process to have my file save as filename_date_time.xls. Sub SaveAs2() ' Dim irdate Dim irtime Application.ScreenUpdating = False Application.DisplayAlerts = False irdate = Month(Now) & Day(Now) & Year(Now) 'Format(Date,"mmddyy") irtime = FormatDateTime(Round(Time * 24, 0.1) / 24, vbShortTime) 'Format(Time,"hhmm") irtime = Hour(Now) & Minute(Now) 'could this line be the problem? On Error Resume Next ActiveWorkbook.SaveAs "H:\" & "PassCounts_" & irdate & "_" & irtime & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub The problem I am having is in the time format. For 12:00 it should show _1200.xls but I am getting _120.xls instead. Can someone help me with properly formating the time for doing this file saveas process? I'm sure it is somthing simple but it just keeps illuding me. Thanks in advance. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
file naming during auto-saveas
from
irtime = Hour(Now) & Minute(Now) 'could this line be the problem? to irtime = Hour(t) & Format(Minute(t), "#00") "SJW_OST" wrote: Hello, I am using the following code to automatically do a saveas process to have my file save as filename_date_time.xls. Sub SaveAs2() ' Dim irdate Dim irtime Application.ScreenUpdating = False Application.DisplayAlerts = False irdate = Month(Now) & Day(Now) & Year(Now) 'Format(Date,"mmddyy") irtime = FormatDateTime(Round(Time * 24, 0.1) / 24, vbShortTime) 'Format(Time,"hhmm") irtime = Hour(Now) & Minute(Now) 'could this line be the problem? On Error Resume Next ActiveWorkbook.SaveAs "H:\" & "PassCounts_" & irdate & "_" & irtime & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub The problem I am having is in the time format. For 12:00 it should show _1200.xls but I am getting _120.xls instead. Can someone help me with properly formating the time for doing this file saveas process? I'm sure it is somthing simple but it just keeps illuding me. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
file naming during auto-saveas
WOW!! That is soo much easier than what I had found before. It makes sense
from start to finish and takes up less area on my screen. Thank you! "Dave Peterson" wrote: Without looking at the details, I'd use something like: ActiveWorkbook.SaveAs "H:\" & "PassCounts_" _ & format(now,"mmddyy_hhmmss") & ".xls" Actually, I'd use a format of yyyymmdd_hhmmss. I think it looks nicer and is easier to sort by in windows explorer. SJW_OST wrote: Hello, I am using the following code to automatically do a saveas process to have my file save as filename_date_time.xls. Sub SaveAs2() ' Dim irdate Dim irtime Application.ScreenUpdating = False Application.DisplayAlerts = False irdate = Month(Now) & Day(Now) & Year(Now) 'Format(Date,"mmddyy") irtime = FormatDateTime(Round(Time * 24, 0.1) / 24, vbShortTime) 'Format(Time,"hhmm") irtime = Hour(Now) & Minute(Now) 'could this line be the problem? On Error Resume Next ActiveWorkbook.SaveAs "H:\" & "PassCounts_" & irdate & "_" & irtime & ".xls" Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub The problem I am having is in the time format. For 12:00 it should show _1200.xls but I am getting _120.xls instead. Can someone help me with properly formating the time for doing this file saveas process? I'm sure it is somthing simple but it just keeps illuding me. Thanks in advance. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Naming Worksheets | Excel Discussion (Misc queries) | |||
Auto naming Worksheets | Excel Discussion (Misc queries) | |||
Auto file naming | Excel Discussion (Misc queries) | |||
Auto naming worksheets | Excel Discussion (Misc queries) | |||
Check cells for 'Auto Save As' file naming | Excel Programming |