Solved

Call a doc template

Posted on 2000-03-22
7
236 Views
Last Modified: 2008-02-01
I have a form in VB6.  I have a combo box that displays a list of forms (10 forms).  

I need to mail merge based on which form is selected.  

2 Questions:
1.  How do I loop thru the combo box to see which letter is selected?  (can only select one letter)

2.  When I click 'send letter' I have it hard coded at this time.  Text and address etc, all hard coded.  How do I call the specific path based on the selected letter?

For example, I select Doc 3 from the combo box, when I click the command button, I want the template for doc 3 to come up.  How?

Thanks in advance....
-A
0
Comment
Question by:ambulance
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 2646958
I assume that your ComboBox Style is 2 - Dropdown List.

1)  You can find out which item in the list is selected by using the ListIndex property.  If it is -1, then nothing has been selected.  Otherwise, you can get the List entry by using this to reference the ittems in the list:

DocName$ = cmbDocuments.List(cmbDocuments.ListIndex)


2)  Assuming that the values in your combobox are the actual document names, then just put DocName$ in your string.  I don't know how you are opening the letter (there is more than one way to do it), but if your code has something like "C:\My Documents\Document1.Doc", and the ComboBox contained "Document1" as the item you picked, then you could use:

"C:\My Documents\" & DocName$ & ".Doc"

Or

"C:\My Documents\" & cmbDocuments.List(cmbDocuments.ListIndex) & ".Doc"

in its place.
0
 

Author Comment

by:ambulance
ID: 2650291
I am right now trying to open a specific template when the button is clicked.  Template is named 'Appendix_C.dot'  

I hard coded the data to be merged.  Merge fields do exist.  This is the code......

The way I have it now...it's calling the documetn, but ONLY displaying the merged fields, not the text of the document.  Does this mean I will have to hard code each letter?

Please help!!!!

Private Sub cmdSendLetter_Click()

    Dim wrdSelection    As Word.selection
    Dim wrdMailMerge    As Word.MailMerge
    Dim wrdMergeFields  As Word.MailMergeFields
    Dim StrToAdd        As String
    Dim DocName$
   
    On Error GoTo Error_Handler
   
    DocName$ = cbxLetter.List(cbxLetter.ListIndex)
   
    Screen.MousePointer = vbHourglass
       
    ' Create an instance of Word  and make it visible
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
   
    ' Add a new document
    Set wrdDoc = wrdApp.Documents.Add
    wrdDoc.Select
    Set wrdSelection = wrdApp.selection
    Set wrdMailMerge = wrdDoc.MailMerge
   
    ' Create MailMerge Data file
    CreateMailMergeDataFile
   
   
   
    ' Create a string and insert it into the document
    'StrToAdd = "Department of Agriculture" & vbCr & "Pet Animals Care and Facility"
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    'wrdSelection.TypeText StrToAdd
    'InsertLines 4   ' Insert merge data
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphLeft

    Set wrdMergeFields = wrdMailMerge.Fields
    wrdMergeFields.Add wrdSelection.Range, "FirstName"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "LastName"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "Address"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "City"
    wrdSelection.TypeText ", "
    wrdMergeFields.Add wrdSelection.Range, "State"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "Zip"
   
    'InsertLines 2
   
    ' Right justify the line and insert a date field' with the current date
   ' wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphRight
    'wrdSelection.InsertDateTime _
    'DateTimeFormat:="dddd, MMMM dd, yyyy", InsertAsField:=False
    'InsertLines 2
   
    ' Justify the rest of the document
    'wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphJustify
    'wrdSelection.TypeText "Dear Shelter Manager "
    'InsertLines 2
   
    ' Create a string and insert it into the document
    'StrToAdd = "After careful review of your request for exemption from license fees for the Pet Animal Care " & _
                "Facilities Act (PACFA) program, we find that we are able to accept your request to waive the fee " & _
                "for the 1999 license year.  Therefore, you are not required to send the fee with your yearly " & _
                "application for the PACFA license.  " & vbCr & vbCr & _
                "The review for exemption takes into consideration the level of your operation, staffing " & vbCr & _
                "requirements, and available budget to determine whether the fee is overly burdensome.  " & vbCr & vbCr & _
                "You will be required to petition the Commissioner in writing for this exemption on a yearly basis, " & _
                "prior to licensing.  Your petition must include a copy of your budget and total operating expenses " & _
                "of the previous year.  " & vbCr & vbCr & _
                "Please contact our office if you have any questions concerning your exemption. " & _
                "Sincerely,   " & vbCr & vbCr & vbCr & vbCr & _
                "Keith Roehr, DVM " & vbCr & _
                "PACFA Veterinarian"

    'wrdSelection.TypeText StrToAdd
   
    ' Go to the end of the document
    wrdApp.selection.GoTo wdGoToLine, wdGoToLast
     
    ' Where to send the document?'
    wrdMailMerge.Destination = wdSendToNewDocument
