Microsoft Excel - Compile error- argument not optional when controlling Word from Excel

Asked By John Wirth on 15-Mar-13 01:37 PM
I am using the following code in word and it works as it should:
Sub DeleteEmptyTablerowsandcolumns()
Dim Tbl As Table
With ActiveDocument
For Each Tbl In .Tables
If Tbl.Cell(1, 2).Range.Text = Chr(13) & Chr(7) Then
Tbl.Select
 Selection.MoveLeft Unit:=wdCharacter, Count:=3
    Dim myRange As Range
    Set myRange = Selection.Range
    myRange.End = ActiveDocument.Range.End
    myRange.Delete
Exit Sub
End If
Next Tbl
End With
End Sub
I need to use it in Excel, but it breaks at myRange.End = WdApp.ActiveDocument.Range.End with a compile error "argument not optional"

Dim WdApp As Word.Application, wdDoc As Word.Document
Set WdApp = Word.Application
Dim Tbl As Table
With WdApp.ActiveDocument
For Each Tbl In .Tables
If Tbl.Cell(1, 2).Range.Text = Chr(13) & Chr(7) Then
Tbl.Select
 Selection.MoveLeft Unit:=wdCharacter, Count:=3
    Dim myRange As Range
    Set myRange = Selection.Range
    myRange.End = WdApp.ActiveDocument.Range.End
    myRange.Delete
Exit Sub

End If
Next Tbl
End With




Danasegarane Arunachalam replied to John Wirth on 16-Mar-13 08:44 PM
When you use the code

Dim myRange As Range

in word winword vba it refers to word Range explicitly. As you have moved the code to excel it is taking that as excel range.

Tell the compiler that it is word range

 Dim myRange As Word.Range

Try this part of code

Dim WdApp As Word.Application, wdDoc As Word.Document
Set WdApp = Word.Application
Dim Tbl As Table
With WdApp.ActiveDocument
For Each Tbl In .Tables
If Tbl.Cell(1, 2).Range.Text = Chr(13) & Chr(7) Then
Tbl.Select
 Selection.MoveLeft Unit:=wdCharacter, Count:=3
  Dim myRange As Word.Range
  Set myRange = Selection.Range
  myRange.End = WdApp.ActiveDocument.Range.End
  myRange.Delete
Exit Sub
End If
Next Tbl
End With
John Wirth replied to Danasegarane Arunachalam on 18-Mar-13 06:49 AM
Thanks- I tried that, but the code then breaks at Selection.MoveLeft Unit:=wdCharacter, Count:=3 with runtime error 438- object doesn't support this property or method (it ran past this line before the change before).
John Wirth replied to Danasegarane Arunachalam on 19-Mar-13 06:39 AM
Managed to fix it  myself, although if I use myRange.Delete I get a runtime error, but if I use cut instead, it works fine.

Sub Tables()
Dim WdApp As Word.Application, wdDoc As Word.Document
Set WdApp = Word.Application
Dim Tbl As Table
With WdApp.ActiveDocument
For Each Tbl In .Tables
If Tbl.Cell(1, 2).Range.Text = Chr(13) & Chr(7) Then
Tbl.Select
    WdApp.Selection.MoveLeft Unit:=wdCharacter, Count:=3
    Dim myRange As Word.Range
    Set myRange = WdApp.Selection.Range
      myRange.End = WdApp.ActiveDocument.Range.End
      myRange.Cut
Exit Sub
End If
Next Tbl
End With
End Sub



.....update on runtime error 5904 cannot edit range:
If I use 
myRange.Select
WdApp.Selection.Delete


It works fine. I think I was declaring the range but the range had not actually been selected so VA was trying to delete the declaration. Odd that using cut worked OK though.