Passing info to the Workbook.Open event

[Brad E.]

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.

 

[B Lynn B]

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

 

[EricG]

I have added version control to some of the templates I create. The code
below, which I place in the Workbook_Open event, checks the user's version of
the file against the "master", wherever that is. If the versions are
different, the user gets a message. Alter to suit your needs. This code
does not require opening the "master" template file. The code assumes there
is a worksheet named "HiddenSheet" in both files that has in cell A1 the
label "Version", and in B1 the value of the version number. The sheet, of
course, should be hidden.

Watch out for line wraps, etc!

HTH,

Eric

'************************
' CODE BEGINS HERE
'************************
Option Explicit

' Set this constant to the path and filename of the master form
Private Const masterFile = "D:\data\My_Folder\Desktop\Master.xls"

Private Sub Workbook_Open()
Dim conData As Object
Dim rstAssigns As Object
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
'
Set conData = CreateObject("ADODB.Connection")
Set rstAssigns = CreateObject("ADODB.Recordset")
'
' Open a data connection to the "master" form so that we
' can check its version number without opening it.
'
With conData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & masterFile & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With
'
' The worksheet named "HiddenSheet" (no $) must be present in
' the "master" file, and on it must be the word "Version" in
' cell A1 and the version number (like 2.1) in cell A2.
'
strSelect = "SELECT * FROM [HiddenSheet$]"
'
On Error GoTo Oops
'
' Open the recordset so we can read the version number
'
rstAssigns.Open strSelect, conData, adOpenStatic, adLockReadOnly,
adCmdText
'
On Error GoTo 0
'
Do While Not rstAssigns.EOF ' We loop, but there is really on one entry
For intCount = 0 To rstAssigns.Fields.Count - 1
'
' Check to see if the master version number and the version
' of this file are the same.
'
' rstAssigns.Fields(intCount).Name is the name of the field ("Version")
' rstAssigns.Fields(intCount).Value is the value of that field (the version
number)
'
If (rstAssigns.Fields(intCount).Value <>
Me.Sheets("HiddenSheet").Cells(2, 1)) Then
MsgBox "Version Number in this file (" &
Me.Sheets("HiddenSheet").Cells(2, 1) & ") does not match" & Chr(10) & _
"the version number in master file (" &
rstAssigns.Fields(intCount).Value & ")" & Chr(10) & Chr(10) & _
"Please acquire and use the latest version of the
form." & Chr(10) & Chr(10) & _
"This file will now close." _
, vbOKOnly, "Mismatched Version Number"
' Me.Close SaveChanges:=False
End If
Next
rstAssigns.MoveNext
Loop
'
' Close the data connection
'
conData.Close
Set conData = Nothing
Set rstAssigns = Nothing
'
Exit Sub
'
Oops:
Debug.Print "Oops! Unable to read the master file's version number."
Debug.Print "Error Message: " & Err.Description
End Sub

 

[Chip Pearson]

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event.

No, parameters cannot be passed to Workbook_Open.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Option Explicit requires that you explicitly declare your variables
with the Dim statement. Without it, VBA will create a new variable
when it is encountered in running code. This may seem nice, but can
make debugging very difficult. Suppose you have variable named MyIndex
declared at the top of the procedure and you use MyIndx 500 lines of
code later. Are you going to notice the misspelling? Without Option
Explicit, VBA would create a new variable named MyIndx initialized to
zero. These sorts of bugs are very hard to find, especially if someone
else wrote the code. Without detailed knowledge of the code, it would
be a shot in the dark to determine if an existing variable name is
misspelled or is intentionally created as a new variable. Option
Explicit gets rid of all that ambiguity. You can configure VBA to
always include an Option Explicit statement in new created modules. Go
to the Tools menu, choose Options, and check the "require variable
declaration" check box.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template

Variables cannot be assigned a default value. When created, they are
initialized to the default for their data type, e.g., 0, vbNullString,
Empty, or Nothing. To initialize them to another value required code
running to do that. Code such as "Dim B As Boolean = True" will
fail with a compiler error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Eric - I appreciate your reply. I actually am using a .txt file with my
template Version number in it. I read the single line entry in that file to
determine if the versions are the same. I've got that part working.

