Updating Excel Links

[inahs2k]

Hi everybody,

I need some help regarding a problem that I have. I have a Excel workbook that has some cells of a sheet linked to some other cells of another sheet (both sheets belong to the same workbook). Now, to begin with, the source sheet (for the links) does not exist (so Excel gives me #REF error which I can live with). The user then runs some macros after putting in some input requirements of the macros. Once the macros have run, then the source sheet is created. So we now have both the links and the source for the links. But when I try to update the links, now that I have the source for the links to get the data from, Excel does not look at the current work book to see if that particular worksheet exists. It always tries to open up another workbook with that name.

Is there anyway that I can force Excel to look at the current workbook first before I have to specify another workbook to look in, to update the links?? I hope I have been clear enough with my question, but if not, please let me know and I will try to rephrase the question.

Thanks in advance and have a great day.
Shahnaz.

 

[DaleWatson123321]

Shahnaz,

How about trying this approach...

Instead of creating your link formulas in advance, create them AFTER the new worksheet is created.

By creating range names for each of the cells where a link formula needs to be placed (see reference to the range names "lnk_1", _2 _3 below), and also range names for each of the source cells (see the formulas below that reference range names "inp_1", _2, _3) it will make your task MUCH easier.

If the link formulas are a simple reference to the source cells, then the following is an example of what will work...

Sub Set_Links()
[lnk_1].Formula = "=inp_1"
[lnk_2].Formula = "=inp_2"
[lnk_3].Formula = "=inp_3"
End Sub

Naturally for this to work, you'll need to create the range names for the "lnk_n" cells, and you can do this in advance.

For the range names of the source cells, you'll naturally need to create the range names AFTER the new worksheet is created. You would do this with code such as...

Activecell.Name = "inp_1", OR
Range("C4&quot.Name = "inp_1"

I hope this approach can work. Please advise as to how you make out.

Another option might be to create the "new" worksheet in advance, but keep it HIDDEN until after the input requirements have been provided ???

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[inahs2k]

Hi Dale,

Thank you for your response. Your suggestion (about named ranges) would have been great except for the fact that there are so many cells that I would have to name and set the links to, it would practically be difficult. And about creating the sheet beforehand and keeping it hidden, the problem is that the sheet depends on the user input. So based on what the user chooses the sheet will be different, but the links will still be valid.

One more thing, when I go through the normal Excel change links process (Edit->Links->Change) Excel tries to open up a workbook with the same name as my source worksheet and in the process it gives me an file open dialog box. So if I browse around and choose the same file that I am working on, the links are updated automatically. So I tried recording the macro for that process and calling it in my VBA code, but that doesnt work. It beats me. So I dont know what to do. If you have any other suggestions, please let me know and I will try them out.

Once again, thanks and have a great day.
Shahnaz.

 

[terzaghi]

Hi

try another approach.

You have the sheet and the links already created and working. The fact is that the source sheet is empty. With your macro you creat a "gost" sheet. After the creation with the data you copy-Paste the gost link over the correct sheet (to whitch the links refers to) and delete the "gost" sheet.

Hope this helps

Andrea

(-:

 

[inahs2k]

Hi Andrea,

Thanks for you suggestion. Now it seems to be working. I created an empty sheet with the same name as my source sheet (so the links refer to an existing sheet and as a result the cells contain the default values) and then based on the user inputs, I am just copying the generated sheet over the already existing blank source sheet, so the links now refer to the correct values.

This seems to be working for now. I hope I dont run into some other issues because of that. But anyway, thanks for your suggestion.

Have a nice weekend.
Shahnaz.

 

[DaleWatson123321]

Hi Shahnaz,

I was busy at a meeting, so am just now able to respond.

Here's some info that might cause you to believe that creating the MANY range names might be a reasonable alternative after all.

I appreciate that creating many range names can be a "chore", but I'd like to suggest that the process can be "sped up". If you do it by using VBA, you could copy several lines, and make changes to the names on each line.

In a test I just wrote, based on the following example - but where there were 100 names per sheet (a total of 300 range names), the length of time it took was: UNDER 1 SECOND.

Sub Create_Names()
Application.ScreenUpdating = False
Names_Sheet1
Names_Sheet2
Names_Sheet3
Application.ScreenUpdating = True
End Sub

Sub Names_Sheet1()
Worksheets("Sheet1&quot.Range("C4&quot.Name = "sh1_1"
Worksheets("Sheet1&quot.Range("C5&quot.Name = "sh1_2"
Worksheets("Sheet1&quot.Range("D10&quot.Name = "sh1_3"
End Sub

Sub Names_Sheet2()
Worksheets("Sheet2&quot.Range("C4&quot.Name = "sh2_1"
Worksheets("Sheet2&quot.Range("C5&quot.Name = "sh2_2"
Worksheets("Sheet2&quot.Range("D10&quot.Name = "sh2_3"
End Sub

Sub Names_Sheet3()
Worksheets("Sheet3&quot.Range("C4&quot.Name = "sh3_1"
Worksheets("Sheet3&quot.Range("C5&quot.Name = "sh3_2"
Worksheets("Sheet3&quot.Range("D10&quot.Name = "sh3_3"
End Sub

==============================
Appreciate that creating the above range names is a "one-time-only" task. Once you've created the range names for these cells (that your code will later be filling with a link formula), you won't need to create them again.

An "ideal attraction" of using range names, is that any future changes you make such as inserting/deleting rows/columns, or moving data, will cause the named cells to adjust "automatically".

The only changes required will be IF and WHEN you want to add ADDITIONAL link-formulas. You would then manually create a range name for each cell where a NEW link formula is to reside, and modify your code that creates your user-input sheet, to include code for the creation of a range name for the input cell (to which the link-formula will reference).

The next two routines are examples of code to merge within your existing code after it creates your user-input worksheet.

Sub Set_InputNames()
Range("C4&quot.Name = "inp_1"
Range("D4&quot.Name = "inp_2"
Range("J4&quot.Name = "inp_3"
End Sub

Sub Set_Links()
[inp_1].Formula = "=sh1_1"
[inp_2].Formula = "=sh1_2"
[inp_3].Formula = "=sh1_3"
End Sub

(Note that when you use range names in VBA ...e.g. [inp_1] above, you don't have to use the Sheet name - and your active sheet can be anywhere within the workbook)

Here's another "tip" regarding another "easy and fast" method of creating range names...

If you already have labels that identify data, or if you don't but choose to enter labels next to the cells where you want to create multiple names, you can do so ALL-AT-ONCE.

First, appreciate that where you want to create names for a range of contiguous cells, you could enter the labels by using Excel's auto-fill. For example enter: inp_1 in the first cell, and inp_2 in the second cell. Then highlight both cells and click-and-drag the bottom-right-corner of the second cell - and drag down for as many labels as you require. Of course the same could be done horizontally on one row.

To create the range names for your block of labels "all-at-once", use these steps:

1) Highlight the labels and the column to the right of the labels.

2) Use the menu: Insert - Name - Create

3) In the "Create Names" window, un-check any options that don't apply, and check-off "Left column". (Or if you've entered labels to the right of the cells where you want the names created, then check off "Right column".)

4) Click "OK", and the names are created.

An "added benefit" of using this method, is that IF your list of labels happens to contain a name you created previously (in a different location), then Excel will present a window with the question: "Replace existing definition of..." ?, with Yes/No/Cancel buttons.

If want to assign range names where the names are "dispersed", but you have labels on the left side of the cells, you can use this method...

a) Highlight the cell that you want to name - i.e. the cell to the right of the cell containing the label.

b) Hold down <Control> and hit <F3> .

c) At this point, you'll notice the name is automatically &quot;picked up&quot; so you don't have to type the name. Simply hit <Enter>.

I hope this info helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca