ActiveWorkbook.Save
Dim rmmth As Date
rmmth = Date
rmmth1 = Format$(rmmth, "dd-mm-yy")
rmmth2 = Format$(rmmth, "mmm-yy")
rmmth3 = Format$(rmmth, "mmm")
day1 = Day(rmmth)
Dim fname1 As String
fname1 = "rm -" & rmmth1 & ".xls"
ChDir "D:\"
ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=xlExcel9795
Workbooks.Open Filename:="\\SS\ss_d\HOTEL HOST INTL. ROOM BILL
FORMAT\ROOM BILLS FORMAT IN EXCEL\all room bill 1 to 6.xls"
Windows(fname1 ).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("all room bill 1 to 6.xls").Activate
ActiveWindow.WindowState = xlMaximized
--
HTH
RP
(remove nothere from the email address if mailing direct)
"sujata_ghosh" wrote in message
...
Hi to all!
i have written a programme in VBA, which will do following tusk when
someone press the button
save the current page to new excel file with current date and current
month, with others fixed formate which will add before or end of the
date or month and repleace the previous file.
format is RM-1 (current date like 13-11-04 or current month like
Nov-04) somke text .xls
i want to give a file name with following format:
(text+ space + (current date or current month) + text)
the current date and current month should be change according to system
date. and then only this function will continue forever.
the problem is i can't join the whole string and put them along with
the date variable.
here are the code which i have made it.
------
ActiveWorkbook.Save
Dim rmmth As Date
rmmth = Date
rmmth1 = Format$(rmmth, "dd-mm-yy")
rmmth2 = Format$(rmmth, "mmm-yy")
rmmth3 = Format$(rmmth, "mmm")
day1 = Day(rmmth)
'f1 = "rm -"
'f2 = ""
'f3 = ".xls"
'Dim fname1 As String
'fname1 = (f1 + f2 + day1 + f3)
fname1 = "RM-1 30-11-04.xls"
If Format(rmmth, "mmm") = "Jan" Or Format(rmmth, "mmm") = "Feb" Or
Format(rmmth, "mmm") = "Mar" Or Format(rmmth, "mmm") = "Apr" Or
Format(rmmth, "mmm") = "May" Or Format(rmmth, "mmm") = "Jun" Or
Format(rmmth, "mmm") = "Jul" Or Format(rmmth, "mmm") = "Aug" Or
Format(rmmth, "mmm") = "Sep" Or Format(rmmth, "mmm") = "Oct" Or
Format(rmmth, "mmm") = "Nov" Or Format(rmmth, "mmm") = "Dec" Then
ChDir "D:\"
ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
Workbooks.Open Filename:="\\SS\ss_d\HOTEL HOST INTL. ROOM BILL
FORMAT\ROOM BILLS FORMAT IN EXCEL\all room bill 1 to 6.xls"
Windows("RM-1 13-11-04.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("all room bill 1 to 6.xls").Activate
ActiveWindow.WindowState = xlMaximized
End If
--------------------
you can see that i have make a manuall format with date, but i want to
make it automaticaly, which ever month it is it will take that month
and use that month along with fixed text formate and save it and same
will happen with daily basis also.
i need your help, can any one help me with any clue or suggession or
bit of code, so that 'fname' will change automaticaly according to
current date and month.
thanks in advance.
--
sujata_ghosh
------------------------------------------------------------------------
sujata_ghosh's Profile:
http://www.excelforum.com/member.php...o&userid=16432
View this thread: http://www.excelforum.com/showthread...hreadid=277972