I have had some other thoughts since my original post, and I am wondering if
you can answer them through this thread?

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event. Without receiving an answer yet,
either my post was too long or it can't be done.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template
file opens, it could check the Update_File value to determine if the template
is being opened for new entry (False) or if it should transfer data from the
calling workbook (True)?

If so, do I have to cancel or turn off the variable before ending the Sub?
Do I do this from the Template macro or the macro in the original workbook
(the one that sets the variable)?
-- TIA, Brad E.

I have added version control to some of the templates I create. The code
below, which I place in the Workbook_Open event, checks the user's version of
the file against the "master", wherever that is. If the versions are
different, the user gets a message. Alter to suit your needs. This code
does not require opening the "master" template file. The code assumes there
is a worksheet named "HiddenSheet" in both files that has in cell A1 the
label "Version", and in B1 the value of the version number. The sheet, of
course, should be hidden.

Watch out for line wraps, etc!

HTH,

Eric

'************************
' CODE BEGINS HERE
'************************
Option Explicit

' Set this constant to the path and filename of the master form
Private Const masterFile = "D:\data\My_Folder\Desktop\Master.xls"

Private Sub Workbook_Open()
Dim conData As Object
Dim rstAssigns As Object
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
'
Set conData = CreateObject("ADODB.Connection")
Set rstAssigns = CreateObject("ADODB.Recordset")
'
' Open a data connection to the "master" form so that we
' can check its version number without opening it.
'
With conData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & masterFile & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With
'
' The worksheet named "HiddenSheet" (no $) must be present in
' the "master" file, and on it must be the word "Version" in
' cell A1 and the version number (like 2.1) in cell A2.
'
strSelect = "SELECT * FROM [HiddenSheet$]"
'
On Error GoTo Oops
'
' Open the recordset so we can read the version number
'
rstAssigns.Open strSelect, conData, adOpenStatic, adLockReadOnly,
adCmdText
'
On Error GoTo 0
'
Do While Not rstAssigns.EOF ' We loop, but there is really on one entry
For intCount = 0 To rstAssigns.Fields.Count - 1
'
' Check to see if the master version number and the version
' of this file are the same.
'
' rstAssigns.Fields(intCount).Name is the name of the field ("Version")
' rstAssigns.Fields(intCount).Value is the value of that field (the version
number)
'
If (rstAssigns.Fields(intCount).Value <>
Me.Sheets("HiddenSheet").Cells(2, 1)) Then
MsgBox "Version Number in this file (" &
Me.Sheets("HiddenSheet").Cells(2, 1) & ") does not match" & Chr(10) & _
"the version number in master file (" &
rstAssigns.Fields(intCount).Value & ")" & Chr(10) & Chr(10) & _
"Please acquire and use the latest version of the
form." & Chr(10) & Chr(10) & _
"This file will now close." _
, vbOKOnly, "Mismatched Version Number"
' Me.Close SaveChanges:=False
End If
Next
rstAssigns.MoveNext
Loop
'
' Close the data connection
'
conData.Close
Set conData = Nothing
Set rstAssigns = Nothing
'
Exit Sub
'
Oops:
Debug.Print "Oops! Unable to read the master file's version number."
Debug.Print "Error Message: " & Err.Description
End Sub

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.

 

[Brad E.]

Thanks, Chip. I have come across misspelled variables and have had to use my
time to find them. I will start opening with Option Explicit from now on.