'    wrdMailMerge.Destination = wdSendToEmail
'    wrdMailMerge.Destination = wdSendToFax
'    wrdMailMerge.Destination = wdSendToPrinter
   
    ' --- Perform MAIL MERGE --- '
    wrdMailMerge.Execute False
   
    wrdDoc.PrintPreview
       
    ' Close the original form document
    wrdDoc.Saved = True
'    wrdDoc.Close False
   
    ' Notify user we are done.
    MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground
   
    ' Release References
    Set wrdSelection = Nothing
    Set wrdMailMerge = Nothing
    Set wrdMergeFields = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
   
    ' Cleanup temp file
'    Kill "C:\DataDoc.doc"
    Screen.MousePointer = vbDefault
Exit Sub

Error_Handler:
    Screen.MousePointer = vbDefault
    MsgBox "Error: " & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Mail Merge Error!"
End Sub



Public Sub InsertLines(LineNum As Integer)
    Dim iCount As Integer
    'INSERT BLANK LINES IN MS WORD
    For iCount = 1 To LineNum
        wrdApp.selection.TypeParagraph
    Next iCount
End Sub
   
Public Sub FillRow(Doc As Word.Document, Row As Integer, Text1 As String, Text2 As String, _
                   Text3 As String, Text4 As String, Text5 As String, Text6 As String)
                   
    With Doc.Tables(1)    ' Insert the data into the specific cell
        .Cell(Row, 1).Range.InsertAfter Text1
        .Cell(Row, 2).Range.InsertAfter Text2
        .Cell(Row, 3).Range.InsertAfter Text3
        .Cell(Row, 4).Range.InsertAfter Text4
        .Cell(Row, 5).Range.InsertAfter Text5
        .Cell(Row, 6).Range.InsertAfter Text6
    End With
End Sub

Public Sub CreateMailMergeDataFile()
    Dim wrdDataDoc  As Word.Document
    Dim X           As Integer
   
    ' Create a data source at C:\DataDoc.doc containing the field data
    wrdDoc.MailMerge.CreateDataSource Name:="C:\Pacfa\Documents\Templates\Appendix_C.doc", HeaderRecord:="FirstName, LastName, Address, City, State, Zip"
   
    ' Open the file to insert data
    Set wrdDataDoc = wrdApp.Documents.Open("C:\Pacfa\Documents\Templates\Appendix_C.doc")
    For X = 1 To 2
        wrdDataDoc.Tables(1).rows.Add
    Next X
   
    ' Fill in the data
    FillRow wrdDataDoc, 2, "James", "Brown", "4567 Main Street", "Grand Junction", "CO", "82541"
    FillRow wrdDataDoc, 3, "Jan", "Miksovsky", "1234 5th Street", "Vail", "CO", "85214"
    FillRow wrdDataDoc, 4, "Brian", "Valentine", "12348 78th Street  Apt. 214", "Denver", "CO", "85796"
   
    ' Save and close the file
    wrdDataDoc.Save
    wrdDataDoc.Close False
End Sub

0
 

Author Comment

by:ambulance
ID: 2650296
In the previous comment, I commented out the actual letter text so I can call a doc from the file.  

I haven't put in the .ListIndex bit to see which letter was selected from the drop down box.  Where would I put that?  I am not EXACTLY sure what the best location would be to do that.

This may not be hard for you experts, but I'm new and really struggling here.  Thanks so much for all your help!!!!

I will increase points!!
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 32

Accepted Solution

by:
bhess1 earned 150 total points
ID: 2650419
Okay, so you have your template documents (1.dot, 2.dot, ...)

Using your code above:

