There is no difference in speed regardless of whether the source file is opened or closed. However, I have noticed that manually creating links (for example, by manually copying and pasting formulas containing links into other cells) when opening the source file Time is much faster.
In my program, I have configured
Application.Calculation is manual instead of automatic.
I tried two ways Complete the link (please note that this is not the real code, but I want it to show what I mean):
1.
Use, copy the formula link from Range(“A1”) to an external source< br>Foreach loop of each Cell in a range:
Cell.FormulaR1C1 = Range(“A1”).FormulaR1C1
2.
Change the formula from Range(“A1 “) Copy to another range without using a foreach loop:
Range(“thisIsMyTargetRange”).FormulaR1C1=Range(“A1”).FormulaR1C1
These two methods are the same Slow, as I said, I have checked the above two methods and opened and closed the source file.
Is there any way to speed it up? Does anyone know enough about the link mechanism in Excel 2003 to provide suggestions on how to improve link performance?
In addition, if the range size is the same, use range copy instead of loop. For example
range(a1:a500).value = range(a1:a500)
Add an alternative method:
In addition, another method is to use the data import function to create a table, this function Refresh when the file is opened, if the form is what you are using. Then, if you want the bits of the table, use the data from the import table/table. This will keep the file updated or link only one big import when you open the file .
My Excel application creates links to other Excel files. It takes about 1 second to create a link to a cell, but I have hundreds of cells to Link, so it is slow.
No matter whether the source file is open or closed, the speed is no difference. However, I noticed that the link is created manually (for example, by manually copying and pasting the formula containing the link into other cells ) Is much faster when opening source files.
In my program, I have configured
Application.Calculation to be manual instead of automatic.
I Try to complete the link in two ways (please note that this is not the real code, but I want it to show what I mean):
1.
Use, link the formula from Range(“A1”) Copy to external source
Foreach loop of each Cell in a range:
Cell.FormulaR1C1 = Range(“A1”).FormulaR1C1
2.
The formula Copy from Range(“A1”) to another range without using foreach loop:
Range(“thisIsMyTargetRange”).FormulaR1C1=range(“A1”).FormulaR1C1
This Both methods are equally slow. As I said, I have checked the above two methods and opened and closed the source files.
Is there any way to speed it up? Does anyone know enough about the link mechanism in Excel 2003 to provide suggestions on how to improve link performance?
Are there formulas relevant? If you don’t use .value instead of .formular1c1.
In addition, if the range size is the same, use range copy instead of loop. For example
range(a1:a500).value = range(a1:a500)
Add an alternative method:
In addition, another method is to use the data import function to create a table, this function Refresh when the file is opened, if the form is what you are using. Then, if you want the bits of the table, use the data from the import table/table. This will keep the file updated or link only one big import when you open the file .