My second question wasn't so much if I can set a Boolean to True when I
initialize the variable, but can I declare a variable in any way that macros
from other workbooks can use the variable? For example, I am checking if the
workbook which someone is opening (I'll call this OriginalWB) is up-to-date.
If not, I want to open the updated workbook (TemplateWB) and do some updating
to OriginalWB. However, TemplateWB can also be opened by a user, so I have
to do a check when TemplateWB opens to see if it opened from a macro or not.
If it did, then I will do my updating.

Could I use something like this in OriginalWB
Public Boolean Update_File
Update_File = True

'then open TemplateWB from within OriginalWB and have OriginalWB's
Workbook_Open() event check the value of Update_File (OrigianlWB's variable
of Update_File)?

This coding is just guesses on my part. Do I have to have a Dim Update_File
as Boolean statement? Is Public correct???
-- Brad E.

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event.

No, parameters cannot be passed to Workbook_Open.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Option Explicit requires that you explicitly declare your variables
with the Dim statement. Without it, VBA will create a new variable
when it is encountered in running code. This may seem nice, but can
make debugging very difficult. Suppose you have variable named MyIndex
declared at the top of the procedure and you use MyIndx 500 lines of
code later. Are you going to notice the misspelling? Without Option
Explicit, VBA would create a new variable named MyIndx initialized to
zero. These sorts of bugs are very hard to find, especially if someone
else wrote the code. Without detailed knowledge of the code, it would
be a shot in the dark to determine if an existing variable name is
misspelled or is intentionally created as a new variable. Option
Explicit gets rid of all that ambiguity. You can configure VBA to
always include an Option Explicit statement in new created modules. Go
to the Tools menu, choose Options, and check the "require variable
declaration" check box.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template

Variables cannot be assigned a default value. When created, they are
initialized to the default for their data type, e.g., 0, vbNullString,
Empty, or Nothing. To initialize them to another value required code
running to do that. Code such as "Dim B As Boolean = True" will
fail with a compiler error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Eric - I appreciate your reply. I actually am using a .txt file with my
template Version number in it. I read the single line entry in that file to
determine if the versions are the same. I've got that part working.

I have had some other thoughts since my original post, and I am wondering if
you can answer them through this thread?

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event. Without receiving an answer yet,
either my post was too long or it can't be done.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template
file opens, it could check the Update_File value to determine if the template
is being opened for new entry (False) or if it should transfer data from the
calling workbook (True)?

If so, do I have to cancel or turn off the variable before ending the Sub?
Do I do this from the Template macro or the macro in the original workbook
(the one that sets the variable)?
-- TIA, Brad E.

I have added version control to some of the templates I create. The code
below, which I place in the Workbook_Open event, checks the user's version of
the file against the "master", wherever that is. If the versions are
different, the user gets a message. Alter to suit your needs. This code
does not require opening the "master" template file. The code assumes there
is a worksheet named "HiddenSheet" in both files that has in cell A1 the
label "Version", and in B1 the value of the version number. The sheet, of
course, should be hidden.

Watch out for line wraps, etc!

HTH,

Eric

'************************
' CODE BEGINS HERE
'************************
Option Explicit

' Set this constant to the path and filename of the master form
Private Const masterFile = "D:\data\My_Folder\Desktop\Master.xls"

Private Sub Workbook_Open()
Dim conData As Object
Dim rstAssigns As Object
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
'
Set conData = CreateObject("ADODB.Connection")
Set rstAssigns = CreateObject("ADODB.Recordset")
'
' Open a data connection to the "master" form so that we
' can check its version number without opening it.
'
With conData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & masterFile & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With
'
' The worksheet named "HiddenSheet" (no $) must be present in
' the "master" file, and on it must be the word "Version" in
' cell A1 and the version number (like 2.1) in cell A2.
'
strSelect = "SELECT * FROM [HiddenSheet$]"
'
On Error GoTo Oops
'
' Open the recordset so we can read the version number
'
rstAssigns.Open strSelect, conData, adOpenStatic, adLockReadOnly,
adCmdText
'
On Error GoTo 0
'
Do While Not rstAssigns.EOF ' We loop, but there is really on one entry
For intCount = 0 To rstAssigns.Fields.Count - 1
'
' Check to see if the master version number and the version
' of this file are the same.
'
' rstAssigns.Fields(intCount).Name is the name of the field ("Version")
' rstAssigns.Fields(intCount).Value is the value of that field (the version
number)
'
If (rstAssigns.Fields(intCount).Value <>
Me.Sheets("HiddenSheet").Cells(2, 1)) Then
MsgBox "Version Number in this file (" &
Me.Sheets("HiddenSheet").Cells(2, 1) & ") does not match" & Chr(10) & _
"the version number in master file (" &
rstAssigns.Fields(intCount).Value & ")" & Chr(10) & Chr(10) & _
"Please acquire and use the latest version of the
form." & Chr(10) & Chr(10) & _
"This file will now close." _
, vbOKOnly, "Mismatched Version Number"
' Me.Close SaveChanges:=False
End If
Next
rstAssigns.MoveNext
Loop
'
' Close the data connection
'
conData.Close
Set conData = Nothing
Set rstAssigns = Nothing
'
Exit Sub
'
Oops:
Debug.Print "Oops! Unable to read the master file's version number."
Debug.Print "Error Message: " & Err.Description
End Sub


