file naming during auto-saveas

S

SJW_OST

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

Dave Peterson

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

Joel

from
irtime = Hour(Now) & Minute(Now) 'could this line be the problem?

to
irtime = Hour(t) & Format(Minute(t), "#00")
 
S

SJW_OST

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top