This might be a toughy

  • Thread starter Thread starter Mac53
  • Start date Start date
M

Mac53

My customer invoices are being made out by various people using excel.
When saving the invoices I've asked them to add the current date to the
filename when saving it,
such as AAA607 PLUS CURRENT DATE.
Problem is they enter the dates in different formats.
e.g. aaa6071107, aaa607010107, aaa6071/1/07, aaa60701/01/07.
Is it possible to get excel to add the current date to the filename
automatically for consistancy and so they can be later retrieved and not
overwritten.

Mac53
 
Why not just put a macro in the before_save event of the ThisWorkbook module
so that it is done when they save. You can even create it so that if they
still continue to try to save another way, their desires are over ridden.
 
Thanks very much for the suggestion, I thought that might have been the way.
Unfortunately, I'm not savy with macros's and would have no idea how to do
that. Learning macros for this one of event is not practicle for me and it
sounds very involved. Do you know of a ready made macro that will do this
and also how to implement it?

Thanks for the reply anyway.

Mac53
 
I am not savy with macros either, but here is one that someone gave me for
the same purpose you are looking for. You may only need the section that
starts at "ActiveWorkbook". My program will be placed on several computers
so I had to verify that the directory exist. Maybe this will get you pointed
in the right direction. I created a commandbutton that said "SAVE & CLOSE".
If you do not wish to close the workbook, remove the "Application.Quit"
command.

'ROUTINE TO SAVE ORDER AND QUIT (CLOSE) EXCEL
Private Sub CommandButton1_Click()
Dim aDirs
Dim sDir As String
Dim i As Long
aDirs = Split("C:\TESTORDER\SUPPLY ORDERS", "\")
sDir = aDirs(LBound(aDirs))
On Error Resume Next
For i = LBound(aDirs) + 1 To UBound(aDirs)
sDir = sDir & "\" & aDirs(i)
MkDir sDir
Next i
On Error GoTo 0
ActiveWorkbook.SaveAs Filename:=sDir & "\" & Format(Date, "yy-mmdd") & "
ST-2 Order.xls"
Application.Quit
End Sub
 
Goto the small excel icon in the upper left of the screen next to File>right
click>insert this.
For today, you will get aaa20071116

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim mydate As String
Application.DisplayAlerts = False
mydate = Format(Date, "yyyymmdd")
SaveAs Filename:="aaa" & mydate & ".xls"
Application.DisplayAlerts = True
End Sub
 
Back
Top