I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony

No doubt this is possible with VBA code, although it could be rather complex.
If you're going to go to the trouble of writing/editing a file to update
from, why not just do the job once directly in the working file?

"Stilltrader47" wrote:

I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony

Thank-you, here's why. I will be distributing copies of the original
(template) file to many users, who will be adding data to and creating
statistics in their own dedicated versions of the file. If I discover that
the text and hyperlink for a comment cell must be updated, I would like to be
able to provide just those cells to the users so they can apply as an update
(without eliminating their accumulated data).

Any other thoughts you have, or specific to using VBA code will be
appreciated

Thanks - Sony

"JLatham" wrote:

No doubt this is possible with VBA code, although it could be rather complex.
If you're going to go to the trouble of writing/editing a file to update
from, why not just do the job once directly in the working file?

"Stilltrader47" wrote:

I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony

I'd probably write some code in the updating info file that would get the
user to open the file to be opened using Application.GetOpenFilename. That
lets me control the opening of the other file AND gives me the name of the
file that I need to change.
I'd probably set up the updating file itself with several columns of
information for each change to be made:
Column A = sheet name where the cell is to be found
Column B = address of the cell to be changed
Column C = new contents for the cell

I'm not quite sure of how you mean "names" in this context, I'm assuming you
mean a named range/cell. In that case you could place that into column B vs
the absolute cell address.

The code would simply go through your list from top to bottom and pick up
the necessary information and make the changes in the other workbook. You do
have to take into consideration that they may have renamed a sheet and/or
altered the layout on any given sheet (which is where named ranges would be
handy).

Now, here's a handy formula that you can use to help overcome the
possibility of someone changing the sheet names:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

What that does is return the name of the worksheet that the A1 (or any cell
on a sheet) reference is from. I wrote it using A1 on the same sheet with
the formula, but you can write it to any other sheet, as here, where I
reference a cell on a sheet named Links:
=RIGHT(CELL("filename",Links!A13),LEN(CELL("filena me",Links!A13))-FIND("]",CELL("filename",Links!A13)))
Initially the results will seem kind of dumb, it will show the existing
sheet name (oh, it won't show anything until after the file has been saved to
disk). But if anyone changes a sheet name, then the cell will reflect the
new sheet name.

What you can do with that is enter formulas on a separate worksheet in a
specific order for a cell on each of the other sheets in the workbook. Then
your updater can look on that sheet to find out what the current name of a
particular sheet is. You can set this sheet's property to xlVeryHidden in
the VBA editor and it won't be visible in the workbook, or even in the Format
-- Sheets --Unhide Sheet list.



"Stilltrader47" wrote:

Thank-you, here's why. I will be distributing copies of the original
(template) file to many users, who will be adding data to and creating
statistics in their own dedicated versions of the file. If I discover that
the text and hyperlink for a comment cell must be updated, I would like to be
able to provide just those cells to the users so they can apply as an update
(without eliminating their accumulated data).

Any other thoughts you have, or specific to using VBA code will be
appreciated

Thanks - Sony

"JLatham" wrote:

No doubt this is possible with VBA code, although it could be rather complex.
If you're going to go to the trouble of writing/editing a file to update
from, why not just do the job once directly in the working file?

"Stilltrader47" wrote:

I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony

JLatham - Thank you for the insightful response. My interest is to email the
update file. That should take care of the user opening it.

I do mean "names" in the context you presume (group cell references).

The user changing sheet names is a concern. I have to apply before I can be
comfortable with the explanation.

In the function, in what order do you reference the source information and
receive files. Can you write the function again using 2 filenames?

headersweblinks.xls = source info file

PG.xls = receive file

This will help me relate the syntax. But what I don't understand is if the
receive sheet name is "RAD", how does the function syntax communicate it,
referencing the example files above.

I envision emailing a source info file and the user will have a completely
different sheet name, which my file won't know...

Thanks Sony



"JLatham" wrote:

I'd probably write some code in the updating info file that would get the
user to open the file to be opened using Application.GetOpenFilename. That
lets me control the opening of the other file AND gives me the name of the
file that I need to change.
I'd probably set up the updating file itself with several columns of
information for each change to be made:
Column A = sheet name where the cell is to be found
Column B = address of the cell to be changed
Column C = new contents for the cell

I'm not quite sure of how you mean "names" in this context, I'm assuming you
mean a named range/cell. In that case you could place that into column B vs
the absolute cell address.

The code would simply go through your list from top to bottom and pick up
the necessary information and make the changes in the other workbook. You do
have to take into consideration that they may have renamed a sheet and/or
altered the layout on any given sheet (which is where named ranges would be
handy).

Now, here's a handy formula that you can use to help overcome the
possibility of someone changing the sheet names:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

What that does is return the name of the worksheet that the A1 (or any cell
on a sheet) reference is from. I wrote it using A1 on the same sheet with
the formula, but you can write it to any other sheet, as here, where I
reference a cell on a sheet named Links:
=RIGHT(CELL("filename",Links!A13),LEN(CELL("filena me",Links!A13))-FIND("]",CELL("filename",Links!A13)))
Initially the results will seem kind of dumb, it will show the existing
sheet name (oh, it won't show anything until after the file has been saved to
disk). But if anyone changes a sheet name, then the cell will reflect the
new sheet name.

What you can do with that is enter formulas on a separate worksheet in a
specific order for a cell on each of the other sheets in the workbook. Then
your updater can look on that sheet to find out what the current name of a
particular sheet is. You can set this sheet's property to xlVeryHidden in
the VBA editor and it won't be visible in the workbook, or even in the Format
-- Sheets --Unhide Sheet list.



"Stilltrader47" wrote:

