Solved: Make Word Work

Hello Everyone :hi:

I am working with a somewhat simple database, and have stumbled into a problem by trying to make Word work from Access correctly. Here are my details:

1. Using the 'Publish with Word' button in Access does not correctly display the data I wish to display correctly.
2. I then created a Word template to use the Mail Merge, linking my 'source' to my access table. Problem is that on my Access Report, I do some complex calculations with the data fields that do not get stored in the table itself, and therfore I don't have access to that using my Mail Merge. Word can not perform the calculations I need either.
3. I then discovered that if I first export my Access report to an Excel file, and use that file as my 'source' for my Word template, all the data is there that I need, including the calculated values done on the Access Report itself.
4. If I open up the template using Word, I get the prompt telling me that "opening this document will run the following SQL command...etc.", and the template opens up as a new document displaying the updated data I just exported to Excel from Access.
5. I created a command button in Access to do the export to Excel, and this works fine.
6. I then created a command button in Access to try and open up the Template using Word, and this is what does not function right. Word does open, and the template does open, but it opens as the template and not as a new document. Nor do I get the MsgBox telling me that the opening the file is going to run the SQL command. The data displayed will be the data from when I last ran the template file from Word itself. If I first do an Export to Excel, and then click on the Access button to open up the template, that new data is not displayed.
7. Using the Hyperlink to open the template does not correct this 100% either. It does open the file, it does update the data from excel, but it opens the file as a template (.dot) instead of as a new document (.doc). I must use the Save As so not to overwrite the .dot template file. This is ok for me, but will be confusing to other users of this program.

Here is the VBA code I tried to use first:

[VBA]
Private Sub OpenWordSDCalc_Click()
On Error GoTo Err_OpenWordSDCalc_Click

Dim LWordDoc As String
Dim oApp As Object

'Path to the word document
LWordDoc = "C:\PathCalc\Main Calc Merge Document.dot"

If Dir(LWordDoc) = "" Then
MsgGox "Document not found."

Else
'Create an instance of MS Word
Set oApp = CreateObject("Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Open FileName:=LWordDoc
End If

Exit_OpenWordSDCalc_Click:
Exit Sub

Err_OpenWordSDCalc_Click:
MsgBox Err.Description
Resume Exit_OpenWordSDCalc_Click

End Sub [/VBA]

If anyone has ANY suggestions or help, I would greatly appreciate it.

Thank you,

Jerry :dunno

Hi Jerry
Welcome to VBAX

Have a look at the item here.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=290
It is easiest to open a mailmerge document as a doc file, and this can be done with a FollowHyperlink line. As this is merged to a new document in any case, there is no advantage in opening the template. The KB item simply creates the new document when it is opened, with an option for saving to a location specified by datasource fields.

Thank you for the reply. I will check out your suggestion and see how that works :friends:

Just wanted to let you know that my issue with Word is resolved, and that I appreciated your input very much:beerchug: .

Jerry

Jerry

How have you resolved it?

Did you use the Add method of the Documents coolection rather than Open?