Results 1 to 6 of 6

Thread: How to update the copy of Excel from the original

  1. #1
    Seeker samsnov's Avatar
    Join Date
    Mar 2013
    Posts
    12
    Articles
    0
    Excel Version
    Excel 2016

    How to update the copy of Excel from the original



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everybody, please can someone help me out here, I have an Excel file copy in a separate folder and I would want the Excel copy to update automatically (without opening the copy file) once I edit the original file, is there any way to achieve this aim.

  2. #2
    That would require some VB scripting to acheive that. Something that would trigger upon a Save action.

    Question: is the copy an exact copy? Or, is it another file that contains data from the original?

    If it is an exact copy, a simple VB script that triggers on Save (or Close) can basically save the file to a second location. I don't have the code available, atm, but it is not difficult to do. Start with a Macro that follows your actions and then edit it for the specifics.

  3. #3
    Seeker samsnov's Avatar
    Join Date
    Mar 2013
    Posts
    12
    Articles
    0
    Excel Version
    Excel 2016
    Thank you, I will try by your instruction and get you informed when through

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Would you be wanting to save backups each time or just overwrite the other copy?
    Hope that helps

    Roy

  5. #5
    Seeker samsnov's Avatar
    Join Date
    Mar 2013
    Posts
    12
    Articles
    0
    Excel Version
    Excel 2016
    No I wouldn't want to save the backup each time but to overwrite the existing copy of the file

  6. #6
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    This might get you started.
    Code:
    Option Explicit
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    
        Const sBackupPath As String = "C:\Users\user name here\Backups\"
        Const sFilename As String = "MyTest2.xlsm"
    
    
        ActiveWorkbook.Saved = True
    
    
        Application.DisplayAlerts = False
        ChDir sBackupPath
        ActiveWorkbook.SaveAs Filename:=sBackupPath & sFilename, _
                              FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Application.DisplayAlerts = True
    
    
    End Sub
    Hope that helps

    Roy

Posting Permissions

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