Results 1 to 8 of 8

Modify Macro To Save In To Another Folder

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Modify Macro To Save In To Another Folder

    Hi

    Can someone please help me with this?

    I have a macro listed below, all the maco does is save a back up of the file i am saving just incase the file gets overwritten by mistake.

    At the moment the back up file saves to the same directory as the workbook being saved, i would like it to save to a folder in the same directory called "Back Up" but i am having problems doing this. I do not want to put in the C:\Directory\Filename all i want to do is save to the folder "Back Up" in the same directory as the orig file no matter where it is.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)
    
    Dim OriginalName As String
    Dim BackupName As String
    OriginalName = ThisWorkbook.FullName
    BackupName = Left(OriginalName, Len(OriginalName) - 4) & ".bak" & _
    Right(OriginalName, 4)
    Application.EnableEvents = False
    Application.DisplayAlerts = False ' no "file exists" please
    ActiveWorkbook.SaveAs Filename:=BackupName, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:=OriginalName, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True ' turn them back on
    Application.EnableEvents = True ' and these also
    Cancel = True ' we did it once, why do it again
    
    End Sub
    Last edited by JimmiOO; 11-15-2010 at 04:40 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