CODEWITHSUNDEEP
vbaexcelms-word
Googlesoar
Googlesoar

Reputation: 25

How to use Words selection methods through Excel VBA

In Word VBA you are able to set and move the cursor position by using the Selection.MoveLeft, Selection.MovRight etc...

However while trying to use this same method in Excel VBA I get an error saying "Object doesnt support this property or method."

I have imported the Word Object Library reference.

How am I able to move the cursor position on the Word document using VBA on the Excel application. Any help will be greatly appreciated.

Code: Set Doc = ActiveDocument.Content

    With Doc.Find
    .Execute FindText:="*", ReplaceWith:="NEW*"
    End With

    Selection.HomeKey Unit:=wdStory
    Selection.MoveDown Unit:=wdParagraph, Count:=11
    Selection.MoveRight Unit:=wdWord, Count:=4
    Selection.MoveRight Unit:=wdWord, Count:=2, Extend:=wdExtend
    Selection.Font.Bold = False
    Selection.Font.Name = "Arial"
    Selection.Font.Size = 9

Upvotes: 0

Views: 7830

Answers (3)

abyseller
abyseller

Reputation: 1

'Something Basic
'Session of word
'Existing document

Sub fromaWordDoc0()
   Dim wdApp As Word.Application
   Dim wdDoc As Word.Document

   Set wdApp = CreateObject("word.application")
   wdApp.Visible = True
   Set wdDoc = wdApp.Documents.Add("C:\Documents\words.docx")

   With wdApp.Selection
      wdApp.Selection.MoveRight Unit:=wdWord, Count:=4
   End With
   Set wdApp = Nothing: Set wdDoc = Nothing
End Sub

Upvotes: 0

Variatus
Variatus

Reputation: 14383

Your problem will go away if you would rephrase your question, "move the cursor position on the Word document using VBA on the Excel application". You can't move the cursor in a Word document using the Excel application.

When you open an Excel workbook you load an instance of the Excel application. You can use this same instance to open several workbooks. It also contains Excel VBA with all objects, methods and functions of the Excel application. This instance has no name.

But you could create another instance of the Excel application with code like

Dim XlApp as Excel.Application
Set XlApp = New Excel.Application

The new instance you have thus created has all the facilities of the first instance but is completely separate form it. You can open workbooks in it with code like

Dim Wb as workbook
Set Wb = XlApp.Workbooks.Add([Template])

Now, if you have set a reference to the MS Word Object Library you can create a Word application using similar code, for example,

Dim WdApp as Word.Application
Set WdApp = New Word.Application

This could be the only instance of MS Word running on your computer or it could be a new instance created in addition to other instances already running. This instance knows all the objects and methods of MS Word.

You can control both instances, XlApp and WdApp, in the same VBA project, but you should be careful to differentiate the objects. Both Excel and Word have a Range object for example. They are very different animals. You can specify, for example,

Dim xlRng As Excel.Range
Dim wdRng As Word.Range
Dim MyRng As Range

In this example, MyRng will be an Excel range if your VBA project is an Excel project. While you have both applications running this kind of defaulting will cause hair loss.

Dim Wb As Workbook
Dim Doc As Document

don't cause similar confusion because there is no Workbook object in MS Word and no Document object in MS Excel. VBA will use the correct application automatically, provided it is available.

When manipulating the WdRng you will have access to all the methods of the Word Range object, including Move, but there is no Address property, for example, which is a property of the XlRng.

Dealing with the two Selection objects is problematic. You will have to activate a window or document or workbook, and VBA will know which Selection object you mean by looking at the application running in the selected window. You won't have that problem if you specify the document/workbook object and use the WdRng or XlRng objects to manipulate your data.

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4714

As your code stands the keyword "selection" refers to the currently selected cell in excel, not the cursor position in your Word document. And the Excel Selection object does not have move methods. You might have more success using Range rather than selection: You example code translates as

With ActiveDocument

    With .Content.Find
        .Execute FindText:="*", ReplaceWith:="NEW*"
    End With


     with .Paragraphs(4).Range.Words(5).Font
         .Bold = false
         .name = "Arial"
          .size = 9
      end with
       With .Paragraphs(4).Range.Words(6)
         .Bold = false
         .name = "Arial"
          .size = 9
      end with
end with

Upvotes: 0

Related Questions