Private Sub cmdSendLetter_Click()

    Dim wrdSelection    As Word.selection
    Dim wrdMailMerge    As Word.MailMerge
    Dim wrdMergeFields  As Word.MailMergeFields
    Dim StrToAdd        As String
    Dim DocName$
     
    On Error GoTo Error_Handler
     
    DocName$ = cbxLetter.List(cbxLetter.ListIndex)
     
    Screen.MousePointer = vbHourglass
         
    ' Create an instance of Word  and make it visible
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True

    ' Instead of adding a document, open the template document    
    Set wrdDoc = wrdApp.Documents.Open "C:\My Documents\Templates\" & DocName, False, True, False, , , True   ' Change path as needed
        ' parameters are:
        '   ConfirmConversions = False  -- If doc needs to be converted, just do it
        '   ReadOnly = True   -- If you need to save the document, do a Save As
        '   AddToRecentFiles = False    -- you don't need all your templates in the recent files list
        '   PasswordDocument, PasswordTemplate - Ignored
        '   Revert = True    - if doc is already open, revert to saved version on disk and lose changes
       
    wrdDoc.Select
    Set wrdSelection = wrdApp.selection
    Set wrdMailMerge = wrdDoc.MailMerge
     
    ' Create MailMerge Data file
    CreateMailMergeDataFile
     
    Set wrdMergeFields = wrdMailMerge.Fields
    wrdMergeFields.Add wrdSelection.Range, "FirstName"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "LastName"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "Address"
    wrdSelection.TypeParagraph
    wrdMergeFields.Add wrdSelection.Range, "City"
    wrdSelection.TypeText ", "
    wrdMergeFields.Add wrdSelection.Range, "State"
    wrdSelection.TypeText " "
    wrdMergeFields.Add wrdSelection.Range, "Zip"
     
wrdSelection.ParagraphFormat.Alignment = wdAlignParagraphRight
     
    ' Go to the end of the document
    wrdApp.selection.GoTo wdGoToLine, wdGoToLast
       
    ' Where to send the document?'
    wrdMailMerge.Destination = wdSendToNewDocument
'    wrdMailMerge.Destination = wdSendToEmail
'    wrdMailMerge.Destination = wdSendToFax
'    wrdMailMerge.Destination = wdSendToPrinter
     
    ' --- Perform MAIL MERGE --- '
    wrdMailMerge.Execute False
     
    wrdDoc.PrintPreview
         
    ' Close the original form document
    wrdDoc.Saved = True
'    wrdDoc.Close False
     
    ' Notify user we are done.
    MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground
     
    ' Release References
    Set wrdSelection = Nothing
    Set wrdMailMerge = Nothing
    Set wrdMergeFields = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
     
    ' Cleanup temp file
'    Kill "C:\DataDoc.doc"
    Screen.MousePointer = vbDefault
Exit Sub

Error_Handler:
    Screen.MousePointer = vbDefault
    MsgBox "Error: " & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Mail Merge Error!"
End Sub



Public Sub InsertLines(LineNum As Integer)
    Dim iCount As Integer
    'INSERT BLANK LINES IN MS WORD
    For iCount = 1 To LineNum
        wrdApp.selection.TypeParagraph
    Next iCount
End Sub
     
Public Sub FillRow(Doc As Word.Document, Row As Integer, Text1 As String, Text2 As String, _
                   Text3 As String, Text4 As String, Text5 As String, Text6 As String)
                   
    With Doc.Tables(1)    ' Insert the data into the specific cell
        .Cell(Row, 1).Range.InsertAfter Text1
        .Cell(Row, 2).Range.InsertAfter Text2
        .Cell(Row, 3).Range.InsertAfter Text3
        .Cell(Row, 4).Range.InsertAfter Text4
        .Cell(Row, 5).Range.InsertAfter Text5
        .Cell(Row, 6).Range.InsertAfter Text6
    End With
End Sub

Public Sub CreateMailMergeDataFile()
    Dim wrdDataDoc  As Word.Document
    Dim X           As Integer
     
    ' Create a data source at C:\DataDoc.doc containing the field data
    wrdDoc.MailMerge.CreateDataSource Name:="C:\Pacfa\Documents\Templates\Appendix_C.doc", HeaderRecord:="FirstName, LastName, Address, City, State, Zip"
     
    ' Open the file to insert data
    Set wrdDataDoc = wrdApp.Documents.Open("C:\Pacfa\Documents\Templates\Appendix_C.doc")
    For X = 1 To 2
        wrdDataDoc.Tables(1).rows.Add
    Next X
     
    ' Fill in the data
    FillRow wrdDataDoc, 2, "James", "Brown", "4567 Main Street", "Grand Junction", "CO", "82541"
    FillRow wrdDataDoc, 3, "Jan", "Miksovsky", "1234 5th Street", "Vail", "CO", "85214"
    FillRow wrdDataDoc, 4, "Brian", "Valentine", "12348 78th Street  Apt. 214", "Denver", "CO", "85796"
     
    ' Save and close the file
    wrdDataDoc.Save
    wrdDataDoc.Close False
End Sub

---------------

Try this - this should do what you need.
0
 

Author Comment

by:ambulance
ID: 2650641
Adjusted points from 100 to 150
0
 

Author Comment

by:ambulance
ID: 2650642
You were a HUGE help!! Thank you so much!

-A
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2651059
No problem - glad I could help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now