Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation Error on Sheet Copy (Excel 2k (SP2))

    Hi all,
    I've got this complicated VBA application which does many things one of which is a custom 'import/export' feature. The user 'exports' a set of worksheets (this is a fixed set, not user-defineable) to some named workbook. On importing (loading) these worksheets, I have code which replaces the same worksheets in the open workbook (the destination of the import). I get automation errors:

    <pre> Application.Workbooks(thisWB).Worksheets(Array("Se rvices", "Design", _
    "Adv. Model", "h.Model")).Delete

    With WB ' the worksheet source workbook (the one being imported)
    Worksheets("Services").Copy After:=Workbooks(thisWB).Sheets(9) ' <- error
    Worksheets("Design").Copy After:=Workbooks(thisWB).Sheets(10)
    Worksheets("Adv. Model").Copy After:=Workbooks(thisWB).Sheets(18)
    Worksheets("h.Model").Copy After:=Workbooks(thisWB).Sheets(33)
    End With
    </pre>


    As you can see, the export function saves four worksheets and the import then copies those same four into the current workbook. Before I can insert these new worksheets I have to delete the sheets of the same name (since I don't want two copies, e.g., Services and Services(2)).

    On the line that copies the Services sheet I get :

    "Run-time error '-2147417848 (80010108)' Automation Error. The object invoked has disconnected from it clients."

    Excel then crashes. <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46>

    Secondary question... Is there a list of all the err.number and err.descriptions?

    Any clues as to what's killing Excel here?
    Deb

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Error on Sheet Copy (Excel 2k (SP2))

    It sounds like WB is no longer an object (at least it's not a valid Workbook).

    By any chance did you set WB=nothing somewhere before this code?

    Or maybe you dimmed and set WB in another sub (an it's not public)?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation Error on Sheet Copy (Excel 2k (SP2))

    I agree with Kevin. Single-step through your code, Highlight WB, and use the Debug | Add Watch menu to make sure that WB exists. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Automation Error on Sheet Copy (Excel 2k (SP2))

    Deb,

    If the code you are ruinning is contained the Importing workbook, try replacing

    Copy After:=Workbooks(thisWB).Sheets(9)

    with
    Copy After:=ThisWorkbook.Sheets(9)

    or maybe just

    Copy After:=Sheets(9)

    if it is also othe active workbook.

    Andrew C

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Error on Sheet Copy (Excel 2k (SP2))

    Actually it's the other way around. The workbook containing that code is the one to be imported INTO (hense the var ThisWB is the destination workbook which is already open (since it's running). I then open the workbook containing the four sheets to be copied (that's the var WB). Here's the code:
    <pre>Public Sub RestoreCase(fName As String)
    Dim WB As Workbook, thisWB As String

    Application.ScreenUpdating = False
    Application.StatusBar = "Updating model with selected business case..."
    thisWB = ThisWorkbook.Name

    Set WB = Workbooks.Open(fName, updatelinks:=1, ReadOnly:=True, password:=pTEMPLATE)

    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Application.Workbooks(thisWB).Unprotect pMONIE
    Application.Workbooks(thisWB).Worksheets("h.Model" ).Visible = True
    Application.Workbooks(thisWB).Worksheets(Array("Se rvices", "Design", "Adv. Model", "h.Model")).Delete

    With WB
    .Worksheets("Services").Copy After:=Workbooks(thisWB).Sheets(9)
    .Worksheets("Design").Copy After:=Workbooks(thisWB).Sheets(10)
    .Worksheets("Adv. Model").Copy After:=Workbooks(thisWB).Sheets(18)
    .Worksheets("h.Model").Copy After:=Workbooks(thisWB).Sheets(33)
    .Close
    End With

    Workbooks(thisWB).Worksheets("h.Model").Visible = xlSheetVeryHidden

    Call RepairNames ' fix #REF in named vars from imported sheets

    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = "Recalculating Model..."
    Application.Calculate
    Application.Workbooks(thisWB).Protect pMONIE

    Application.ScreenUpdating = True
    Call ActionDone

    End Sub</pre>


    The RepairNames is something I wrote to fix the #REF in the named vars which were mangled when I deleted the four worksheets. Any names that referenced cells on any of the four worksheets were lost and replaced with #REF. I go through the names and replace "#REF" with the correct worksheet name. That works fine.

    I'll try not explicitly stating its position in the new work (no After: argument). <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Thnx, Deb

Posting Permissions

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