Macro stops after passing control and closing the original file

whose2know

Board Regular
Joined
May 1, 2002
Messages
59
I have a template that periodiocally checks for version control on a server, and if the versions don't match, I want to replace the template on the harddrive that the user is using. I have it set up that the template macro opens a version control macro on a server and then that version control macro should shut down the template and save the file over it. The problem is when the template is closed, then the macro stops working. Any thoughts on how to complete this? I'm sure its something that has been done before, but I could not find it through searching.

Main template code example:
Code:
Sub UpdateTemplate()
Application.DisplayAlerts = False
On Error GoTo FNF
Let FPath = Range("Path") & "\"
Let UpdateFilename = Range("UpdateFN")
Let TempFileII = "UpdateBAMTemplate.xlsm"
Application.Run "'" & FPath & UpdateFilename & "'!UpdateBAMTemplate"
Application.DisplayAlerts = True
Exit Sub

FNF:
MsgBox "Update Check File Not Found...Are you Logged onto VPN?"
Application.DisplayAlerts = True
End Sub
' * * * * * * * * * * * * * * *
This is the macro in that is called
' * * * * * * * * * * * * * * *
Code:
Sub UpdateBAMTemplate()
Let MasterFile = ActiveWindow.Caption
Let MasterPath = ActiveWorkbook.Path

Let TempFileII = "UpdateBAMTemplate.xlsm"
Let CheckVersion = Range("M8")
Windows(TempFileII).Activate
Let NewVersionPath = ActiveWorkbook.Path
Let UpdateVersion = Range("A2")

If UpdateVersion > CheckVersion Then
    Windows(MasterFile).Close '*** MACRO STOPS AFTER THIS POINT!!
    MsgBox "Your Template is Out Of Date...Copying over the latest version to your Hard drive"
    Let NewVersion = NewVersionPath & MasterFile
    Let OldVersion = MasterPath & MasterFile
    FileCopy FP1, FP2
Workbooks.Open Filename:=FP2
Else
    MsgBox "Your Template is up to date"
    Windows(MasterFile).Activate
    Range("A4").Value = Date
    Application.Run "'" & TempFile & "'!LockedMode"
    ActiveWorkbook.Save
    Application.Run "'" & TempFile & "'!ReOpen"
End If

Windows(TempFileII).Close 'Activate
End Sub

I want it to copy over the newer version and then open the newer version back up on the user's harddrive. However, the macro stops once the original file is closed. I can't save the file over an open file...any thoughts?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
The macro should reside in a third workbook that handles the opening and closing of the template file. It can then close itself when finished (this might end all code at this point). The code you have now should probably run this other bit of code {check if a new version is availabe, if so --> open the helper workbook, run the code}.

Wonder if you couldn't just point users to the template on the server when they need the template - then they'll have the latest all the time without the need for code.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top