:

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.

.

 

[Chip Pearson]

You can declare a variable (as Public) in one workbook and change its
value from another workbook if the second workbook has a VBA reference
to the project containing the variable to be changed. Say you have two
workbook, Book1.xls which contain the variable declaration and
Book2.xls that contains code that is to change the variable in Book1.
In VBA, open Book1, go to the Tools menu, choose VBA Project
Properties, and change the project name from VBProject to something
more meaningful, say projTest. Then, open Book2 in VBA and go to the
Tools menu, choose References, and check the entry for projTest.

Now, if Book1(projTest) has the declaration

Public MyVar As Boolean

that variable can be change from Book2 with the code

projTest.MyVar = True

To do this sort of thing with variables, a reference must exist
between the projects. If you don't want to require a reference, you
can create a procedure that changes the variable value. For example,
in Book1 (projTest), use code like

Public MyVar As Boolean
Public Sub ChangeMyVar(B As Boolean)
MyVar = B
End Sub

You can then call this from Book2, without the reference to projTest,
by using Application.Run:

Sub AAA
Application.Run "Book1.xlsm!ChangeMyVar"
End Sub

Using Application.Run doesn't require a reference between the
workbooks, but cannot be used to change a variable. It can only call
(Public) procedures.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Thanks, Chip. I have come across misspelled variables and have had to use my
time to find them. I will start opening with Option Explicit from now on.

