dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    10

    Resolved [RESOLVED] [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    Hello,

    I'm using the following code to auto save a dated and time stamped backup copy of a workbook. Aside from removing the date and time, is there a way to keep it from creating a new copy each time it auto saves?

    Sub SaveThis()
    Dim dt As String, wbPath As String
    wbPath = "C:\Users\ashley.browne\Desktop"
    dt = Format(CStr(Now), " mm-dd-yy hhmm")
    ThisWorkbook.SaveCopyAs wbPath & "\ITPM Inspection Tracking Spreadseet" & dt & ".xlsm"
    Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
    End Sub

    Thanks!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,532

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    Code:
    Sub saveBook()
        Dim str As String
        
        str = "c:\yourPath\testfile.xlsm"
        
        ThisWorkbook.SaveAs str
    End Sub

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    is there a way to keep it from creating a new copy each time it auto saves?
    not if you are using a unique file name each time, you could always delete the previous copy (or all previous copies) each time
    or save without the minutes part of the timestamp, then it should only create a new copy every hour, the file will still have a full timestamp just not as part of the filename
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    10

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    Thank you for your response, I'm not sure that's what I'm looking for though. I tried your code and it isn't doing what I need (I'm very new to Excel VBA though so maybe I'm doing something wrong).

    I'm sure you can see what my code does just by looking at it, but just to clarify that and what I'm trying to accomplish:
    Without the date and timestamp in the file name the code will execute an auto save every minute, and the first auto save creates a backup copy that all subsequent saves are saved to. The date and timestamp throw a wrench in that because each new timestamp = new name/new file. Ideally I would like to find a way around this "new name/new file with each timestamp" problem and have all saves continue to save to the same backup to eliminate an excess of files saving.

    Thanks again

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    10

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    Quote Originally Posted by westconn1 View Post
    not if you are using a unique file name each time, you could always delete the previous copy (or all previous copies) each time
    or save without the minutes part of the timestamp, then it should only create a new copy every hour, the file will still have a full timestamp just not as part of the filename
    I was kind of thinking that would be the case, but I'm super new to Excel VBA so I wanted to ask some experts.
    Honestly, the date and timestamp are a bit redundant to me since the file has a last modified date and time in the windows explorer, but it's for a client.

    Thanks for your help!

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    you could test
    Code:
    kill wbPath & "\ITPM Inspection Tracking Spreadseet*-*.xlsm"
    BEFORE saving the next copy
    all files with date format in matching base file name will be deleted
    you may need to test if any file exists that would be deleted else an error will occur in no matching files, use DIR to check if some file(s) exist
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    10

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    Thanks! I'll give it a go.

    I'm also wondering, since the code now auto saves everything to the copy, would it be easier to append the date and timestamp to the original since the only saving that happens there is upon closing?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: [Excel] .SaveCopyAs Dated and Time Stamped Backup That Saves to the Same Copy

    would it be easier to append the date and timestamp to the original
    you will still have the same problem, the original will still exist each time it is savedAs

    the choice comes back to what the user DEMANDS
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width