H
handstand
Hi All,
Lot of excellent help here - I am almost there - Please help me
connect the dots...
OK - what's the problem: * I will try to keep a version control of a
template I develop on an ongoing basis. The version number I will try
and use to provide an easy way to upgrade any file based on an older
version of the template by simply copying values that reside in cells
that I see on a list of named ranges into a file that is a copy of the
new template.*
- Does this make any sense...
How do I do this - I am not sure - I need help - here are my
thoughts...
My idea of an answer is this:
1: I will use the version control suggested by Tom Ogilvy in
http://www.excelforum.com/showthread.php?t=479156
And keep the version number in a property named "Version".
2: I have a hidden sheet named "config" in which i have an ongoing list
of cells holding the names of all the ranges that must be copied. The
list of named ranges is right below the cell named
"Config_values_to_copy", so
range("Config_values_to_copy").offset(1,0).value could be
"Priority_Column" in which case there is a column off values there,
that must be copied to the new template and placed in the same named
range.
3: I will keep the newest version of the xls-file (template) - not as a
xlt file, just xls for now somewhere - on the LAN in a specific folder
and always have the name of the latest version being: "Risc and issue
documentation Template, latest version.xls" (If possible I will
manually include the version number into the file name instead, and
make sure that there is only one file in the particular folder.) For
this help let's just say that the folder is local
"C:\latest-version-template\" I think that this can somehow be done
based in parts on the answer Ron de Bruin gives to this topic
http://www.excelforum.com/showthread.php?t=564858 - but I am not sure
that it is all there...
4: When a user opens a workbook based on the template a control button
named something like "check for new template version" should be
accessible - alternatively I will hide the code and do this when the
user opens the file.
5: Now here goes: The code must somehow check current version from step
(1) against the version found in the folder with the latest-greatest
version of this template. If the template available in that folder is
newer than the template that this current file is based upon, then
prompt the user if he wants to upgrade. If YES then I must somehow
iterate throught the named ranges and copy all the values onto a copy
of the new template and afterwards save this intelligently on the users
harddrive.
I imagine using this kind of iteration to go through the named ranges -
this code is altered a bit from elsewhere in the file:
i=1
While Len(Range("Config_values_to_copy").Offset(i, 0).Value) > 1
' As long as I am in this loop, then I have values that must be
copied to the new workbook.
[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value
i = i + 1
Wend
Can I do this - how do I copy a version of the newer template and paste
the values (by value) into the identical named ranges? Probably the line
[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value
needs some re-work. I just cannot see precisely what I need to do.
I look forward to some quality help.
Bye from Denmark
Soren
Lot of excellent help here - I am almost there - Please help me
connect the dots...
OK - what's the problem: * I will try to keep a version control of a
template I develop on an ongoing basis. The version number I will try
and use to provide an easy way to upgrade any file based on an older
version of the template by simply copying values that reside in cells
that I see on a list of named ranges into a file that is a copy of the
new template.*
- Does this make any sense...
How do I do this - I am not sure - I need help - here are my
thoughts...
My idea of an answer is this:
1: I will use the version control suggested by Tom Ogilvy in
http://www.excelforum.com/showthread.php?t=479156
And keep the version number in a property named "Version".
2: I have a hidden sheet named "config" in which i have an ongoing list
of cells holding the names of all the ranges that must be copied. The
list of named ranges is right below the cell named
"Config_values_to_copy", so
range("Config_values_to_copy").offset(1,0).value could be
"Priority_Column" in which case there is a column off values there,
that must be copied to the new template and placed in the same named
range.
3: I will keep the newest version of the xls-file (template) - not as a
xlt file, just xls for now somewhere - on the LAN in a specific folder
and always have the name of the latest version being: "Risc and issue
documentation Template, latest version.xls" (If possible I will
manually include the version number into the file name instead, and
make sure that there is only one file in the particular folder.) For
this help let's just say that the folder is local
"C:\latest-version-template\" I think that this can somehow be done
based in parts on the answer Ron de Bruin gives to this topic
http://www.excelforum.com/showthread.php?t=564858 - but I am not sure
that it is all there...
4: When a user opens a workbook based on the template a control button
named something like "check for new template version" should be
accessible - alternatively I will hide the code and do this when the
user opens the file.
5: Now here goes: The code must somehow check current version from step
(1) against the version found in the folder with the latest-greatest
version of this template. If the template available in that folder is
newer than the template that this current file is based upon, then
prompt the user if he wants to upgrade. If YES then I must somehow
iterate throught the named ranges and copy all the values onto a copy
of the new template and afterwards save this intelligently on the users
harddrive.
I imagine using this kind of iteration to go through the named ranges -
this code is altered a bit from elsewhere in the file:
i=1
While Len(Range("Config_values_to_copy").Offset(i, 0).Value) > 1
' As long as I am in this loop, then I have values that must be
copied to the new workbook.
[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value
i = i + 1
Wend
Can I do this - how do I copy a version of the newer template and paste
the values (by value) into the identical named ranges? Probably the line
[new workbook!] Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value = Range(Range("Config_values_to_copy").Offset(i,
0).Value).Value
needs some re-work. I just cannot see precisely what I need to do.
I look forward to some quality help.
Bye from Denmark
Soren