This might be a toughy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mac53 - 15 Nov 2007 21:49 GMT

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

Reply to this Message

Don Guillett - 15 Nov 2007 22:41 GMT

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.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 7 lines]
>
> Mac53

Reply to this Message

Mac53 - 16 Nov 2007 05:15 GMT

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

> 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
[quoted text clipped - 12 lines]
>>
>> Mac53

Reply to this Message

WLMPilot - 16 Nov 2007 11:44 GMT

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

> 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
[quoted text clipped - 22 lines]
> >>
> >> Mac53

Reply to this Message

Don Guillett - 16 Nov 2007 17:15 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 7 lines]
>
> Mac53

Reply to this Message

Rate this thread: