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

Forgot to say, the other thing i was finding was to try and get the cursor to be in the right place when the document opened ie at the table cell to enter the location.

Colin

Answer 3

Thanks again Rich

Regards

Colin

Answer 4

Rich,

I don't seem tobe able to get it to bring up the SaveAs dialog box, any ideas,

I tried

wdDoc.Saveas2     but do not wish to assign a name until the dialog box comes up so i can save it ina specific location on the hard drive.

Regards

Colin

Answer 5

Hi,

Easiest way (but not compatible with Word 2003) is do use:

    wdApp.CommandBars.ExecuteMso("FileSaveAs")

Cheers

Rich