VBA to update current open workbook

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
Try this:

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.


VBA Code:
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
 
Last edited:

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,271
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,833
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
'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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,561
Messages
5,625,510
Members
416,115
Latest member
Gonzo5711

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top