Passing info to the Workbook.Open event
 

I have a workbook template which I keep on our Network for multiple users to
be able to access and use. Once in a while, I will have to modify the file.
What I am looking to do is when someone saves a re-named copy for themselves,
and then access that file again in the future and there has been a
modification to the template, I want to update the file they are opening.

For example:
John opens the template file today, makes his changes and saves as "John
Doe" on his local drive.
Tomorrow, I find an error in a formula or coding, so I correct it in the
Template file.
The following day, John wants to make a change to his file, so he opens it.
The Workbook_Open macro recognizes there has been an update to the template
and asks John if he wants to update his file. John says Yes.

Now, the template also has a Workbook_Open macro. Is there a way for me to
pass some kind of Code in the process of my "John Doe" macro opening the
template, so that the Template knows to open differently than when someone
just regularly opens the template?

I need the update of the file to occur from the Template file, because if
there is anything new in the template, the old file, "John Doe", won't know
that. However, I can program any new Template to work appropriately
depending on the Version of the old file.

Some of the Code I have in my Workbook_Open event:
Dim MyVersion as Integer
Dim FileNumber as Integer
Dim TemplateFile as String
MyVersion = Range("Sheet3!$A$1").Value
FileNumber = FreeFile
TemplateFile = "M:\Folder1\MyTemplate.xltm"
Open TemplateFile For Input as #FileNumber.....here is where I need to pass
a variable to TemplateFile so the Template knows, upon the Workbook_Open
macro, if it should open and load from an old file or just load and be ready
for user entry.

-- Sorry this got to be long, but I appreciate any help with this. Brad E.

Passing info to the Workbook.Open event
 

If this were my problem, I would stop at notifying the user there is a newer
version of the template file, rather than trying to update their older saved
version. Here's some code that will do that. But you can add bits to try to
update their current file if you want, This will at least work to discover
when the update is needed.

dtLast would be edited each time you save a new version of the template so
it shows the date saved. and of course myFile would be the path to your
master template.

Private Sub Workbook_Open()

Dim objFSO As Object
Dim objFile As Object
Dim myFile As String
Dim dtLast As Date

dtLast = #1/1/2010#

myFile = "C:\MyFolder\MyFile.xls"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(myFile)
If DateValue(objFile.datelastmodified) < dtLast Then
MsgBox "Please replace this template file with latest version."
End If

Set objFSO = Nothing
Set objFile = Nothing

End Sub


"Brad E." wrote:

I have a workbook template which I keep on our Network for multiple users to
be able to access and use. Once in a while, I will have to modify the file.
What I am looking to do is when someone saves a re-named copy for themselves,
and then access that file again in the future and there has been a
modification to the template, I want to update the file they are opening.

For example:
John opens the template file today, makes his changes and saves as "John
Doe" on his local drive.
Tomorrow, I find an error in a formula or coding, so I correct it in the
Template file.
The following day, John wants to make a change to his file, so he opens it.
The Workbook_Open macro recognizes there has been an update to the template
and asks John if he wants to update his file. John says Yes.

Now, the template also has a Workbook_Open macro. Is there a way for me to
pass some kind of Code in the process of my "John Doe" macro opening the
template, so that the Template knows to open differently than when someone
just regularly opens the template?

I need the update of the file to occur from the Template file, because if
there is anything new in the template, the old file, "John Doe", won't know
that. However, I can program any new Template to work appropriately
depending on the Version of the old file.

Some of the Code I have in my Workbook_Open event:
Dim MyVersion as Integer
Dim FileNumber as Integer
Dim TemplateFile as String
MyVersion = Range("Sheet3!$A$1").Value
FileNumber = FreeFile
TemplateFile = "M:\Folder1\MyTemplate.xltm"
Open TemplateFile For Input as #FileNumber.....here is where I need to pass
a variable to TemplateFile so the Template knows, upon the Workbook_Open
macro, if it should open and load from an old file or just load and be ready
for user entry.

-- Sorry this got to be long, but I appreciate any help with this. Brad E.