Automation: delete columns is failing (97)

Topic

New Reply

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.

Reply | Quote

Replies

Does this work?

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

instead of just

.Columns(sRows).Delete Shift:=xlToLeft

Reply | Quote

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.

Reply | Quote

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:

Activecell.select

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

Reply | Quote

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:

Activecell.select

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Does this work?

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

instead of just

.Columns(sRows).Delete Shift:=xlToLeft

Reply | Quote