Macro to open a word document from excel

[Guest]

Can anyone help please,I am looking for a macro that will open a word
template based upon a 'Y' or 'N' answer in an excel column (column G).

Can this be done?

 

[Bob Phillips]

If Range("H1").Value = "Y" Then
Set oWordApp = CreateObject("Word.Application")
Set oDoc = oWordApp.Documents.Open("C:\personal\bob\doc1.doc")
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing
End If



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

How does this work if there is a N in the column, as that will have to open
the other word doc...sorry should have explained this more...Thanks for the
reply.

 

[Bob Phillips]

Just change the logic to do the test before the documents open in a simple
If ... Else test.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi, my VBA knowledge is non existent!! How do I do that ?

 

[Bob Phillips]

Set oWordApp = CreateObject("Word.Application")
If Range("H1").Value = "Y" Then
Set oDoc = oWordApp.Documents.Open("C:\personal\bob\doc1.doc")
Else
Set oDoc = oWordApp.Documents.Open("C:\personal\jim\another.doc")
End If
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing

If your VBA is non-existent, what will you do with the doc when it is
opened?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

The actual spreadsheet is an advice note register for deliveries, depending
on if they select Y or N, the marco will open the correct form to fill in as
we have incendences of people completing/saving the incorrect form. This just
makes the decison for them.


thank you for your help.

 

[Guest]

Hiya, I am getting a compile error 'INVALID OUTSIDE PROCEDURE',
I have changed the range to read G3 as this is is the cell that it will
read the y/n from (When we add a new entry it moves a row down (so row 3 is
always for 'new data'

Can you tell me whee I'm going wrong, thank you

Set oWordApp = CreateObject("Word.Application")
If Range("G3").Value = "Y" Then
Set oDoc = oWordApp.Documents.Open("C:\Documents and
Settings\mchallacombe\Desktop\CRF_016\advice note\ST011AFO Issue 1 Advice
note AND Customs invoice.dot")
Else
Set oDoc = oWordApp.Documents.Open("C:\Documents and
Settings\mchallacombe\Desktop\CRF_016\advice note\ST011FO Issue 4 Manual
Advice Note.dot")
End If
'do something
Set oDoc = Nothing
oWordApp.Quit
Set oWordApp = Nothing

 

[Bob Phillips]

First, you need to put it in a sub.

Second, as they are forms to fill-in, you need to make Word visible, and not
quit after opening the docs.

Try

Sub GetWordDocument()
Dim oWordApp As Object
Dim oDoc As Object
Dim sFilename As String

Set oWordApp = CreateObject("Word.Application")
If Range("G3").Value = "Y" Then
sFilename = "C:\Documents and Settings\mchallacombe\Desktop\" & _
"CRF_016\advice note\ST011AFO Issue 1 Advice " &
_
"note AND Customs invoice.dot"
Else
sFilename = "C:\Documents and Settings\mchallacombe\Desktop\" & _
"CRF_016\advice note\ST011FO Issue 4 Manual " &
_
"Advice Note.dot"
End If
Set oDoc = oWordApp.Documents.Open(sFilename)
oWordApp.Visible = True
Set oDoc = Nothing
Set oWordApp = Nothing
End Sub




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you, 1 more question!! (sorry for this, but your help is really
appreciated) If I run the macro from the code view it works, but nothing
happens from the normal spreadsheet view when you insert a Y OR N in cell
G3?? Any suggestions, I tried to put a button on the spreadsheet to create
the form linking it to the vba you have supplied me, but that just says
compile error.

Thank you again,
Mark

 

[Bob Phillips]

Add this extra code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G3" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call GetWordDocument
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

WORKS PERFECT - Thank you very much

Thank you, 1 more question!! (sorry for this, but your help is really
appreciated) If I run the macro from the code view it works, but nothing
happens from the normal spreadsheet view when you insert a Y OR N in cell
G3?? Any suggestions, I tried to put a button on the spreadsheet to create
the form linking it to the vba you have supplied me, but that just says
compile error.

Thank you again,
Mark