Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object disconnected from client?!? (Excel 2000)

    The object invoked has disconnected from its clients?!?.
    What seems like a simple process refuses to work. The macro fails with the above error message at the Subtotal method.
    When I step through the code it works fine, and when I run it as a separate macro it runs fine.
    The attached file, TestForSubT.xls contains the code and shows what the data looks like before it

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    Please reattach the file to your message above. Perhaps due to bugs in the restored Lounge, the file doesn't appear to be accessible. Without seeing the code, I would guess that you need to code fully resolved references to all of the objects used in the code, from the Workbook on down the object hierchy, such as:

    With ThisWorkbook.Worksheets("Sheet1")
    Range("mySubtotals").Subtotal ...
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    John,
    I think I tried what you suggested, but the same error occurred.
    Ross

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    Which macro in your workbook and which line?

    I can't step thru it, since I son't have the files and connections it is supposed to open and connect to so can't test it. could you eliminate this "excess" code and have the spreadsheet "filled" with whatever test data you want and then be more specific about the problem statement. The spreadsheet (as attached) does not have anything I would think about subtotaling.

    Steve

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    Steve,
    Sorry, I added data to the original attachment and forgot to include it this time.
    The attached file is the product of the macro up to the point of sub-totalling. It works beyond that point if you step through it, and if you run the "subt" macro separately. It just won''t run all at once. The run-time error is "Automation error The object invoked has disconnected from its clients."
    Ross

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    I found this article, it has to do with "page breaks" so I am not sure it is related, but there is a downloadable "fix" if it is related.

    Steve

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    Steve,
    The KB article seems to address the issue. The error message is certainly bang on. But I am not asking the sub-total method to add page breaks.
    Nevertheless, I downloaded the hotfix from MS. It did not work and I have a call in to MS to help me double-check whether it is indeed the correct patch and if I installed it properly. I'll keep you posted on this, if you wish.
    Ross

  8. #8
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    John,
    Thanks for your effort. Believe it or not, MS solved the problem for me. The clue here is that the macro works fine when you step through it. This suggests a "timing issue". All I had to do was to introduce a 1 second pause just prior to the offending line of code, and the macro finishes off fine.
    With respect to the code you used to convert the text to numbers, I used paste special because I thought the loop might be slower. I will try your method.
    Thanks again to you and Steve.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    There are other possible reasons for the message, but I don't know ADO, so perhaps another Lounger can help. The tail end of your primary macro runs for me if I comment out everything through "Thisworkbook.Names("ExternalData_1").Delete". I rewrote some bits, and you'll need an additional Dim. I also recommend that you change all instances of 'ActiveWorkbook' to 'ThisWorkbook'. See if this helps:

    Dim rCell As Range

    ' code continues from here ...

    Thisworkbook.Names("ExternalData_1").Delete
    With xlSheet
    'Convert text to numbers for sort
    For Each rCell In Range(.Range("B2"), .Range("B2").End(xlDown))
    rCell.Value = rCell.Value2
    Next rCell
    'Sort & subtotal by tax office
    .Range("B2").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Range("A2").Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    .Outline.ShowLevels RowLevels:=2
    .Range("A1").Select
    End With
    'Windows("MtgsForTaxOffices.xls").Close savechanges:=False
    Set xlSheet = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Object disconnected from client?!? (Excel 2000)

    <hr>This suggests a "timing issue".<hr>
    Another method that sometimes solves trouble like this is to set the part of the macro that needs to be continued in another sub and start that sub using the ontime method. This ensures the call stack is cleared and sometimes resolves timing issues. Another possible way could have ben to use DoEvents just after a time consuming step (or one that uses systems outside Excel).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •