opening up a word document from a button

[Dazzla]

I have a form with various details on clients. I have 6 different letters that need to pull off different fields from the client form. Is it possible to have a button (for each letter) that when when clicked will open up the word document for the specific client with the fields on their poulated, such as name, ammount owed etc.

Mail merge does not do the trick as it brings back all the clients in the database and not the specific one.

 

[daveUK]

I do something similar myself. I have a form with a listbox on it. The listbox displays all the records from a cuople of tables. Beside the listbox I have half a dozen or so command buttons, which, when pressed, will insert the selected persons details and insert them into a Word document.

First of all you need to select the fields on the word documents, which you want to be completed and insert bookmarks.

Then you can insert the following code behind one of the command buttons. I've just used the example of inserting the surname and forename into bookmarks on the document called Surname and Forename.

Dim objWord As Word.Application
Dim ctlList As Control, varItem As Variant
Dim Surname As String, Forename As String, 

Set ctlList = Forms!NameOfForm!NameOfListbox

'Make sure a value is selected from the listbox
If ctlList.ItemsSelected.Count = 0 Then
    MsgBox "You must select a person from the listbox",
Else
    For Each varItem In ctlList.ItemsSelected
           
        'Store the selected value in seperate strings
        Surname = ctlList.Column(0) 'First column in listbox
        Forename = ctlList.Column(1) 
                
    Next varItem


Set objWord = CreateObject("Word.application")

With objWord
    .Visible = True
    
    .Documents.Open "Filepath of Word Document"
          
    'Move to each of the bookmarks and insert the selected values
    .ActiveDocument.Bookmarks("Surname").Select
    .Selection.Text = Surname
         
    .ActiveDocument.Bookmarks("Forename").Select
    .Selection.Text = Forename
        
End With
End If

Set objWord = Nothing

Exit_Err_Form_Click:
    Exit Sub

Err_FMed23_Click:
    If Err.Number = 5174 Then 'document cannot be found
        MsgBox "The Word Document cannot be found." 
        objWord.Visible = False
        Set objWord = Nothing
    ElseIf Err.Number > 0 Then
        MsgBox Err.Description
    End If

HTH

 

[ansentry]

daveUK,

Is the list box meant to be used as multi-select or single? If I multi-select the list box display say 3 selections (that fine) word open and then is only 1 Document (the last one that I selected in the list box)

What does this do?

For Each varItem In ctlList.ItemsSelected

Also shouldn't this:

 .Documents.Open "C:\Temp\AccessTest.doc"

be

 .Documents.[B]Add[/B] "C:\Temp\AccessTest.[B]dot[/B]"

If you use a template (.dot) then when the document is saved it does not save over your .doc .

I have been able to print a report using multi-select, but have not a yet been able to create multiple documents. I have been able to merge selected records, but would like to try and get multi select list box to work. I might add that I have obtained the code and ideas from these forums and made changes to suit my situation.

This is why I am interested in your post; it is not meant a criticism

 

[daveUK]

John,

What does this do?

For Each varItem In ctlList.ItemsSelected

It runs through each the listbox checking, which item is selected. It's not necessary for this example as only one item in the listbox can be selected, but I was feeling lazy when I wrote the code and copied bits of it from a multi-select listbox that I had used elsewhere in the programme.

Also shouldn't this:

 .Documents.Open "C:\Temp\AccessTest.doc"

be

 .Documents.Add "C:\Temp\AccessTest.dot"

If you use a template (.dot) then when the document is saved it does not save over your .doc .

I agree you should save the word document as a template, but for some reason I cannot get the information to insert into a Word template, so I create the Word document as read-only.

 

[ansentry]

Dave,

Thanks for your reply, would you like a copy of a sample db that I have (it relates to opening word and bookmarks etc from both list and singleform)

It may help you with your "template" problem. I don't know why a template won't work for you. What if any error do you get?

 

[Dazzla]

Thats great and does the job thanks, but just to be awkward would the same code work for opening a document template or would it be different. Problem is that if users open it up in word and then save it. They overwrite the bookmarks.

 

[ansentry]

Dazzla,

If you read what I posted to DaveUK you will see that I use code different to him.

Here is a copy of my code I use to select a customer from a listbox and then create the document.

Declare the follwing
    Dim objWord As Word.Application
    Dim ctlList As Control
    Dim [B]strCusDetails[/B] As String
    
    
    If Me.[B]lstCusGeneric[/B].Value = "" Then
    
    MsgBox " No Record selected from list", vbCritical, "Selection Error"
    
    Me.[B]lstCusGeneric[/B].SetFocus
    
    Else
    
Set ctlList = [B]lstCusGeneric[/B]

[B]strCusDetails[/B] = ctlList.Column(1)


       'Set word as an application and make it invisible
         Set objWord = CreateObject("Word.Application")
         objWord.Visible = False 'True is visible
        
       'path and name of the template your are using.
         objWord.Documents.Add ("[B]C:\Temp\AccessTest.dot[/B]")
     
       'This is for the bookmark that you created in the template
         objWord.ActiveDocument.Bookmarks("[B]bmCusDetails[/B]").Select
                      
       'This is the field in access that containts the data that has to be entered at the
       'bookmark
         objWord.Selection.Text = [B]strCusDetails[/B]
         
        
'Word (or the document that you created with the template, will now open)
    objWord.Visible = True
    
    Set objWord = Nothing


End If


    
End Sub

What is in bold you will have to change to suit your situation

Just a couple of pointers ( If you don't mind) when you name your bookmarks in the word document put bm or bmk in front to identify them. make them the similar to your control names e.g Access=txtCusDetails Word= bmCusDetails.

Using a list box means that you are not using the control names to equal the bookmarks.

You stated in your first post:

Mail merge does not do the trick as it brings back all the clients in the database and not the specific one.

If you don't mind post the code that you are using, I would like to have a look at the problem.

 

[Dazzla]

Its ok it works now so thanks for that, Just needed to know how to open a template rather than a document. Should of really read the message further up