DDE and/or Importing text from Word

[Guest]

I have access forms that open Word templates to create word documents. When
I'm done typing on the Word documents, I want to be able to import the
information I've typed back into Access. It's pretty simple to export data
from Access into Word by automation, but there is nothing much written on
doing the reverse.

The easiest way would seen to be to use DDE in the close/quit event of the
Word template. For the life of me, I can't figure out the syntax to do this.

I can "almost" get there using DDE from the Access side as follows:
Private Sub OpenWordTemplate_Click ()
'code that opens template and fills various fields
While objWord.IsObjectValid("test.dot")
'code here that as long as the word document is open,
'Access "hangs"
Wend

Dim Channel as Long
Channel = DDEInitiate("WinWord", Topic:="test.dot")
strBodyBookmark = DDERequest(Channel, "bodybookmark")
End Sub

But my problem is that I want to trigger the request for this information at
the close/quit event in the Word (to capture the last version of the
document); and a document has to be open to use DDE.

Any thoughts or suggestions would be greatly appreciated.

Bruce Maston

 

[Brian Wilson]

I have access forms that open Word templates to create word documents.
When
I'm done typing on the Word documents, I want to be able to import the
information I've typed back into Access. It's pretty simple to export
data
from Access into Word by automation, but there is nothing much written on
doing the reverse.

The easiest way would seen to be to use DDE in the close/quit event of the
Word template. For the life of me, I can't figure out the syntax to do
this.

I can "almost" get there using DDE from the Access side as follows:
Private Sub OpenWordTemplate_Click ()
'code that opens template and fills various fields
While objWord.IsObjectValid("test.dot")
'code here that as long as the word document is open,
'Access "hangs"
Wend

Dim Channel as Long
Channel = DDEInitiate("WinWord", Topic:="test.dot")
strBodyBookmark = DDERequest(Channel, "bodybookmark")
End Sub

But my problem is that I want to trigger the request for this information
at
the close/quit event in the Word (to capture the last version of the
document); and a document has to be open to use DDE.

Any thoughts or suggestions would be greatly appreciated.

Bruce Maston

Have you looked at using WithEvents, eg:

Dim WithEvents wdApp As Word.Application

Then create the instance:

Set wdApp = New Word.Application
wdApp.Documents.Open ("C:\MyDoc.doc")
wdApp.Visible = True
wdApp.Activate

Then you can tap in to the object's events:

Private Sub wdApp_DocumentBeforeClose(ByVal Doc As Word.Document, Cancel As
Boolean)
Dim strParagraph1
' Grab the first paragraph to save in my db
strParagraph1= Doc.Paragraphs(1).Range.Text
End Sub

 

[Guest]

Hi Brian,

Thank you for your reply. I have now spend several days working on this,
and I’ve made some progress. I still haven’t cracked it, and I see that
WithEvents are a complicated thicket. So I’m going to lay out as carefully
as I can what I’ve done so far. You will see that my problem is "premature
termination†of the Class Module containing the WithEvents procedures with
the result that they do not fire.

First, I created a Class Module and I named it cWord.

This is the code in the Class Module cWord:
---------------------------------------
Option Compare Database
Public WithEvents clWord As Word.Application
Public WithEvents clDoc As Word.Document
‘You cannot put these declaration statement in the regular module
‘But you can declare it in Form1, and it seems to make no difference

Private Sub Class_Initialize()
MsgBox "Initialize Successfulâ€
End Sub

Private Sub Class_Terminate()
MsgBox "Terminatingâ€
End Sub

Private Sub clWord_documentbeforeclose(ByVal Doc As Word.Document, Cancel As
Boolean)
strBody = ActiveDocument.Bookmarks("body").Range
MsgBox strBody
‘What I’m trying to do is capture the contents of the “body†bookmark
'in a string variable
‘at the closing of a Word document
End Sub
------------------------------------------------
My regular procedure module has this code:
------------------------------------------------
Public clWord As Word.Application
‘You have to declare this variable here
'as well as as a WithEvent in Class Module cWord
‘or you get an error the the Word object doesn’t exist
'the second time you click the
‘command1 button
Public clDoc As Word.Document
Public myClassModule As Object
‘Nothing at all fires in the Class Module cWord
' unless you declare something
‘I’ve seen mention of this in other threads.
------------------------------------------------
I have a Form1, and it contains a Command button named Command1
The code here is:
------------------------------------------------
Private Sub Command1_Click()
Set clWord = New Word.Application
Either: Set clDoc = _
clobWord.Documents.Add _
(Template:="d:\data\templates\BLANK_test.dot")
Or:
clobWord.Documents.open ("d:\data\skunk.doc")
‘No matter whether you try adding a template
'or opening a document, the result is the same
clWord.Visible = True
clWord.Activate
Set myClassModule = What???
‘This is probably where the problem is. If you comment out “Set
myClassModuleâ€,
‘what happens is that when you open the form
'you see first the box “initialize successfulâ€
‘and then you see “terminatingâ€. Nothing happens when closing the Word
document
‘because the Class Module is terminated.
‘On the other hand, I can’t figure out any syntax
'for this line (or some other line in its place) that keeps the
‘Class Module from prematurely terminating
End Sub

 

