PDA

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.