How to update the copy of Excel from the original

samsnov

New member
Joined
Mar 18, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
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.
 

dond427

New member
Joined
Jan 7, 2013
Messages
13
Reaction score
0
Points
0
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.
 

samsnov

New member
Joined
Mar 18, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
Thank you, I will try by your instruction and get you informed when through
 

samsnov

New member
Joined
Mar 18, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
Excel 2016
No I wouldn't want to save the backup each time but to overwrite the existing copy of the file
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
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
 
Top