Results 1 to 5 of 5

Stop overwrite of file if it already exists, no popup wanted

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Stop overwrite of file if it already exists, no popup wanted

    I can get rid of the popup/warning about overwriting an existing file, but I just can't figure out how to get the macro NOT to overwrite the file if it already exists. A coworker ended up deleting a couple days worth of data entry the beginning of this month, so it was no big deal at that time (barely an hours worth of work this time), but I want to prevent a complete loss of the month if they decide to do it again later.

    I've included the entire code below, but have highlighted the coding I'm working with in red below. So long as I leave out the red coding, the popup comes up, but nobody reads it, so the file gets overwritten

    Thanks in advance for your help

    Sub OpenAndSaveNew31()
    '
    ' OpenAndSaveNew Macro
    '
    '   This saves a copy of the current file into the backup folder
        ActiveWorkbook.Save
        Application.ScreenUpdating = False
    '      Define source file name, not including path.
        SourceFile = UCase(Format(DateAdd("m", -1, Date), "YYYY-MM")) & ".xlsm"
    '      Define target file name. Include path if needed.
        DestinationFile = "C:\Users\UserName\DestFolder\" & UCase(Format(DateAdd("m", -1, Date), "YYYY-MM")) & ".xlsm"
    '      Copy source to target.
        Workbooks(SourceFile).SaveCopyAs DestinationFile
    
    '   This opens the 31-Day workbook
        Workbooks.Open Filename:="C:\Users\UserName\SourceFolder\"
        ActiveWorkbook.RunAutoMacros xlAutoOpen
        
    '   This will save the workbook by the current year and month
        Dim sFile As String
        sFile = Format(Now(), "yyyy-mm") & ".xlsm" ' generates the file name for the current year-month
        Application.DisplayAlerts = False  '<-- Automatically overwrites the file if it exists already, don't want it, change to not overwrite existing AND no popup
        ActiveWorkbook.SaveAs Filename:="C:\Users\UserName\SourceFolder\" & sFile
      
    '   This will create a shortcut of the saved file in U:\shortcuts
        Filename = "U:\shortcuts\" & sFile & ".lnk" ' link name
        With CreateObject("WScript.Shell").CreateShortcut(Filename)
          .TargetPath = "C:\Users\UserName\SourceFolder\" & sFile
          .Save
        End With
            
    '   This will save and close the workbook just created
        ActiveWorkbook.Save
        ActiveWorkbook.Close
                
    End Sub
    Last edited by LoneWolf3574; 12-06-2012 at 03:01 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1