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

Thanks Rich,

It does work however seems to stop after the save as file has been executed leaving the vb code hanging to finish

    wdApp.CommandBars.ExecuteMso("FileSaveAs") ' this works ok to bring up the dialog box save file and "disappears", it does not finish the remaining code below.

ExitPoint:

    If bWeStartedWord Then wdApp.Quit

    Set wdDoc = Nothing

    Set wdApp = Nothing

    Unload UserForm1

Any suggestions to make it finish?

Thanks

Colin

Answer 3

it does not finish the remaining code below.           

Hi,

Are you sure?  How can you tell?  It might stop stepping through the code, but I think the code will run to End Sub.  Proof?  Does the userform disappear in Excel?  Does Word Quit if it wasn't already open?

Try adding a    Stop     after the SaveAs line.

Note: if you want to hide the UserForm at the start of the code, you could add Me.Hide at the start of the CommandButton1_Click() procedure.  You still need to Unload it at the end though.

Cheers

Rich

Answer 4

Hi Rich,

My mistake I stand corrected, i was stepping through the code and it seemed to get muddled up somewhere along the line.

Thanks again

Regards

Colin