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.
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.
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.
Code:
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 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.