• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

sending data to word as mail merge not working

I have a button on a form that when clicked will generate a word document using a blank template and just supplying needed data. it used to work fine until we upgraded to Office 2007. here is the sub routine.

Private Sub MailMergeInit_Click()

  Dim objWord As Word.Document
  Dim objDocument As Word.Document
 
  Set objWord = GetObject("\\Txnt38\puwtc\Business_Partner_Database\Preliminary_V1.doc", "Word.Document")
  ' Make word visible
  objWord.Application.Visible = True
  ' Set the mail merge data source as the current record
  objWord.MailMerge.OpenDataSource Name:=Access.CurrentDb.Name, LinkToSource:=True, Connection:="TABLE tblMain", SQLStatement:="SELECT * FROM [tblMain] WHERE [Notice Number]=" & Notice_Number.Value
  objWord.MailMerge.Execute
 
  ' Grab the newly opened document
  Set objDocument = objWord.Application.ActiveDocument
  Sleep 2000 ' sleep for 1000msec = 1 second
  ReplaceDocLinks
 

End Sub
0
jlcannon
Asked:
jlcannon
  • 9
  • 5
2 Solutions
 
jlcannonAuthor Commented:
The error I am getting is "Warning: Could not perform test link replacement. try closing the document and re-exporting it.
0
 
GrahamSkanCommented:
On which line does it produce the error?
0
 
jlcannonAuthor Commented:
it happens after it opens the word docs and it does not give me a debug option its just an error
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
jlcannonAuthor Commented:
Looks like the error is in this sub routine as the Err_ReplaceDocLinks: is the error i am getting.

Private Sub ReplaceDocLinks()

  On Error GoTo Err_ReplaceDocLinks
 
  ' Find next Document link: #\\
 
  With ActiveDocument.Content.Find
 
    .ClearFormatting
    .Text = "#\\\\*#"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = True
   
    Do While .Execute(FindText:="#\\\\*#", Forward:=True, Format:=False) = True
      With .Parent
        .Select
        linkLocation = Selection.Text
        properLocation = Mid(linkLocation, 2, Len(linkLocation) - 2)
        .Cut
        ActiveDocument.Hyperlinks.Add _
          Anchor:=Selection.Range, _
          Address:=properLocation
      End With
    Loop
   
    Do While .Execute(FindText:="#http://*#", Forward:=True, Format:=False) = True
      With .Parent
        .Select
        linkLocation = Selection.Text
        properLocation = Mid(linkLocation, 2, Len(linkLocation) - 2)
        .Cut
        ActiveDocument.Hyperlinks.Add _
          Anchor:=Selection.Range, _
          Address:=properLocation
      End With
    Loop
    Set firstRange = ActiveDocument.Range(Start:=0, End:=1)
    firstRange.Select
  End With
 
  MsgBox "File export completed."
Resume_ReplaceDocLinks:
  Exit Sub
 
Err_ReplaceDocLinks:
  MsgBox "Warning: Could not perform text link replacement.  Try closing the document and re-exporting it.", vbCritical
  Resume Resume_ReplaceDocLinks
 
End Sub
0
 
GrahamSkanCommented:
You' might need to remove the error handler to see what the actual error is, and which is the failing line.

In the meantime, I suggest passing the document object (objDocument) that into the routine and using that instead of ActiveDocument
0
 
jlcannonAuthor Commented:
so your saying to replace ActiveDocument with objDocument?
0
 
GrahamSkanCommented:
More or less, yes. The code finds the result document, but does nothing with it.

 
'... 
 ' Grab the newly opened document
  Set objDocument = objWord.Application.ActiveDocument
  Sleep 2000 ' sleep for 1000msec = 1 second
  ReplaceDocLinks objDocument 
'...  

Private Sub ReplaceDocLinks(oDoc as Word.Document)

  On Error GoTo Err_ReplaceDocLinks
 
  ' Find next Document link: #\\
 
  With oDoc.Content.Find
 
    .ClearFormatting
    .Text = "#\\\\*#"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = True
   
    Do While .Execute(FindText:="#\\\\*#", Forward:=True, Format:=False) = True
      With .Parent
        .Select
        linkLocation = Selection.Text
        properLocation = Mid(linkLocation, 2, Len(linkLocation) - 2)
        .Cut
        oDoc.Hyperlinks.Add _
          Anchor:=Selection.Range, _
          Address:=properLocation
      End With
    Loop
   
    Do While .Execute(FindText:="#http://*#", Forward:=True, Format:=False) = True
      With .Parent
        .Select
        linkLocation = Selection.Text
        properLocation = Mid(linkLocation, 2, Len(linkLocation) - 2)
        .Cut
        oDoc.Hyperlinks.Add _
          Anchor:=Selection.Range, _
          Address:=properLocation
      End With
    Loop
    Set firstRange = ActiveDocument.Range(Start:=0, End:=1)
    firstRange.Select
  End With
 
  MsgBox "File export completed."
Resume_ReplaceDocLinks:
  Exit Sub
 
Err_ReplaceDocLinks:
  MsgBox "Warning: Could not perform text link replacement.  Try closing the document and re-exporting it.", vbCritical
  Resume Resume_ReplaceDocLinks
 
End Sub

Open in new window

0
 
jlcannonAuthor Commented:
Ok so here is the current state of affairs. the very first time I open the database and click the generate letter button it works with the exception that i would like it to close the template document and just leave the newly created document open. But them once I have done it 1 time, if i try to do it again i get the error and with the err_txt turned off it take me to the sub routine ReplaceDocLinks() which is the above referenced routine and the highlighted line is "With ActiveDocument.Content.Find" the very first line in the sub....
0
 
jlcannonAuthor Commented:
Ok so now it seems consistant. the very first time I run it it opens the template then also opens and creates a new documetn using that template and it fails to close the template but thats another question. the problem comes if i then close the word docs and then go click that button on the form again to start this process all over it gives an error and the highlighted code is "Set firstRange = ActiveDocument.Range(Start:=0, End:=1)" in the Private Sub ReplaceDocLinks(oDoc As Word.Document)
0
 
GrahamSkanCommented:
I didn't see that line. Change it as well, so that it reads:

Set firstRange = oDoc .Range(Start:=0, End:=1)
0
 
jlcannonAuthor Commented:
this now gives a syntax error with that line highlighted in blue.
0
 
jlcannonAuthor Commented:
thats what I get for compy / paste... there was a space between the odoc and .range.... works beautiful/
0
 
jlcannonAuthor Commented:
awesome!
0
 
GrahamSkanCommented:
Thanks. I think that this was really a Word problem. It you have similar questions, it would be better to post in, or at least, include the Word zone.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now