W
Will
I've created a piece of VBA code to refresh some data tables and then
do a copy-paste special on the result... reason being is there are 16
data tables in the workbook and it runs too slowly if they are
"active".
The code I've written works the first time it is run (i.e. for one data
table), however the second time I run the code I get the following
message:
Runtime error '-2147417848 (80010108)' Automation error The object
invoked has disconnected from its clients.
The code is below, the row that is creating the error is highlighted
with ***
Sub DataTableSetup(DT)
'This macro calculates re-calculates either select or all data tables
in the model. Individual data
'tables are selected by passing the number of the data table to the
function. All data tables are selected by passing
' "ALL" to the macro. The macro paste values of data table
calculations. This is to prevent the
' model from becoming unacceptably slow when data tables exist
Dim origsheet As String
Dim origrow As Integer
Dim origcol As Integer
Dim DTRName As String ' Data table area name
Dim DTIName As String ' Data table input name
Dim DTCPName As String ' Data table copy / paste area
Dim DTHCName As String ' Data table hardcode area
Dim calcstate As String
Dim i As Integer
' Marks the orginial excel location to allow orginal location to be
returned at the end of the macro
origsheet = ActiveSheet.Name
origrow = ActiveCell.Row
origcol = ActiveCell.Column
' Sets calculation state to manual to speed up macro
calcstate = Application.Calculation
Application.Calculation = xlCalculationManual
'Test to see if 1 or all data tables require updating
If DT = "All" Then
'Calculates all data tables
'Sheets("iMacros").Select 'selects macro sheet
'For i = 1 To Range("charge_sens_data").Rows.Count 'loops over
all data tables
'This part is not yet complete
'Next i
Else
'Calculates a select data table
i = DT
Sheets("iMacros").Select 'selects macro sheet
'Retrieves required variables
DTRName = ActiveSheet.Range("DataTableAreas").Cells(i, 2).Value
DTCPName = ActiveSheet.Range("DataTableAreas").Cells(i, 3).Value
DTIName = ActiveSheet.Range("DataTableAreas").Cells(i, 4).Value
DTHCName = ActiveSheet.Range("DataTableAreas").Cells(i, 5).Value
'Selects sensitivites sheet, sets up data table and calculates
value
Sheets("oSensitivities").Select
ActiveSheet.Range(DTRName).Select
Selection.Table RowInput:=Range(DTIName) ***** PROBLEM CODE
******
Application.Calculate
ActiveSheet.Range(DTCPName).Copy
ActiveSheet.Range(DTCPName).Select
'Pastes output to iMacroSheet
ActiveSheet.Range(DTRName).Copy
'Sheets("iMacros").Select
ActiveSheet.Range(DTHCName).Select
End If
Sheets(origsheet).Select
Cells(origrow, origcol).Select
Application.Calculation = calcstate
Application.ScreenUpdating = False
End Sub
Any help would be much appreciated!
Cheers,
Will
do a copy-paste special on the result... reason being is there are 16
data tables in the workbook and it runs too slowly if they are
"active".
The code I've written works the first time it is run (i.e. for one data
table), however the second time I run the code I get the following
message:
Runtime error '-2147417848 (80010108)' Automation error The object
invoked has disconnected from its clients.
The code is below, the row that is creating the error is highlighted
with ***
Sub DataTableSetup(DT)
'This macro calculates re-calculates either select or all data tables
in the model. Individual data
'tables are selected by passing the number of the data table to the
function. All data tables are selected by passing
' "ALL" to the macro. The macro paste values of data table
calculations. This is to prevent the
' model from becoming unacceptably slow when data tables exist
Dim origsheet As String
Dim origrow As Integer
Dim origcol As Integer
Dim DTRName As String ' Data table area name
Dim DTIName As String ' Data table input name
Dim DTCPName As String ' Data table copy / paste area
Dim DTHCName As String ' Data table hardcode area
Dim calcstate As String
Dim i As Integer
' Marks the orginial excel location to allow orginal location to be
returned at the end of the macro
origsheet = ActiveSheet.Name
origrow = ActiveCell.Row
origcol = ActiveCell.Column
' Sets calculation state to manual to speed up macro
calcstate = Application.Calculation
Application.Calculation = xlCalculationManual
'Test to see if 1 or all data tables require updating
If DT = "All" Then
'Calculates all data tables
'Sheets("iMacros").Select 'selects macro sheet
'For i = 1 To Range("charge_sens_data").Rows.Count 'loops over
all data tables
'This part is not yet complete
'Next i
Else
'Calculates a select data table
i = DT
Sheets("iMacros").Select 'selects macro sheet
'Retrieves required variables
DTRName = ActiveSheet.Range("DataTableAreas").Cells(i, 2).Value
DTCPName = ActiveSheet.Range("DataTableAreas").Cells(i, 3).Value
DTIName = ActiveSheet.Range("DataTableAreas").Cells(i, 4).Value
DTHCName = ActiveSheet.Range("DataTableAreas").Cells(i, 5).Value
'Selects sensitivites sheet, sets up data table and calculates
value
Sheets("oSensitivities").Select
ActiveSheet.Range(DTRName).Select
Selection.Table RowInput:=Range(DTIName) ***** PROBLEM CODE
******
Application.Calculate
ActiveSheet.Range(DTCPName).Copy
ActiveSheet.Range(DTCPName).Select
'Pastes output to iMacroSheet
ActiveSheet.Range(DTRName).Copy
'Sheets("iMacros").Select
ActiveSheet.Range(DTHCName).Select
End If
Sheets(origsheet).Select
Cells(origrow, origcol).Select
Application.Calculation = calcstate
Application.ScreenUpdating = False
End Sub
Any help would be much appreciated!
Cheers,
Will