Linking Access97 and Word97 
Author Message
 Linking Access97 and Word97

Im sorry but this is a little complicated.
I have a project written in Office 4.x (Access 2.0 and Word 6) and I am
upgrading to Office 97.  The Word component consists of multiple
templates linked together with Wordbasic code.  The access part is a
patient database, accounts and various other odds and sods, including a
medication database used for prescription writing.  The two programmes
interact via DDE calls.  In upgrading some of these calls do not work
(error message indicates overflow), so I have upgraded to VBA code.
However, the action that was called with DDE causing an overflow error
also does not work using Automation.
I can open Word from Access, or open a particular file from Word in a
resting state with the following VBA code,with the string CodeCall being
the appropriate command to cause a word macro to run (eg.
"Normal.OpenFile.MAIN"):

Function OpenWord(CodeCall As String)
On Error GoTo Error_OpenWord
Dim PatientFiles As New Word.Application, strCallAutoexec As String
Set PatientFiles = GetObject(, "Word.Application")
WordNotOpen:
PatientFiles.Application.Visible = True
If strCallAutoexec <> "" Then
    PatientFiles.Application.Run strCallAutoexec
End If
If CodeCall <> "" Then
    PatientFiles.Application.Run CodeCall
End If
PatientFiles.Activate
Resume_OpenWord:

Exit Function
Error_OpenWord:
If Err = 429 Then     ie Word not open
    strCallAutoexec = "Normal.Autoexec.MAIN"
    GoTo WordNotOpen
End If
MsgBox Error$
Resume Resume_OpenWord
End Function

The document thus opened is a patient file, based on a separate template
(Medical.dot) and is the main part of the Word project.  There is no
problem connecting to Access from this, but when I try to return and
cause a Word macro to run using code similar to above I consistently get
messages stating Unable to run specified macro.  The macro is
contained within Medical.dot.  This is the code, with strMedCode being
"Medical.AddMedication.MAIN":

Public Sub AddMed(strMedCode As String)
On Error GoTo Error_AddMed
Dim wordMedAdd As New Word.Application, strWordCode As String
Dim objFileName As Word.Document
Set wordMedAdd = GetObject(, "Word.Application")
WordNotOpen:
wordMedAdd.Application.Visible = True
This opens Word if word not open.
If strWordCode <> "" Then
    wordMedAdd.Application.Run strWordCode
End If
If strMedCode <> "" Then
    wordMedAdd.Application.Run (strMedCode)
End If
wordMedAdd.Activate
Resume_AddMed:
Exit Sub
Error_AddMed:
If Err = 429 Then  ie Word not open
    strWordCode = "Normal.Autoexec.MAIN"
    GoTo WordNotOpen
End If
MsgBox Error$
Resume Resume_AddMed
End Sub

I have tried transferring the macro to Normal.dot and calling it from
there, but no difference.  I have selected Word 8 in the references
section of Access, and Access in the reference section of the VBA
module.  The only difference that I can see between the first and second
situations is that in the second a file is open (rather than Word being
closed or open with an empty window) and that the document open is not
based on Normal.dot.  Is there something Im missing?
I really need to get this project revamped pronto, so would appreciate
help.
Many thanks in advance, and I apologise for it being so long winded.
Brett

ps if you could please reply to my e-mail as well it would be greatly
appreciated



Tue, 03 Jul 2001 03:00:00 GMT  
 Linking Access97 and Word97

Quote:

>Dim PatientFiles As New Word.Application

The New keyword instantiates a whole new Word session in memory, but
you later re-initialise the variable with GetObject. If the first Word
application remains in memory, you'll be leaking memory. The more
usual way to get a Word.Application handle is along these lines:

  On Error Resume Next   '<----- pick up error if Word is not running
  Set wapp = GetObject(, "Word.Application")

  If Err.Number Then
    Debug.Print "Creating new Word application"
    Set wapp = CreateObject("Word.Application")
  End If
  On Error GoTo 0        '<----- back to normal error handling

Quote:
>Error_OpenWord:
>If Err = 429 Then     ie Word not open
>    strCallAutoexec = "Normal.Autoexec.MAIN"
>    GoTo WordNotOpen
>End If

If you are getting overflow errors, it maybe because the GoTo command
should be a Resume: without Resume, the error handler does not get
reset, and you'll end up in an infinite recursion rather than an
infinite loop!

Quote:
>If CodeCall <> "" Then
>    PatientFiles.Application.Run CodeCall
>End If

Remember that PatientFiles was dimmed as a application object anyway,
so the call to PatientFiles.Application is strictly redundant.

The other thing to bear in mind is that if you create a handle to the
document rather than the application, you can call any methods in the
template directly:

  docMyNewDocument.InsertNewPage( strMyAddress )

where you have a

  Public Sub InsertNewPage( AddressText as String )

in the word template. For me, this has the advantage of being able to
run functions and subs as well as macros, the programming and
debugging all happens inside Word, and I assume it's quicker to run
VBA code within the same memory as WinWord itself.

Quote:
>Dim wordMedAdd As New Word.Application, strWordCode As String
>Dim objFileName As Word.Document
>Set wordMedAdd = GetObject(, "Word.Application")

I'm not quite sure what you're trying to do here. Once again, you have
a brand new word application which you don't use. I see you were
intending to open a new document objFileName, but I can't see any
further reference to this. One way to find or open a document with a
specific template is like this:

  ' See if there's one already open
  For Each wdoc In wapp.Documents
    If wdoc.AttachedTemplate.FullName = strTemplatePath Then
      Exit For
    End If
  Next wdoc

  ' If we couldn't find one, create a new one
  If wdoc Is Nothing Then
    Set wdoc = wapp.Documents.Add(Template:=strTemplatePath)

  ElseIf wdoc.AttachedTemplate.FullName <> strTemplatePath Then
    Set wdoc = wapp.Documents.Add(Template:=strTemplatePath)

  End If

It took quite a lot of messing about to pick up all the gotchas, but
it did work in the end. Best of luck!

Tim F

--



Wed, 04 Jul 2001 03:00:00 GMT  
 Linking Access97 and Word97
Dear Tim,
Many thanks for the time you took to struggle through my question (re
linking Word and Access).  I'll put them into action and see how I go.
Regards,
Brett Thompson


Thu, 05 Jul 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Closing Word97 from Access97

2. Pulling Access97 data into Word97 variables

3. Editing an Access97 field value from Word97

4. Access97-Word97 Mail Merge

5. Access97/Word97/data exchange HELP!!!

6. Access97/Word97 MailMerge

7. passing values between Word97 and Access97

8. Mail Merge access97 with word97 (Blank spaces)

9. Access97 and Word97 (MailMerge)

10. DDE between Access97 and Word97

11. Integration between Access97 and Word97

12. Print Word97 document from Access97

 

 
Powered by phpBB® Forum Software