Macro to save file to a specific directory depending on the day of week

R

robertguy

Hi,


Can any one help me with the Macro code which when activated wil
determine the day of the week it is and save the current file to a
associated specific directory – depending on what day it is.

i.e. file in use is finance.xls

When macro activated (via a macro button) the file would automaticall
be saved in a directory corresponding to the day the macro is ran

c:\Monday\ finance.xls

c:\Tuesday\ finance.xls

c:\Wednesday\finance.xls

c:\Thusday\ finance.xls

c:\Friday finance.xls

It can be assumed that the current file has been saved already into it
usual directory and the macro will be run at end of day and the Exce
then closed.

Any help would be greatly appreciated

Many thanks


Rob


NB Running Excel 200
 
I

icestationzbra

try this (modify to suit):

Sub SaveAs()

Dim strPath As String

Const SavePath As String = "D:\TmpXls\" 'you should have this folde
already created

strPath = Format(Date, "dddd") 'you should have this folder alread
created

ActiveWorkbook.SaveAs Filename:=SavePath & strPath & "\finance.xls"

End Su
 
I

icestationzbra

slight change:

Sub SaveAsPath()

Dim SavePath As String

SavePath = "D:\TmpXls\" & Trim(Format(Date, "dddd")) 'you should hav
this folder already created

ActiveWorkbook.SaveAs Filename:=SavePath & "\finance.xls"

End Su
 
A

AlfD

Hi!

You could also vary the "finance.xls" part of the name. If you don't,
you are inviting Excel to overwrite the one you put in last week. If
that is OK, OK.

Otherwise you might extend the file name by adding, say, the date :

"Finance " & trim(format(date,"dd-mmm") & ".xls")

Alf
 
Top