Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Backup File

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How Can I make Backup File with VBA Command (may be .tmp) ,Because sometime a Excel is Error . This BackupFile have to has the same PATH of Old File also . Thanks a lot.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If Dir(ThisWorkbook.parth & "tempname.tmp") <> "" Then Kill (ThisWorkbook.parth & "tempname.tmp")
    ActiveWorkbook.SaveCopyAs (ThisWorkbook.parth & "tempname.tmp")

    The first is to delete the existing backup copy of the file.
    Second line is the save a copy of the file to the same directory as the current file.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please note, for some reason, the forum converted my 1 slash into 2, so when you are copying the code, make sure you change any 2 slashes into 1.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One typo I have picked up. Instead of "parth", it should be "Path".

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Wichien,

    No need for VBA to do this. When saving, use SaveAs and click the Options button. There is a checkbox "Always Create Backup" which will do what you want.

    If you must do this through VBA, in the code module for ThisWorkbook try,

    ---begin VBA---
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Name, CreateBackup:=True
    Application.DisplayAlerts = True
    End Sub
    ---end VBA---

    This should do what you want. Please repost if you need something different.

    Thanks,
    Jay

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's what I want !. Thanks so much BabyTiger and Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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