MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Worksheet Selection Change


Posted by Stuart on October 28, 2001 8:18 PM

I have the following in my workbook, but instead of opening Word, I wish to open a specific word document. I have tried document.open, but this does not work in excel. Any help is appreciated.

Regards
Stuart

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Range("A1")
If Not Intersect(keyRange, Target) Is Nothing Then
If Target = "True" Then
UserReply = MsgBox("Microsoft Word opening... Do you wish to continue?", vbYesNo)
If UserReply = vbYes Then
Application.ActivateMicrosoftApp xlMicrosoftWord
Else: End If
Else: End If
End If
End Sub


Posted by Leroy on October 29, 2001 12:41 AM

Stuart - you may need to use DDEInitiate.

eg: ChannelNumber = Application.DDEInitiate( _
app:="WinWord", _
topic:="C:\YOURDOC.DOC")

Application.DDETerminate ChannelNumber

Could be worth a look?

Posted by Stuart on October 29, 2001 1:43 AM

Didn't work!

I tried your suggestion, but I get error messages:

1. Remote Data Not Accessible. Start Application WINWORD.EXE? (Yes/No buttons)

2. The document name or path is invalid. Try these suggestions. *Check the file permissions in the document or drive. *Use the file open dialog box to to locate the document (C:\MonthlyPlanner.doc)

(OK Button only)

Before you ask, yes that is the correct file location and name! Any assistance is greatly appreciated. My code now looks as below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Range("A1")
If Not Intersect(keyRange, Target) Is Nothing Then
If Target = "True" Then
UserReply = MsgBox("Microsoft Word opening... Do you wish to continue?", vbYesNo)
If UserReply = vbYes Then
ChannelNumber = Application.DDEInitiate( _
app:="WinWord", _
topic:="C:\MonthlyPlanner.doc")
Application.DDETerminate ChannelNumber
Else: End If
Else: End If
End If
End Sub

Posted by Leroy on October 29, 2001 9:43 AM

Re: Didn't work!

Hi Stuart,

Give this a go.

Dim WordPath as Variant
Dim WordDoc as Variant

WordPath = "c:\<yourpath>\office\winword.exe"
WordDoc = "c:\yourfile.doc"

Shell WordPath, 6 'This command will open Word NB: the second parameter controls how Word will be opened
Chan = DDEInitiate("WinWord", "System") 'This opens DDE channel
DDEExecute Chan, "[fileopen(" + Chr$(34) + WordDoc + Chr$(34) + ")]" ' This opens your file

' You can do any other word commands here using DDE

DDETerminate Chan ' This will terminate the DDE channel

This code should come after your display of the dialog box

Hope this is better.

L.

Posted by Stuart on October 29, 2001 12:00 PM

That did the job - Thanks Leroy!