Solved: Excel Macro to save a copy of the file in new location with the date

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Trevors726

Thread Starter
Joined
Jan 7, 2013
Messages
7
I have a spreadsheet that is used by a number of users and has a macro built into it to collect updates from several other sheets. I want to add to the macro so that once it has been updated and saved the macro then saves another version of the sheet in a seperate folder with the date and time it was saved as part of the file name.

I have tried

ChDir"H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:= _
"H:\ResourcingTeams\Placements\Weekly activity\Archive\" & Now.Day & Now.Month &Now.Year &" .xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

But get errors

Any suggestions greatly appreciated
 
Joined
Sep 4, 2003
Messages
4,912
Instead of using Now.Day / Now.Month / Now.Year use the following:

Day(Date) / Month(Date) / Year(Date)

or

Day(Now) / Month(Now) / Year(Now)



Rollin
 

Trevors726

Thread Starter
Joined
Jan 7, 2013
Messages
7
Thanks Rollin,

I've tried
ChDir "H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:= _
"H:\Resourcing Teams\Placements\Weekly activity\Archive\ & Day(Date) / Month(Date) / Year(Date) & .xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

and the (Now) version i had to remove the / to make it run but now end up with a file called & Day(Date) not the actual date/ time it was saved.

I've also tried using a range on the spreadsheet that had the date and time in but becasue that has / within the date excel wont save the file name.

Any other thoughts on how i can make this work.

Cheers
 
Joined
Feb 14, 2013
Messages
3
I need to create a database out of daily emails dropping in a specific folder.

1> Emails seggregated into folders upon arrival with the help of RULES,
2> Upon arrival, same emails are Exported to Excel file
3> Excel file on Auto Save & Auto Update mode
4> all exported data is formatted as Database in Excel
5> Final view of Excel database on Internet Explorer Web View having a search box to run specific DATA search

Can anyone please help me out with this project with detailed guides.
 
Joined
Sep 4, 2003
Messages
4,912
You can't use some special characters in the filename. If you want to save with the date just use the replace command to remove the slashes and replace with hyphens

ActiveWorkbook.SaveAs Filename:= _
"H:\Resourcing Teams\Placements\Weekly activity\Archive\ & Replace(Date, "/", "-") & .xlsx"
If you want to use the date and time just use the NOW function and do the same as above but you'll also need to add an additional replace statement to remove the colon character and replace with another allowed character such as a period, underscore, or hyphen. By default the time function will include the number of seconds so if you want to remove that you will need to format the time as HH:MM before doing the replace.

ActiveWorkbook.SaveAs Filename:= _
"H:\Resourcing Teams\Placements\Weekly activity\Archive\ & Replace(Replace(Now, "/", "-"), ":", "-") & .xlsx"
 
Joined
Sep 4, 2003
Messages
4,912
@ Nexami

Please do not Hijack this thread. Please start a new thread for your own issue


Rollin
 

Trevors726

Thread Starter
Joined
Jan 7, 2013
Messages
7
Thanks for the help - have found some other similar posts and between tme have come up with

ChDir "H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:="H:\Resourcing Teams\Placements\Weekly activity\Archive\activity-tracker " & _
Format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=False

Which is working
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top