VBA to update current open workbook

[diddi]

Morning all.
I have a workbook which is often updated. I have code that correctly identifies if an update has been posted to the server and notifies the user to close and get new copy. My question is: can i Shell to a .bat which will close excel, copy and replace the workbook and reopen the new version. (an auto-update). Any other options are welcome.
cheers

 

[yky]

Do you have to close Excel? I think it'd be relatively easy to write a VBA script to close the workbook, copy and replace the old workbook, and open the new workbook.

 

[yky]

Try this:

Sub update_file()

    Dim oFSO As Object
    Dim wb As Workbook
 
    Workbooks("myworkbook.xlsx").Close savechanges:=False
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFile "C:\temp\myworkbook.xlsx", "C:\temp\", True
    Set wb = Workbooks.Open("C:\temp\s\temp\myworkbook.xlsx")
    Set oFSO = Nothing
    Set wb = Nothing
End Sub

Need to set the proper path and file name

 

[diddi]

i will give it a try and get back. there is no requirement to close excel because i have appropriate initialisation management in the worksheet_open event
many thx for your thoughts

 

[diddi]

having taken on board the suggestion from @yky i have implemented the following code. unfortunately, once the workbook closes, no further lines are executed, so the file copy does not occur, nor does the workbook reopen. The other part which updates my new version to the server is fine because the server version is never opened.

Sub CheckForUpdate()
    Dim oFSO As Object, wb As Workbook, UpdatePath As String ' updated version
    Dim FSO As Object, CurrVers As Date, ThisVers As Date, UserPath As String ' current version
 
    UserPath = ActiveWorkbook.FullName
    UpdatePath = "\\ServerPath\FileName.xlsm"
 
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = oFSO.GetFile(UpdatePath)
    CurrVers = objFile.DateLastModified
    ThisVers = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
    Set oFSO = Nothing
    If CurrVers - ThisVers > 0.0001 Then
        Label20.Caption = "Hold on. There is a new version being installed"
        DoEvents
        Application.Wait (Now + TimeValue("0:00:03"))
        Workbooks("Onsite Labour 18.xlsm").Close savechanges:=False  ' code stops here
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        oFSO.CopyFile UpdatePath, UserPath, True
        Set wb = Workbooks.Open(UserPath)
        Set oFSO = Nothing
        Set wb = Nothing
    ElseIf CurrVers - ThisVers < 0.001 And CurrentUser = "Admin" Then
        Res = MsgBox("The server version needs updating", vbYesNo)
        If Res = vbYes Then
            Set oFSO = CreateObject("Scripting.FileSystemObject")
            oFSO.CopyFile UserPath, UpdatePath, True
            MsgBox "That worked"
        End If
    End If
End Sub

 

[yky]

The code I wrote is meant to be run from another workbook, not the one that's already running. I see now what you're trying to do. Try the following batch file which is named openexcel.bat.

rem this line kills the running Excel. Not sure what'd happen if you have multiple instances of Excel running
taskkill /im excel.exe

rem this line copy file from a directory to another. Should work for network drives also.
copy c:\temp\myworkbook.xlsm c:\temp\s\myworkbook.xlsm

rem this line starts an Excel file
start excel c:\temp\s\myworkbook.xlsm

Sub update_file()
   Shell "c:\openexcel.bat"
End Sub

Frankly, I'm very wary about these code. The control of program is passed from Excel to shell, which then abruptly kills Excel. I don't know what effect it has on the memory.

 

[diddi]

So we are back to Shell as I was first wondering. thx i shall give it a try. the workbook is purely a control tool from which all data is manipulated in random files, so hopefully excel quitting will not pose any problems.

 

[yky]

So we are back to Shell as I was first wondering. thx i shall give it a try. the workbook is purely a control tool from which all data is manipulated in random files, so hopefully excel quitting will not pose any problems.

In that case, maybe wrong workbook is closed. Instead of closing "Onsite Labour 18.xlsm", maybe userpath should be closed. This is the file to be updated, right? So, modify the following line of code to close userpath.

'Workbooks("Onsite Labour 18.xlsm").Close savechanges:=False  ' code stops here

Workbooks(userpath).Close savechanges:=False

The code will close userpath. The subsequent code, as listed in #5, will copy/update the file, then, open it.