The object invoked has disconnected from its clien

Ciarano · March 12th, 2004, 10:32 AM

Hi guys

I receive an Automation Error - The object invoked has disconnected from its clients when trying to create a spreadsheet and populate its details with information from a Oracle database.
The button is on a form - here is the code

Private Sub SpreadsheetButton_Click()

Set Dimension = ExpressNames.Item(SelectedNode.Tag)

If Dimension.Structure = "RELATION" Then Set Dimension = Dimension.Dimensions(1)

If SelectedNode.Parent Is Nothing Then
   Set Values = Dimension.Limit("TO ALL") 'At the top of the tree
   Extra = ""
Else

   Set Values = Dimension.Limit("TO " & SelectedNode.Parent.Tag & " ''" & SelectedNode.Parent.Text & "''")
   Extra = " for " & ExpressNames.Item(SelectedNode.Parent.Tag).Title & " " & SelectedNode.Parent.Text
End If

Application.ScreenUpdating = False

ThisWorkbook.Sheets("DimensionTemplate").Copy

'Need to reference the ExpressProject macro file to new workbook to work correctly
ActiveWorkbook.VBProject.References.AddFromFile (ThisWorkbook.FullName)

Windows(1).Caption = Dimension.Title
Windows(1).DisplayHeadings = False

If Dimension.Names.Count > 3 Then
   Sheets(1).Range("C5").Resize(, Dimension.Names.Count - 3).EntireColumn.Insert
End If
Sheets(1).SaveChanges = False

VarCount = 0
For Each Variable In Dimension.Names
   VarCount = VarCount + 1

   Sheets(1).Range("A1").Offset(1, VarCount - 1) = Variable.Title
   Sheets(1).Range("A1").Offset(2, VarCount - 1) = Variable.ExpressName
   Sheets(1).Columns(VarCount).ColumnWidth = IIf(Variable.Width > 7, Variable.Width, 7)

   Sheets(1).Range("A1").Offset(3, VarCount - 1).Resize(2).Numberformat = Variable.DataType.Numberformat
' Sheets(1).Range("A1").Offset(1, VarCount - 1).Resize(4).HorizontalAlignment = Variable.DataType.HorizontalAlignment
Next Variable

If Values.Count > 2 Then
   Sheets(1).Range("C5").Resize(Values.Count - 2).EntireRow.Insert
End If

VarCount = 0

ProgressBar.Max = Dimension.Names.Count
ProgressBar.Min = 0

For Each Variable In Dimension.Names
   VarCount = VarCount + 1
   ProgressBar.Value = VarCount

   VariableValues = ExpressArray(Variable.Database, "FETCH " & Variable.FetchString, 2)

   For ValueCount = 1 To UBound(VariableValues, 1)
      Sheets(1).Range("A1").Offset(ValueCount + 2, VarCount - 1) = VariableValues(ValueCount, 1)
   Next ValueCount

   With Sheets(1).Range("A3").Offset(, VarCount - 1).Resize(UBound(VariableValues, 1), 1).Validation

      .Delete
      .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="True"
      .InputTitle = Variable.Title
      .InputMessage = "Enter " & Variable.LD
      .ShowInput = True

      If Variable.Structure = "RELATION" Then
         If Variable.RelatedDimension.Limit("TO ALL").Count < 60 Then
            'Create the popup list of values if not too many
            ListofValues = ""
            For Each Value In Variable.RelatedDimension.Limit("TO ALL")
               ListofValues = ListofValues & "," & Value
            Next Value

            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListofValues
            .IgnoreBlank = True
            .InCellDropdown = True
            .ErrorTitle = "Invalid " & Variable.Title
            .ErrorMessage = "You have entered an invalid " & Variable.Title
            .ShowError = True
            .InputTitle = Variable.Title
            .InputMessage = "Enter " & Variable.LD
            .ShowInput = True
         End If
      End If
   End With
Next Variable

Call ReportScreen.SetupHeaders(Sheets(1), "C000", Dimension.Title & " Printout")
Sheets(1).SaveChanges = True
Application.ScreenUpdating = True
ProgressBar.Value = 0
End Sub

I receive the error in this line of code
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListofValues

Has anyone run into this type of error before?

Many thanks for any help

Cheers

Ciaran

joefawcett · March 12th, 2004, 11:18 AM

Perhaps it's because you delete the validation first, shouldn't you be using the modify method?
As a side issue it's hard to debug code like this. Event handlers should only contain code that is specific to the control they are in or the form the control is on. All other stuff should be in modules.

--

Joe

Ciarano · March 12th, 2004, 11:38 AM

Hi Joe

I presume you mean .delete should be .modify?
Would you have any links to explain the modify method further?
I can't do much about the structure of the code, this code is part of a VB front-end that is being used for an existing application. I am testing a migration from Oracle Express Server to Oracle9i using OLAP.

Thanks again

Ciaran