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!