[Guest]

I did figure this out, and I'm putting it here to help anybody who comes along.
Changes I made from my last post:
IN THE CLASS MODULE cWord:
-----------------------------------------
Private Sub Class_Initialize()
Set clWord = New Word.Application
Set clDoc = New Word.Document
Set clobdoc = _
clobWord.Documents.Add _
(Template:="d:\data\templates\BLANK_test.dot")
clobWord.Visible = True

End Sub
'the instances of Word go here, so they are "tied" to cWord
Private Sub clWord_Quit()
Set clWord = Nothing
Set myClassModule = Nothing
End Sub
---------------------------------------
IN THE REGULAR PROCEDURE MODULE:
---------------------------------------
Global myClassModule As cword
'it's supposed to be "Global"
---------------------------------------
In Form1:
---------------------------------------
Private Sub Command1_Click()
Set myClassModule = New cword
end sub
'when you click on the button, you create a new instance of cWord
'and this initializes it where (in turn, see above) the instances
'of Word are initialized.
'myClassModule (and cWord) exist until the quit event for Word

Full disclosure: I have a tattered version of the Paul Litwin "Bible," but I
don't have it where I am working here. So I went to Barnes & Noble and
looked up WithEvents. There are a few pages that outlined this well, and I
was able to figure it out from this source. Litwin is not called "The Bible"
for nothing! If you don't have his book, you should get it.

I haven't gone on to use it in my application yet, but this will obviously
work to pull back the bookmark contents at the closing of a word document.
The same WithEvents syntax would equally work for any automation project. I
am thankful to Brian Wilson for pointing me in this direction. I'm only
sorry that this post is mis-labeled DDE rather than WithEvents.

Bruce Maston

 

[Brian Wilson]

I did figure this out, and I'm putting it here to help anybody who comes
along.
Changes I made from my last post:
IN THE CLASS MODULE cWord:
-----------------------------------------
Private Sub Class_Initialize()
Set clWord = New Word.Application
Set clDoc = New Word.Document
Set clobdoc = _
clobWord.Documents.Add _
(Template:="d:\data\templates\BLANK_test.dot")
clobWord.Visible = True

End Sub
'the instances of Word go here, so they are "tied" to cWord
Private Sub clWord_Quit()
Set clWord = Nothing
Set myClassModule = Nothing
End Sub
---------------------------------------
IN THE REGULAR PROCEDURE MODULE:
---------------------------------------
Global myClassModule As cword
'it's supposed to be "Global"
---------------------------------------
In Form1:
---------------------------------------
Private Sub Command1_Click()
Set myClassModule = New cword
end sub
'when you click on the button, you create a new instance of cWord
'and this initializes it where (in turn, see above) the instances
'of Word are initialized.
'myClassModule (and cWord) exist until the quit event for Word

Full disclosure: I have a tattered version of the Paul Litwin "Bible," but
I
don't have it where I am working here. So I went to Barnes & Noble and
looked up WithEvents. There are a few pages that outlined this well, and
I
was able to figure it out from this source. Litwin is not called "The
Bible"
for nothing! If you don't have his book, you should get it.

I haven't gone on to use it in my application yet, but this will obviously
work to pull back the bookmark contents at the closing of a word document.
The same WithEvents syntax would equally work for any automation project.
I
am thankful to Brian Wilson for pointing me in this direction. I'm only
sorry that this post is mis-labeled DDE rather than WithEvents.

Bruce Maston

Hi Bruce
I had been thinking about this post over the weekend, contemplating the best
solution and I'm glad to see that in the mean time you have found a way
forward.
The conclusion I came to is that I didn't know enough about what you were
trying to do to give any concrete advice. Is there really a need to store
data in both Word and Access (leading to problems in synchronizing them)?
If you really have to, I wonder if you have seen the MS control that would
allow you to display/edit the Word document on an Access form (
http://support.microsoft.com/kb/q311765/ ). I have used this control for
testing purposes, but never in a production db.