Thank-you, here's why. I will be distributing copies of the original
(template) file to many users, who will be adding data to and creating
statistics in their own dedicated versions of the file. If I discover that
the text and hyperlink for a comment cell must be updated, I would like to be
able to provide just those cells to the users so they can apply as an update
(without eliminating their accumulated data).

Any other thoughts you have, or specific to using VBA code will be
appreciated

Thanks - Sony

"JLatham" wrote:

No doubt this is possible with VBA code, although it could be rather complex.
If you're going to go to the trouble of writing/editing a file to update
from, why not just do the job once directly in the working file?

"Stilltrader47" wrote:

I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony

Ok, first, look back at my previous post - that formula I gave there gives
you a method of tracking the current sheet names in the distributed workbook
on a hidden sheet within it. The code in the update file would first extract
the current sheet names from that hidden sheet. Since you created that
hidden sheet, you know the order of the sheet name list and so you know that
you want to change cells on a sheet that was originally named
MyContactsSheet, but the user has now named CustomerContacts.

In your code, you'd set up a reference to the other workbook by name. Since
you had them open it via code, once it's open you set a reference much like
this:

Dim otherWB As Workbook
Set otherWB = Workbooks(OtherWorkbookName)
where OtherWorkbookName contains its name such as "ABC Company Book.xls"

To get to a particular sheet within that workbook you could again set a
reference, as:
Dim whatSheet As Worksheet
Set whatSheet = otherWB.Worksheets(someSheetName)
where someSheetName holds one of the sheet names you picked up from that
hidden sheet in their workbook.
To initially get information from that hidden sheet your code might start
out something like
Set whatSheet = otherWB.Worksheets("HiddenInformationSheet")

Once you have a reference to a worksheet, you refer to cells on it using the
..Range property of a worksheet, like
someData = whatSheet.Range("A5")
to get the information from cell A5 on that sheet, or to place information
into it, you'd just kind of reverse it:
whatSheet.Range("A5") = someData

You might grab a copy of this (safe, I wrote it) introduction to VBA
programming that does touch on the topics of using Excel objects like
workbooks, worksheets, and ranges within VBA code.
http://www.jlathamsite.com/Teach/VBA...troduction.pdf


"Stilltrader47" wrote:

JLatham - Thank you for the insightful response. My interest is to email the
update file. That should take care of the user opening it.

I do mean "names" in the context you presume (group cell references).

The user changing sheet names is a concern. I have to apply before I can be
comfortable with the explanation.

In the function, in what order do you reference the source information and
receive files. Can you write the function again using 2 filenames?

headersweblinks.xls = source info file

PG.xls = receive file

This will help me relate the syntax. But what I don't understand is if the
receive sheet name is "RAD", how does the function syntax communicate it,
referencing the example files above.

I envision emailing a source info file and the user will have a completely
different sheet name, which my file won't know...

Thanks Sony



"JLatham" wrote:

I'd probably write some code in the updating info file that would get the
user to open the file to be opened using Application.GetOpenFilename. That
lets me control the opening of the other file AND gives me the name of the
file that I need to change.
I'd probably set up the updating file itself with several columns of
information for each change to be made:
Column A = sheet name where the cell is to be found
Column B = address of the cell to be changed
Column C = new contents for the cell

I'm not quite sure of how you mean "names" in this context, I'm assuming you
mean a named range/cell. In that case you could place that into column B vs
the absolute cell address.

The code would simply go through your list from top to bottom and pick up
the necessary information and make the changes in the other workbook. You do
have to take into consideration that they may have renamed a sheet and/or
altered the layout on any given sheet (which is where named ranges would be
handy).

Now, here's a handy formula that you can use to help overcome the
possibility of someone changing the sheet names:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))

What that does is return the name of the worksheet that the A1 (or any cell
on a sheet) reference is from. I wrote it using A1 on the same sheet with
the formula, but you can write it to any other sheet, as here, where I
reference a cell on a sheet named Links:
=RIGHT(CELL("filename",Links!A13),LEN(CELL("filena me",Links!A13))-FIND("]",CELL("filename",Links!A13)))
Initially the results will seem kind of dumb, it will show the existing
sheet name (oh, it won't show anything until after the file has been saved to
disk). But if anyone changes a sheet name, then the cell will reflect the
new sheet name.

What you can do with that is enter formulas on a separate worksheet in a
specific order for a cell on each of the other sheets in the workbook. Then
your updater can look on that sheet to find out what the current name of a
particular sheet is. You can set this sheet's property to xlVeryHidden in
the VBA editor and it won't be visible in the workbook, or even in the Format
-- Sheets --Unhide Sheet list.



"Stilltrader47" wrote:

Thank-you, here's why. I will be distributing copies of the original
(template) file to many users, who will be adding data to and creating
statistics in their own dedicated versions of the file. If I discover that
the text and hyperlink for a comment cell must be updated, I would like to be
able to provide just those cells to the users so they can apply as an update
(without eliminating their accumulated data).

Any other thoughts you have, or specific to using VBA code will be
appreciated

Thanks - Sony

"JLatham" wrote:

No doubt this is possible with VBA code, although it could be rather complex.
If you're going to go to the trouble of writing/editing a file to update
from, why not just do the job once directly in the working file?

"Stilltrader47" wrote:

I am building a complex workbook that is storing cumulative data and
calculations. Comment cells within the workbook contain hyperlinks to
various web sites. It is possible that the comment cells and hyperlinks may
have to change periodically to different web addresses and text. I'll call
this the working file.

Is it possible to import or merge a file to the working file that contains
updated comment cells and hyperlinks? This would allow specific cells within
the original file to update without overwriting the accumulated data and
changing calculated fields. My objective is to update only selected cells or
names within the original file. Thanks for your help on this.

Sony