Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for the help. I have really good VBA code that allows me to select a document or many, mailmerge and preview or print documents. What I am trying to do differently is pull in the data from a multi-row(record) query and fill a table in a word document. I probably haven't yet grasped the essentials. I could easily add the mergefield names, but I'm not sure that would get me 25 rows of data or more than one row of data. I have looked at the following references but don't see how they work
    "http://windowssecrets.com/forums/sho...ngs-(2000-2010)
    or
    http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    For a recent, worked example, see the attachment to post #13 at: http://www.msofficeforums.com/mail-m...nt-dollar.html"

    This is the good I use to do normal mailmerge, which is based on an access query with one row(record) of data, I want to be able to pull in multiple rows(records), It would look like a table.
    'Itterate over FormName looking for Checkx boxes where x=1..n.
    '
    'For each check box found, print the document in the checkbox's
    'StatusText property using OLE and Word.
    '
    Function PreviewDocs(FormName$)
    On Error GoTo PreviewDocs_ErrHandler
    'Variables for form controls
    Dim Frm As Form
    Dim iCurrentControl As Integer
    Dim bControlExists As Boolean
    Dim bControlSelected As Boolean
    Dim szCheckBox As String
    Dim szConnection As String

    'Variables for Word
    Dim o_App As Object
    Dim szWordPath As String
    Dim szDocumentFilename As String
    Dim szDocumentLabel As String
    Dim szFocus As String * 100
    Dim szTempFileName As String
    Dim o_Doc As Object

    'Praveenb: For using OLE in mail merge
    Dim szTempTextFilePath As String
    Dim szQueryName As String

    On Error Resume Next
    'Praveenb - Adding to error handler
    'On Error GoTo PreviewDocs_ErrHandler

    DoCmd.Hourglass True

    'Link to word, starting if required, and get Word's path
    Application.Echo True, "Opening Word link..."

    Set o_App = CreateObject("Word.Application")
    szWordPath = GetDBPath() & "Docs\"

    'An error occured. Word could not be opened
    If szWordPath = "" Then
    GoTo PreviewDocs_ErrHandler:
    Else 'Word started. Now itterate over FormName
    bControlExists = True
    iCurrentControl = 1
    bControlSelected = False
    'Stop the loop when there are no more controls OR
    'a shift key is held down. 16=vk_Shift.
    Do While bControlExists 'And (GetASyncKeyState(16) >= 0)
    If iCurrentControl < 233 Then
    szCheckBox = "Check" & CStr(iCurrentControl)

    Else
    Exit Do
    End If
    'Try to get the check state for CurrentControl

    bControlSelected = Forms(FormName$)(szCheckBox).Visible _
    And Forms(FormName$)(szCheckBox)

    If bControlSelected Then 'Control was selected. Print it
    'Lookup the filename of the document
    'Get the LineIdent from the form
    szTempFileName = Forms(FormName$)("Name" & CStr(iCurrentControl)).caption
    'Do 'Strip everthing except the last line from the string
    'Debug.Print TempFileName
    szTempFileName = Right(szTempFileName, _
    Len(szTempFileName) - InStr(szTempFileName, Chr$(10)))
    'Loop Until IsNull(TempFileName) Or (InStr(TempFileName,
    'Chr$(10)) = 0)
    If Len(szTempFileName) <> 0 Then
    szDocumentFilename = szTempFileName
    szDocumentLabel = Left$(Forms(FormName$)("Name" & CStr(iCurrentControl)).caption, _
    InStr(Forms(FormName$)("Name" & CStr(iCurrentControl)).caption, Chr$(13)) - 1)
    szConnection = DLookup("[Queryname]", "TDocuments", "[Filename] Like'" & szTempFileName & "'")

    'Debug.Print DocumentFileName$, DocumentLabel$
    Application.Echo True, "Opening document " & szDocumentLabel & "..."

    If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath
    With o_App

    Set o_Doc = .Documents.Add(szWordPath & szDocumentFilename, False)
    If CInt(o_App.Version) < 10 Then
    'Word 97 and 2000
    o_Doc.Mailmerge.OpenDataSource Name:=GetDBFilenamep(), _
    ReadOnly:=False, Connection:=szConnection ', _
    OpenExclusive:=False, SubType:=wdMergeSubTypeWord2000
    Else
    'Word xp and higher
    ' o_Doc.Mailmerge.OpenDataSource Name:=GetDBFilenamep(), _
    ' ReadOnly:=False, Connection:=szConnection, _
    ' SubType:=8
    'Praveennb - getting the query name without the string "QUERY" as the TDocuments table currently has it.
    szQueryName = Replace(szConnection, "QUERY ", "")
    'Praveenb - Currently storing the temporary text file at the same location as the database.
    szTempTextFilePath = GetDBPath() & "/" & szQueryName & ".txt"
    'Praveenb Export the query result to a text file
    DoCmd.TransferText acExportDelim, , szQueryName, szTempTextFilePath, True
    'Praveenb - Perform mailmerge with the text file as the source
    o_Doc.Mailmerge.OpenDataSource _
    Name:=szTempTextFilePath, _
    ReadOnly:=True
    End If
    o_Doc.Mailmerge.Destination = 0
    'o_Doc.MailMerge.Destination = wdSendToNewDocument
    .Visible = True
    .Activate
    .WindowState = 1
    '.WindowState = wdWindowStateMaximize
    '.Activate (to bring up pop-up, move this to after Visible for Win Vista, Win7 11.12.10)
    o_Doc.Mailmerge.Execute Pause:=False
    'o_Doc.CommandBars("Mail Merge").Visible = False
    'We must activate Word to allow the user to interact
    'with any ASK prompts or errors.

    'DoCmd.RunCommand acCmdAppMinimize
    End With
    o_Doc.Close 0

    'Praveenb - Delete the temporary text file - if it exists
    If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath
    'o_Doc.Close wdDoNotSaveChanges
    'o_App.Visible = True

    Application.Echo True, "Document has been merged " & szDocumentLabel & "..."

    Line1: ' Else

    Else 'An error occured. We must be past the end of the Checkxx boxes. End print run
    bControlExists = False
    End If
    End If
    iCurrentControl = iCurrentControl + 1
    Loop
    End If


    'o_App.Visible = True
    Application.Echo True, "Closing Word link..."

    'Set o_App = Nothing
    'o_App.Quit
    'o_App.Visible = True
    ' Exit Function
    PreviewDocs_Exit:
    On Error Resume Next
    'o_App.Quit
    Set o_App = Nothing
    'o_App.Quit
    'Praveenb - Delete the Temporary text file - if it exists
    If FileExists(szTempTextFilePath) Then Kill szTempTextFilePath

    Exit Function

    PreviewDocs_ErrHandler:
    Select Case Err.Number
    'Case 2465 happens when iterating over docs and the
    'iteration passes the 130 documents on the
    'FGenerateDocuments Form
    Case 4605
    GoTo Line1:
    Case 5151
    MsgBox "a document could not be found"
    GoTo Line1:
    Case Else
    MsgBox "Please report the following error to support:" & vbCr _
    & vbTab & Err.Number & vbCr & Err.Description, _
    vbCritical + vbOKOnly, C_MsgTitle
    Resume PreviewDocs_Exit
    Resume
    End Select


    End Function

  2. #17
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do see some benefit of using Mail Merge in some circumstances. For instance, a law firm may want to create boiler plates for contracts/agreements and insert data in the body of the form/Word Doc. Trying to do this with an Access report may be cumbersome as you type all of the text for the body in an expression of an unbound textbox. In other words, Mail Merge in a Word Doc affords expansion and contraction as you insert fields. The only way to do this in an Access report is to concatenate.

    So, anyway, I tried to take advantage of a connection from a Word Doc to an Access DB and import data to a table in Word. I am not seeing it. I was, however, successful using DAO from within Access.

    I used access to open a Word Doc, Create a table, and then populate the table with data from a query.

    I uploaded the sample here.
    https://www.accessforums.net/sample-...ble-45539.html

  3. #18
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    I have used the code that you sent me. I put it into a module and I can populate a Word document with the contents of a query. I am doing this on a Windowsxp machine using an Access 2000 database format. It seems to only find the document if I call it a docx. I keep all my documents in Word97-2003 format because I have customers on the whole range of Windows and Office. I can't think why it only responds to docx.

    I think I am at the beginning of quite a long road. I need to be able to format round the data with some headings, I need to be able to total some of the data and add the totals in. I need to be able to have code that will print the document and bring it to the screen to be previewed and edited. And eventually to include it in the other printing that I do so it is seamless to the user.

    But I have to say I'm thrilled to bits to have got this far. And I keep making progress everytime I sit down and work on it. Thanks very much for taking the trouble to put this together.

  4. #19
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It seems to only find the document if I call it a docx.
    docx is for 2007 and > Word docs. Use .doc for Word97-2003. Search the VBA for docx and replace with doc

    I need to be able to format round the data with some headings
    This text is written into the first row of the table and IIRC the VBA that writes the header to the table is, somewhat, separate from the rest

  5. #20
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    I meant to say that when I change the line of code to say .doc, it seems that it cannot find the document. It only seems to work if I say .docx in the code. But I will keep working on it and coax it into good behavior. Thanks for the other information, I will be working on that too.

  6. #21
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    My mistake, it works very nicely for .doc. Thanks.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query contents are deleted after exporting the query results
    By alfcee in forum Import/Export Data
    Replies: 6
    Last Post: 11-13-2012, 09:35 AM
  2. Replies: 5
    Last Post: 04-18-2012, 12:04 PM
  3. Replies: 3
    Last Post: 10-24-2011, 08:13 AM
  4. Replies: 0
    Last Post: 03-29-2011, 04:11 PM
  5. Replies: 2
    Last Post: 10-19-2006, 04:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums