Hi all, I have a query regarding 1

[MatthewGB]

Hi all,

I have a query regarding sending data from an Access Database into a word template.

Essentially, I want to be able to click a button on one of my forms in the database, and have the information contained in particular fields sent into a word template which has already been set up. I guess you would set up bookmarks on the template so Access knows where to put the data, but im not sure of the best way of transferring the data (syntax, method etc.) Ideally I would like to send the data from Access over to Word, rather than open word and retrieve the data from Access. Also, when the user hits Save or SaveAs, i would like the default filename to be that of one of the fields.

I have tried using DDE already, but it has problems finding the bookmarks with a .dot template. It works fine with a .doc template however. Although I'm sure there are much easier methods than DDE.

Anyone help or direction to helpful threads would be much appreciated.

Thanks
Matt

 

[Nikita6003]

Hi Matt,

Sure there are easier ways. In Access you can set up a straight link to Word by launching it using VBA, then use the data in Access to fill the field values that you want filled. If you;re using FormField textboxes, for instance, you could use the ThisDocument.Fields("FieldName&quot.Result property to set the field value
And if you've got bookmarks set up, this works in a similar way.

Example code (pelase set a reference to Word before coding: go to TOOLS>REFERENCES in Access's module window & check the option Micorsoft Word x.0 Object Model where x = 8 for office 97, 9 for office2k, and 10 for office xp):

Dim l_appWord As Word.Application
Dim l_docWordDoc as Word.Document
Dim l_rsRecordset As Recordset

'Launch Word & open doc
Set appWord  = CreateObject("Word.Application")
Set l_docWordDoc = appWord.OpenDocument("FullPathAndFileNameToYourWordDocument")

'Open recordset
Set l_rsRecordset = CurrentDB.OpenRecorset("SELECT * FROM PutYourTableNameHere")
Do Until l_rsRecordset.EOF
  docWordDoc.Fields("FieldName1").Result = l_rsRecordset("FieldName1")
  docWordDoc.Fields("FieldName2").Result = l_rsRecordset("FieldName2")
  docWordDoc.SaveAs "PathHere" & l_rsRecordset("FileFieldName")
  l_rsRecordset.MoveNext
Loop

'Release objects & quit word
docWordDoc.Close
Set docWordDoc = Nothing
appWord.Quit
Set appWord = Nothing

HTH

Cheers
Nikki

 

[MatthewGB]

Thanks for all the info Nikki.

I understand the method with you are transferring the information, however I have come up with a problem along the way. I get a error saying that the OpenDocument method is not available for l_appWord. I have loaded the Word 8.0 Object Library (i'm using Word 97).

Also, is the line:
Set appWord = CreateObject("Word.Application&quot
supposed to open up an instance of word? Nothing seems to happen at this line.

Maybe I need other library references??
Any further assistance would be greatly appreciated.

Cheers,
Matt

 

[MatthewGB]

Nikki,

I realise what is happeing now with the CreateObject code. It starts an instance of word in the Process section of Task Manager. How do i go about making the instance of Word and the current document visible to the user? The reason I want this to happen is because I am basically transferring info from access to a Letter Report document. Access fills in stuff like address, name etc, and the user types in the letter.

I want the fields to be filled from Access but the user manually shuts down the document. So I can omit the Close and Quit statements.

I noticed that the .SaveAs method automatically saves to the position and name you specify. How do you get the code to bring up the SaveAs dialog box instead? This is so the user can change to whatever path they like, but the filename is determined by the field in Access.

Thanks for your time
Matt

 

[Nikita6003]

Hiya Matt (we meet again!)

If you want the app visible take a look at the WordApp object - it has a property

Visible

. So just add the line

appWord.Visible = True

wherever you want your user to see the Word app in the code execution.

If you want them to take a proper look you'll probably have to take out the appWord.Quit line - in the example code that'll close the Word app window almost as soon as it's opened ...

Cheers
Nikki

 

[MatthewGB]

Hey Nikki,

If I could give you another star i would! Thanks for all your help. Now the only thing left I need to do with this thing is the Saving issue. As I mentioned in my previous post, when the user goes to save, I would like for the SaveAs dialog box to appear, with the default file name as one of the fields on the document.

It would be acceptable for the SaveAs box to appear as soon as the fields are filled in from Access, but i would prefer it to occur after the user has typed out the letter, and then when they hit save or saveas, the above condition occurs.

Is it possible to control this from within Access, or would I have to modify the Document to trap the Save and SaveAs button events?

Cheers
Matt

 

[Nikita6003]

Nooo problem - just change to this:

    Dim l_appWord As Word.Application
    Dim l_docWord As Word.Document
    Dim l_dlgDialog As Object
    
    Set l_appWord = CreateObject("Word.Application")
    Set l_docWord = l_appWord.Documents.Open("Y:\Data\WinWord\emoticons.doc")
    
    'use if you want your users to see the app during code processing
    l_appWord.Visible = True
    
    Set l_dlgDialog = l_appWord.Dialogs(wdDialogFileSaveAs)
    'Change Hello.doc" to the Result of your Word Field
    l_dlgDialog.Name = "hello.doc"
    l_dlgDialog.Show
    
    ' ...your mailmerge code here ...
    Set l_appWord = Nothing

Cheers
Nikki

ps - thanks for the virtual star, anyway

 

[Nikita6003]

Oh, and to control the SaveAs option you'll have to sneakily add your own SaveAs function to the File menu. You can control the Toolbars pretty much at will, using the OnWorkbookOpen event to set your version & the OnClose to revert back to standard. Just remember there'll be more users like me who use [F12] to saveas, so change this shortcut key to point at your macro as well.

Dunno if it's possible to trap the BeforeDisplay or somesuch event of a dialog - couldn't find it, anyway - but if you need an example of the above send me a quick note on Nikita6003@hotmail.com for an example word doc where the toolbar is manipulated to replace the Protection ... option with my own version

Cheers again
Nikki