View Full Version : Solved: Save current workbook to filename in format dd.mm.yy+string variable
PsYiOn
06-05-2008, 07:45 AM
I am trying to make a sub that will copy an active worksheet to a new sheet and then save it. I want it to save with a file name based on teh date and users name.
I have a variable setup that will hold the users name which is call strUser. Iv tried adding that to vba Date function but because of the /s it didnt work or at least i think thats why.
I have this so far
ActiveWorkbook.Sheets("Data").Activate
Dim NewName As String
Dim OldWkb As Workbook
MsgBox myTime
NewName = strUser
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=NewName
i need newname to be in this format DATE_USERNAME_RCA_REPORT with the date being sperated by dots liek this 05.06.08.
can anyone lend me a hand?
Kind Regards
PsYiOn
lifeson
06-05-2008, 08:23 AM
Where is myTime from?
Should n't that also be in your save filename eg
ActiveWorkbook.SaveAs Filename:=NewName & myTime
Bob Phillips
06-05-2008, 08:44 AM
ActiveWorkbook.SaveAs Filename:=NewName & "-" & Format(Date,"yyy-mm-dd")
lifeson
06-05-2008, 08:48 AM
Try
myTime = Format(Now, ("dd.mm.yy"))
ActiveWorkbook.SaveAs Filename:=myTime & "_" & user & "_RCA_REPORT"
PsYiOn
06-05-2008, 08:55 AM
Mytime is actually related to somthing i tried earlier but forgot to remove.
PsYiOn
06-05-2008, 09:06 AM
i used
myTime = Format(Now, ("dd.mm.yy"))
ActiveWorkbook.SaveAs Filename:=myTime & "_" & user & "_RCA_REPORT"
and it worked fine, thanks alot guys.
One more question though, can i have this file saved to a certain path say K:\doncast\miltonkeynesrepair\emc\reports
?
lifeson
06-05-2008, 09:15 AM
i used
myTime = Format(Now, ("dd.mm.yy"))
ActiveWorkbook.SaveAs Filename:=myTime & "_" & user & "_RCA_REPORT"
and it worked fine, thanks alot guys.
One more question though, can i have this file saved to a certain path say K:\doncast\miltonkeynesrepair\emc\reports
?
Ceratinly can
Dim fPath As String, saveName As String, user As String, mytime As String, fname As String
user = Application.UserName
mytime = Format(Now, ("dd.mm.yy"))
fPath = "K:\doncast\miltonkeynesrepair\emc\reports"
fname = mytime & "_" & user & "_RCA_REPORT"
saveName = fPath & fname
ActiveWorkbook.SaveAs Filename:=saveName
marshybid
06-05-2008, 09:17 AM
Hi There,
this is what you currently have
Code:
myTime = Format(Now, ("dd.mm.yy")) ActiveWorkbook.SaveAs Filename:=myTime & "_" & user & "_RCA_REPORT"
I would do the following
Dim FileLoc As String
myTime = Format(Now, ("dd.mm.yy"))
FileLoc = "K:\doncast\miltonkeynesrepair\emc\reports"
ActiveWorkbook.SaveAs Filename:=FileLoc & myTime & "_" & user & "_RCA_REPORT"
I think this will work, it seems to work for me.
Marshybid
PsYiOn
06-05-2008, 09:49 AM
I used
Dim FileLoc As String
myTime = Format(Now, ("dd.mm.yy"))
FileLoc = "K:\doncast\miltonkeynesrepair\emc\reports"
ActiveWorkbook.SaveAs Filename:=FileLoc & myTime & "_" & user & "_RCA_REPORT"
Because i understood everything that was being done. Thanks for all the help guys, this forum seems like the place to come for help. I tried ozgrid and got banend for incorrect thread name. I thought i had followed the proceedure they had made a sticky thread but obviously not.... Banned for 2 weeks.... didnt even tell me exactly how id gone wrong....
Anyway this place is kool and thansk for all your help/time.:beerchug:
Bob Phillips
06-05-2008, 10:04 AM
I tried ozgrid and got banend for incorrect thread name. I thought i had followed the proceedure they had made a sticky thread but obviously not.... Banned for 2 weeks.... didnt even tell me exactly how id gone
:rotlaugh: Everybody gets banned from OzGrid, your aren't a VBA developer until you do.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.