1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by Trevors726, Feb 13, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Trevors726

    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
     
  2. Rollin_Again

    Rollin_Again

    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
     
  3. Trevors726

    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
     
  4. nexami

    nexami

    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.
     
  5. Rollin_Again

    Rollin_Again

    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

    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.

     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    @ Nexami

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


    Rollin
     
  7. nexami

    nexami

    Joined:
    Feb 14, 2013
    Messages:
    3
    sorry abt that ,,, just created a new thread
     
  8. Trevors726

    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
     
  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089329

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice