Open a word document from excel using vba

Question

Good day,

I have a user form in excel which opens up a word document, question is how do i make the word document active , it should when open have the cursor at a set postion and start taking data from the user form text box values.

I have it such that the document opens and it stops there.

Some code below which has been called from an excel user form where the data has been entered intot he textBoxes..

objWord.Documents.Open "K:\Control_Centre\Fatal_Collision_Photo_Album.doc"

    objWord.Activate

(it stops here)  Selection.TypeText Text:=TextBox2.Value

    Selection.MoveDown Unit:=wdLine, Count:=2

    Selection.TypeText Text:=TextBox4.Value

    Selection.MoveRight Unit:=wdCell, Count:=2

    Selection.TypeText Text:=TextBox6.Value

    Selection.MoveRight Unit:=wdCell, Count:=2

Any suggestions?

Thaks in advance for anyhelp

Colin

Answer 1

Hi,

Something like this?

Private Sub CommandButton1_Click()

' Set reference to 'Microsoft Word xx.0 Object Library'' via VBE > Tools > References.'

    Dim wdApp As Word.Application

    Dim wdDoc As Word.Document

    Dim bWeStartedWord As Boolean

    On Error Resume Next

    Set wdApp = GetObject(, "Word.Application")

    On Error GoTo 0

    If wdApp Is Nothing Then

        Set wdApp = CreateObject("Word.Application")

        bWeStartedWord = True

    End If

    wdApp.Visible = True  'optional, not required

    wdApp.Activate        'optional, not required

'    On Error GoTo ExitPoint    'commented-out during debugging.

    Set wdDoc = wdApp.Documents.Open("K:\Control_Centre\Fatal_Collision_Photo_Album.doc")

    wdDoc.Tables(1).Cell(1, 2).Range.Text = TextBox1.Value 'Location

    wdDoc.Tables(2).Cell(1, 2).Range.Text = TextBox2.Value 'Day

    wdDoc.Tables(2).Cell(1, 4).Range.Text = TextBox3.Value 'Date

    wdDoc.Tables(2).Cell(1, 6).Range.Text = TextBox4.Value 'Time

    wdDoc.Tables(3).Cell(1, 2).Range.Text = TextBox5.Value 'Collision Ref No. (left)

    wdDoc.Tables(3).Cell(1, 4).Range.Text = TextBox6.Value 'Collision Ref No. (right)

    wdDoc.Close SaveChanges:=True  'Close file and Save changes

ExitPoint:

    If bWeStartedWord Then wdApp.Quit

    Set wdDoc = Nothing

    Set wdApp = Nothing

    Unload Me

End Sub

Hope that helps.

Cheers

Rich

Answer 2

Hi,

Since your code is running in Excel VBA, any unqualified code will be assumed to belong to the Excel Application.

You should qualify anything that needs to work in word by using the objWord object

Try:

    **objWord.**Selection.MoveDown......

etc.

Hope that helps.

Cheers

Rich

Answer 3

tried this,  it produces "run time error 4120:

Bad parameter

and highlights

objWord.Selection.MoveDown Unit:=wobjWord.dLine, Count:=2

Answer 4

Hi,

I made a UserForm with two TextBoxes and a Command Button, then added this code:

Private Sub CommandButton1_Click()

' Set reference to 'Microsoft Word xx.0 Object Library'

' via VBE > Tools > References.

'

    Dim wdApp As Word.Application

    Dim wdDoc As Word.Document

    Dim bWeStartedWord As Boolean

    On Error Resume Next

    Set wdApp = GetObject(, "Word.Application")

    On Error GoTo 0

    If wdApp Is Nothing Then

        Set wdApp = CreateObject("Word.Application")

        bWeStartedWord = True

    End If

    wdApp.Visible = True

    wdApp.Activate 'not required

'    On Error GoTo ExitPoint

    Set wdDoc = wdApp.Documents.Open("C:\Temp\MyWordDoc.docx")

    wdApp.Selection.MoveDown Unit:=wdLine, Count:=2

    wdApp.Selection.TypeText Text:=TextBox1.Value

    wdApp.Selection.MoveRight Unit:=wdCell, Count:=2

    wdApp.Selection.TypeText Text:=TextBox2.Value

    wdApp.Selection.MoveRight Unit:=wdCell, Count:=2

    wdDoc.Close SaveChanges:=True

ExitPoint:

    If bWeStartedWord Then wdApp.Quit

    Set wdDoc = Nothing

    Set wdApp = Nothing

    Unload Me

End Sub

It runs find PROVIDING the cursor is in a table, since .MoveRight Unit:=wdCell will fail if you are not in a table.

I think it would be best if you explained in more detail exactly what you are trying to do.  i.e. what is in the Word document when you open it, and where do you need to place data?

Cheers

Rich

Answer 5

Thanks Rich,

essentially i am trying to open a word document which has a title with 3 tables underneath which will take the entries from the textbox values in the user form which was completed previously under the excel vba.   I can attach a copy of the file below.

https://www.dropbox.com/s/n4zyhkenhiz0z73/dropbox.doc

Thanks again for any help you can give on this.

Regards

Colin