add three parts of text with one date into a string

  • Thread starter Thread starter sujata_ghosh
  • Start date Start date
S

sujata_ghosh

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
 
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)
 
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.
 
Back
Top