Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation: delete columns is failing (97)

    Arghhhh... this is driving me crazy. Not sure whether to post this under Excel or Access.

    Some VBA code from Access 97 controlling Excel 97 was working, and now it stoppped and I can't figure out why. The VBA code determines which columns of the spreadsheet to delete, stores the results in a variable named sRows (sorry...), and calls the Delete method. The error message is code #1004 - "Method columns of object '_Application' failed.

    What is really frustrating is that if I delete any random column manually from Excel while in debug mode, then the code works just fine. I've tried rebooting the computer to no avail.

    Here are the relevant sections of code in the Access form:


    Private Sub ModifyLODMxl(objXL As Excel.Application, rs As DAO.Recordset, sWkbk As String)
    Dim sRows As String
    Dim iRow As Integer
    Dim nTank as Integer

    With objXL
    [lines snipped...]
    ' this section mentions ROWS, but it is really COLUMNS
    .Worksheets("Dip History").Activate
    i = Int((nTank * 3 + 2) / 26)
    If i = 0 Then
    sRows = ""
    Else
    sRows = Chr(64 + i)
    End If
    i = (nTank * 3 + 2) - (i * 26) + 1
    sRows = sRows & Chr(64 + i) & ":CL"

    ' at this point, sRows has the value "R:CL"
    .Columns(sRows).Delete Shift:=xlToLeft ' <<<< error 1004 occurrs here


    If I go into the Access Immediate window, and type, objXL.Columns("DD").Delete then I get the same error message. Switch into Excel and delete a random column manually, then back to Access and reissue the command from the Immediate window, and it works fine.

    Can anybody see what I am overlooking or suggest a workaround.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation: delete columns is failing (97)

    Does this work?

    .Worksheets("Dip History").Columns(sRows).Delete Shift:=xlToLeft

    instead of just

    .Columns(sRows).Delete Shift:=xlToLeft

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation: delete columns is failing (97)

    No it does not. Now I get Error 13 - type Mismatch.

    finally found the answer -- and it was NOT in the code. Something was corrupted in the Excel file. Deleted a combo box from the Excel worksheet (unrelated to the deletion columns), and it worked fine. Rebuilt the combo, plus another one just like it. Worked fine. Dragged a chart about 2 cm across the worksheet . Failed.!!

    Rebuilt the combos while the graph is properly positioned on the worksheet. Works fine. Seems pretty fragile!!!

    Thanks for your input.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  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: Automation: delete columns is failing (97)

    When you are running the code and have problems: is one of the OBJECTS selected? XL97 has problems doing some things when OBJECTS are selected. YOu must have the object NOT get the focus. SInce many of the objects do NOT have the option to NOT take the focus, an easiy fix is to add the line:
    <pre>Activecell.select</pre>

    near the beginning of the sub to select a cell rather than the object. The hit or miss aspect you speak of seems to be indicative of this problem since when you are debugging you end up removing the focus from the object!

    Steve

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

    Re: Automation: delete columns is failing (97)

    The "Move and size with cells" options of the objects you mention can have effect on the behaviour when deleting rows. When done manually, you could get a message like "Cannot shift objects off sheet". Dunno what gives when done through code using Access.
    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
  •