add three parts of text with one date into a string
 

Hi to all!

i have written a programme in VBA, which will do following tusk whe
someone press the button

save the current page to new excel file with current date and curren
month, with others fixed formate which will add before or end of th
date or month and repleace the previous file.

format is RM-1 (current date like 13-11-04 or current month lik
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 syste
date. and then only this function will continue forever.
the problem is i can't join the whole string and put them along wit
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" O
Format(rmmth, "mmm") = "Mar" Or Format(rmmth, "mmm") = "Apr" O
Format(rmmth, "mmm") = "May" Or Format(rmmth, "mmm") = "Jun" O
Format(rmmth, "mmm") = "Jul" Or Format(rmmth, "mmm") = "Aug" O
Format(rmmth, "mmm") = "Sep" Or Format(rmmth, "mmm") = "Oct" O
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 BIL
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 t
make it automaticaly, which ever month it is it will take that mont
and use that month along with fixed text formate and save it and sam
will happen with daily basis also.

i need your help, can any one help me with any clue or suggession o
bit of code, so that 'fname' will change automaticaly according t
current date and month.

thanks in advance

--
sujata_ghos
-----------------------------------------------------------------------
sujata_ghosh's Profile: http://www.excelforum.com/member.php...fo&userid=1643
View this thread: http://www.excelforum.com/showthread.php?threadid=27797

add three parts of text with one date into a string
 

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

add three parts of text with one date into a string
 

What kind of date wouldn't pass your "IF" test?

Ampersand is the best concatenation operation. the Plus sign can
misinterpret your intent if you are trying to concatenate numbers.

fname1 = "RM-1 " & format(Date,"dd-mm-yy") & ".xls"

should give you what you need:

Testing in the immediate window:
fname1 = "RM-1 " & format(Date,"dd-mm-yy") & ".xls"
? fname1
RM-1 13-11-04.xls


Sub AAA()
Dim fname1 as String
fname1 = "RM-1 " & format(Date,"dd-mm-yy") & ".xls"
ChDrive "D"
ChDir "D:\"
ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=xlWorkbookNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.close SaveChanges:=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"
ActiveWindow.WindowState = xlMaximized
End Sub

should be most of what you need.

--
Regards,
Tom Ogilvy



"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