Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mail merge (?) on certain rows of sheet with access records included too?

    I have what seems to me like a fairly complicated task I'm trying to achieve...

    I have a spreadsheet (HK) that includes a list of room numbers (ColB) and tasks to perform that day (ColQ). I want to print off a page (currently a word doc), but only for the room numbers where the task is one of a few options ("Clean" or "Linen" or "Check"), and include on each instance the room number and task.

    To further complicate things, I also want to connect to an access db (MT) and add into each instance of the template/mail merge document records with a matching room number.

    I'd like to do all this from inside excel as the users are already printing opening and printing the HK spreadsheet, if possible.

    Anybody got some help on how to achieve all this?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi JonTom

    ..if you attached a sample extract of your Housekeeping spreadsheet, together with an Excel-format extract of some of the Access db records, it would make it much easier to assist you.

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    JT,

    The following code will look for room numbers in column B that have a matching task in Column Q of "Clean" or "Linen" or "Check". The rooms and tasks will be assigned to the array variables s and t respectively. There can be as many rooms as you want and the arrays will grow as you add more. When it has completed its search, the spreadsheet will minimize, the macro will open a Word document called "Mail merge.docm", and then run a macro located in the Word document.

    The Word document has two bookmarks called Room and Action that will indicate where to place the room number and the task passed from Excel. These bookmarks can be placed anywhere in the document. The Macro "InsertAtBookmark" receives the passed values, finds the bookmarks and inserts the strings. It then prints the document. I have placed a 10 sec pause to allow for the printing before it loops and receives the second set of parameters. It will continue to loop until Excel has sent all the rooms that have met the criteria. Each room and task will have its own printed sheet. The Word document will then close and the spreadsheet is re-maximized.

    As far as connecting to Access, I will pass the ball to someone more knowledgeable.
    HTH,
    Maud

    MailMerge1.png MailMerge2.png

    Spreadsheet standard module:
    Code:
    Public Sub RoomTask()
    On Error GoTo ErrorHandler
    'DELCARE  AND SET VARIABLES
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim s() As Variant
    Dim t() As Variant
    Dim cell As Range
    Dim rng As Range
    Dim I As Integer
    Set rng = [b2:b16]
    Set WordApp = CreateObject("Word.Application")
    Set WordDoc = WordApp.Documents.Open("C:\Users\Maudibe\Desktop\Mail merge.docm")
    I = 0
    '-------------------------------------------
    'FIND ROOMS WITH SPECIFIC TASKS
    For Each cell In rng
        ReDim Preserve s(0 To I)
        ReDim Preserve t(0 To I)
        With cell.Offset(0, 15)
        If .Value = "Clean" Or .Value = "Check" Or .Value = "Linen" Then
            s(I) = cell.Value
            t(I) = .Value
            I = I + 1
        End If
        End With
    Next
    Application.WindowState = xlMinimized
    '------------------------------------------
    'RUN WORD MACRO- PASS ROOM AND TASK
    WordApp.Visible = True
    For I = 0 To UBound(s)
        WordApp.Run "InsertAtBookmark", s(I), t(I)
        WordDoc.PrintOut
        'PAUSE FOR PRINTING
        ti = Timer
        Do While Timer < ti + 10
        DoEvents
        Loop
    Next I
    '------------------------------------------
    'CLOSE WORD, NO SAVE, CLEANUP
    WordApp.Quit SaveChanges:=wdDoNotSaveChanges
    Set WordApp = Nothing
    Application.WindowState = xlMaximized
    Exit Sub
    ErrorHandler:
    MsgBox "Make sure the Word document is closed"
    End Sub
    Word document:
    Code:
    Public Sub InsertAtBookmark(R As String, A As String)
    'DECLARE  AND SET VARIABLES
    Dim RRng As Word.Range
    Dim ARng As Word.Range
    Set RRng = ActiveDocument.Bookmarks("Room").Range
    Set ARng = ActiveDocument.Bookmarks("Action").Range
    '---------------------------------
    'INSERT ROOM AND TASK AT BOOKMARKS
    RRng.Text = R
    ARng.Text = A
    '---------------------------------
    'RECREATE BOOKMARKS THAT ARE OVERWRITTEN
    ActiveDocument.Bookmarks.Add "Room", RRng
    ActiveDocument.Bookmarks.Add "Action", ARng
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-08-14 at 22:15.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    JT,

    In the above post, you will need to change the path in the line:

    Set WordDoc = WordApp.Documents.Open("C:\Users\Maudibe\Desktop\M ail merge.docm")

    to the correct path of where you placed the files, as well as adjusting the range in the line:

    Set rng = [b2:b16]


    to the range of the room numbers. Since the Word file is not saved when the macro closes it, it is always ready for the next use. Change the wording/sentences/paragraph to the way you want the document to look. Just make sure you have 2 bookmarks called Room and Action located at the point of desired insertion.

    Without seeing an example, I am hoping that this is what you are looking for.

    Maud
    Last edited by Maudibe; 2013-08-18 at 16:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •