xlCalculationManual does not stop linked formulas from calculating?

[Clark23]

So I am working on a bit of a stop-gap solution until a new set of workbooks/database are created.

User locates the workbook they need to link to and is prompted with a list of sheets in the workbook. The file path and sheet name are stored in two cells of my current workbook. A button triggers a macro to create 16 formulas for each row. Probably not more than 100-200 rows, but I'm having issues with 20-30 rows right now.

There are only a total of 10 possible workbooks to link to. Since INDIRECT doesn't work with linked workbooks I had to write VBA that goes through each line and creates the links. There are 10-15 people who will use distinct copies of the sheet and the links will need to be updated on a weekly basis. It's not a one time shot or I would just manually create the links myself.

Even with Calculations, EnableEvents, and ScreenUpdating turned off, Excel still checks each link. Is there a way to have Excel not calculate the links until my procedure has completed. I know it will take a minute or two to pull all of the links in regardless, but this is acceptable. The twenty minutes it's taking now is not acceptable.

I actually forgot to copy the file to a flash drive before I left Friday, and I don't have VPN; I'm rewriting the gist of the code below.

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 to last_row
Cells(i,5).Formula = "distinct linked formula here"
Cells(i,6).Formula = "another distinct linked formula here"
Cells(i,7).Formula = "etc"
Cells(i,8).Formula = "etc"
' and so on....
Next

Application.Calculation = xlAutomaticCalculation
Application.ScreenUpdating = True
Application.EnableEvents = True

I normally don't disable events but I thought it might help. It didn't seem to have any effect though. Is there a way to write the formulas to an array and that set the range equal to that array? I've used this solution for big chunks of data in the past.

I also tried to preface the formula with an apostrophe and then use replace to change it to a formula but Excel didn't fall for that one. AFAIK using Replace calculates the cells one by one anyway, regardless of the manual calculation setting.

 

[SkipVought]

1. Is the data being linked in a TABLE in the remote workbook?

2. If so, is there a need for a LINK, as opposed to the DATA that is in a cell?

It might be quicker to QUERY the remote workbooks, grabbing some or all the data, and THEN populating these cells.

faq68-5829

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Clark23]

Thanks for your quick reply. Unfortunately the data is not setup in tables. I wish it was, but I did not design the linked sheets. I will definitely keep your suggested method in mind for future projects though, because I didn't know you could do it that way.

I can run a loop to open the workbooks and pull in the data from each workbook, but I was trying to avoid this. If the user forgets to update the data after making a change to the linked workbook, the sheets are no longer accurate. I suppose I could check the last modified date of each workbook upon opening and prompt the user to update.

When I just drag down a row of these formulas, they calculate fairly quickly. It is just a matter of forcing Excel to not resolve each link as I'm writing to the cells. So, I'm still hoping for some solution to my original problem but come Monday I might have to open them in the background and pull in the data manually.

 

[SkipVought]

" It is just a matter of forcing Excel to not resolve each link as I'm writing to the cells. "

Is it not a pay me now or pay me later? Either way, if it were possible, the link must be resolved and calculated. I don't see where there would be a time savings. Maybe I am missing something here.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Clark23]

The formulas with links seem to calculate considerably faster if I were to say, drag a row of formulas down and repeat them 100 times. There is a bit of lag (less than a minute) but I thought Excel might optimize moving the data from another workbook if it "knows" it has 100 links to resolve in the same workbook, rather than doing it one cell at a time?

 

[SkipVought]

Have you tried using Edit > Replace to assign the workbook link?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]