Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date in Text String | Excel Worksheet Functions | |||
Date format in a text string | Excel Worksheet Functions | |||
Extract certain parts of string | Excel Worksheet Functions | |||
Extract certain parts of string | Excel Worksheet Functions | |||
How do I convert a text string into a date? | Excel Worksheet Functions |