Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data table automation problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Table Automation | Excel Discussion (Misc queries) | |||
C# Automation problem | Excel Programming | |||
C++ automation problem | Excel Programming | |||
problem with pivot table automation using perl | Excel Programming | |||
importing Word table data into Excel - solutions for automation | Excel Programming |