MS Excel Experts

[Damian]

Are there any people out there with a bit of VB knowledge for Excel.

Here is my first scenario

I have got a yes/no selection in my Excel spreadsheet and on clicking YES I would like it to open a specific Word Document, on clicking NO, it does nothing.

Second scenario

A yes/no selection again and on clicking YES this time it opens a nrand new Word Document and automatically puts in some pre-determined text (meaning the word template does not have to be with the spreadsheet all the time) and on clicking NO, it does nothing

It's basically for a H&S questionnaire, for example, if there is working at height the Manager clicks YES and this either opens the Working at Height Risk Assemsment template, or it open a new document and pastes in the wording

Hope this make sense

Cheers

[Ian]

Okay, this took a little while -

Copy the code below into the VB Editor for the command button that you are using on the excel sheet for Yes. If you want the No button to do nothing, then dont link it to anything.

in the line:-
Set wdDoc = wdApp.Documents.Open("C:\linked from worksheet.doc")
set the path to the word doc that you want.

'In order to use this code you must set a reference to the
'Word object library by doing this. In the VB Editor click
'Tools, References. Then search for Microsoft Word n.n Object Library
'where n.n will depend on your version of Word.

Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open("C:\linked from worksheet.doc")

wdApp.Visible = True
wdDoc.Activate


End Sub


Hope this helps

[Damian]

That was spot on, worked a treat. Can I be cheeky and run this scenario past you.

The document will be given to a variety of site managers which would mean having to give each of them all the template word documents(and there could be 10 or 15) also and making sure they are all stored in the correct way so the hardcoded links work. They cannot link to a server as they may be onsite not connected to the network.

Would there be a way to have some 'text' in the VB code so a new Word document would be created and some text be put into that document automatically thus negating the need for the hard coded paths. I am guessing no formatting will be able to be transferred

Cheers

[ukanalyst]

That was spot on, worked a treat. Can I be cheeky and run this scenario past you.

The document will be given to a variety of site managers which would mean having to give each of them all the template word documents(and there could be 10 or 15) also and making sure they are all stored in the correct way so the hardcoded links work. They cannot link to a server as they may be onsite not connected to the network.

Would there be a way to have some 'text' in the VB code so a new Word document would be created and some text be put into that document automatically thus negating the need for the hard coded paths. I am guessing no formatting will be able to be transferred

Cheers

Here you go mate, the solution posted gives you pretty much what you want with a small addition:

Sub test()
Dim wdApp As Word.Application, wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Add("Normal.dot")

wdApp.Visible = True
wdDoc.Activate
wdDoc.Range(0, 0).Text = "Hello World"
wdDoc.Range(6, 11).Bold = True
wdDoc.Range(0, 5).Underline = True

End Sub

HTH

Dave

[Damian]

Cheers will try tomorrow