Rx_
Nothing In Moderation
- Local time
- Yesterday, 18:52
- Joined
- Oct 22, 2009
- Messages
- 2,803
In the past, my code demonstrated using Excel object code from MS Access for remote automation. The recommendation is to turn off (xlManual) the calculation when dumping big amounts of data into an Excel worksheet then using vba to insert custom Formulas in cells to work with the data.
Instead, turn off the automatic, get the worksheet built out, then turn calculation back on at the end.
Concern: Noted that some file locations where a workbook can be saved can prevent this setting in Excel. It might be that a workbook has a link to another workbook at a secured location.
It is good practice to add Error Traps (e.g. On Error Goto Err_Trap) to procedures. Given the nature of an Application.Caculation
How should this trap be built?
For the time being, my process locates it at the very end of a procedure and switches to the On Error Resume Next.
On Error Resume Next ' This can error if Excel is in a non-Trusted file location
ObjXL.Application.Calculation = xlAutomatic ' or option xlManual '
' Destroy object variables
' Then Exit Sub or Function
Instead, turn off the automatic, get the worksheet built out, then turn calculation back on at the end.
Concern: Noted that some file locations where a workbook can be saved can prevent this setting in Excel. It might be that a workbook has a link to another workbook at a secured location.
It is good practice to add Error Traps (e.g. On Error Goto Err_Trap) to procedures. Given the nature of an Application.Caculation
How should this trap be built?
For the time being, my process locates it at the very end of a procedure and switches to the On Error Resume Next.
On Error Resume Next ' This can error if Excel is in a non-Trusted file location
ObjXL.Application.Calculation = xlAutomatic ' or option xlManual '
' Destroy object variables
' Then Exit Sub or Function