My second question wasn't so much if I can set a Boolean to True when I
initialize the variable, but can I declare a variable in any way that macros
from other workbooks can use the variable? For example, I am checking if the
workbook which someone is opening (I'll call this OriginalWB) is up-to-date.
If not, I want to open the updated workbook (TemplateWB) and do some updating
to OriginalWB. However, TemplateWB can also be opened by a user, so I have
to do a check when TemplateWB opens to see if it opened from a macro or not.
If it did, then I will do my updating.

Could I use something like this in OriginalWB
Public Boolean Update_File
Update_File = True

'then open TemplateWB from within OriginalWB and have OriginalWB's
Workbook_Open() event check the value of Update_File (OrigianlWB's variable
of Update_File)?

This coding is just guesses on my part. Do I have to have a Dim Update_File
as Boolean statement? Is Public correct???
-- Brad E.

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event.

No, parameters cannot be passed to Workbook_Open.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Option Explicit requires that you explicitly declare your variables
with the Dim statement. Without it, VBA will create a new variable
when it is encountered in running code. This may seem nice, but can
make debugging very difficult. Suppose you have variable named MyIndex
declared at the top of the procedure and you use MyIndx 500 lines of
code later. Are you going to notice the misspelling? Without Option
Explicit, VBA would create a new variable named MyIndx initialized to
zero. These sorts of bugs are very hard to find, especially if someone
else wrote the code. Without detailed knowledge of the code, it would
be a shot in the dark to determine if an existing variable name is
misspelled or is intentionally created as a new variable. Option
Explicit gets rid of all that ambiguity. You can configure VBA to
always include an Option Explicit statement in new created modules. Go
to the Tools menu, choose Options, and check the "require variable
declaration" check box.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template

Variables cannot be assigned a default value. When created, they are
initialized to the default for their data type, e.g., 0, vbNullString,
Empty, or Nothing. To initialize them to another value required code
running to do that. Code such as "Dim B As Boolean = True" will
fail with a compiler error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

Eric - I appreciate your reply. I actually am using a .txt file with my
template Version number in it. I read the single line entry in that file to
determine if the versions are the same. I've got that part working.

I have had some other thoughts since my original post, and I am wondering if
you can answer them through this thread?

My original question is asking about passing information to another file and
specifically to the Workbook_Open() event. Without receiving an answer yet,
either my post was too long or it can't be done.

The Code you posted starts with Option Explicit - can you explain what this
does, because I see it in the discussion posts all the time, but I never use
it.

Also, you use Private Const masterFile = ... Is there a Public function I
could use, such as "Public Boolean Update_File = True" and when the template
file opens, it could check the Update_File value to determine if the template
is being opened for new entry (False) or if it should transfer data from the
calling workbook (True)?

If so, do I have to cancel or turn off the variable before ending the Sub?
Do I do this from the Template macro or the macro in the original workbook
(the one that sets the variable)?
-- TIA, Brad E.

:

I have added version control to some of the templates I create. The code
below, which I place in the Workbook_Open event, checks the user's version of
the file against the "master", wherever that is. If the versions are
different, the user gets a message. Alter to suit your needs. This code
does not require opening the "master" template file. The code assumes there
is a worksheet named "HiddenSheet" in both files that has in cell A1 the
label "Version", and in B1 the value of the version number. The sheet, of
course, should be hidden.

Watch out for line wraps, etc!

HTH,

Eric

'************************
' CODE BEGINS HERE
'************************
Option Explicit

' Set this constant to the path and filename of the master form
Private Const masterFile = "D:\data\My_Folder\Desktop\Master.xls"

Private Sub Workbook_Open()
Dim conData As Object
Dim rstAssigns As Object
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String
'
Set conData = CreateObject("ADODB.Connection")
Set rstAssigns = CreateObject("ADODB.Recordset")
'
' Open a data connection to the "master" form so that we
' can check its version number without opening it.
'
With conData
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & masterFile & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With
'
' The worksheet named "HiddenSheet" (no $) must be present in
' the "master" file, and on it must be the word "Version" in
' cell A1 and the version number (like 2.1) in cell A2.
'
strSelect = "SELECT * FROM [HiddenSheet$]"
'
On Error GoTo Oops
'
' Open the recordset so we can read the version number
'
rstAssigns.Open strSelect, conData, adOpenStatic, adLockReadOnly,
adCmdText
'
On Error GoTo 0
'
Do While Not rstAssigns.EOF ' We loop, but there is really on one entry
For intCount = 0 To rstAssigns.Fields.Count - 1
'
' Check to see if the master version number and the version
' of this file are the same.
'
' rstAssigns.Fields(intCount).Name is the name of the field ("Version")
' rstAssigns.Fields(intCount).Value is the value of that field (the version
number)
'
If (rstAssigns.Fields(intCount).Value <>
Me.Sheets("HiddenSheet").Cells(2, 1)) Then
MsgBox "Version Number in this file (" &
Me.Sheets("HiddenSheet").Cells(2, 1) & ") does not match" & Chr(10) & _
"the version number in master file (" &
rstAssigns.Fields(intCount).Value & ")" & Chr(10) & Chr(10) & _
"Please acquire and use the latest version of the
form." & Chr(10) & Chr(10) & _
"This file will now close." _
, vbOKOnly, "Mismatched Version Number"
' Me.Close SaveChanges:=False
End If
Next
rstAssigns.MoveNext
Loop
'
' Close the data connection
'
conData.Close
Set conData = Nothing
Set rstAssigns = Nothing
'
Exit Sub
'
Oops:
Debug.Print "Oops! Unable to read the master file's version number."
Debug.Print "Error Message: " & Err.Description
End Sub


:

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.

.