Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    35
    Thanks
    2
    Thanked 1 Time in 1 Post

    Unhappy Excel datasource mailmerge querystring problem

    I need to create a condition where a record is merged based on whether a range of cells are not blank. i.e. record is merged if any cell in range has a value. At present this works:- 'Where Cell1 is not blank or Cell2 is not blank or Cell3 is not blank'. My whole querystring is coming close to 255 length limit and I need to increase the number of cells being tested. The relevant columns are contiguous, the cell values are custom accounting ( 000.00) and each column has a header (fieldname). I can set a range name before the mail merge gets called easiliy enough. What I'm looking for is something like 'Where '" & RangeName & "' is not blank " etc., etc., and of course that range is the intersect of the relevant columns and record under scrutiny. The merge is called from a Word template and excel/word, although happy using the column header field names, doesn't seem to like rangenames.
    Ideas anyone??

    Thanks

    DougieW

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Is this being done with VBA, or conventionally?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Doug,

    I am assuming that "merge if any of the cells are not blank" refers to a concatenation of the cells in the range. If so, this can easily be achieved with a user defined function.

    In a standard module, copy/paste the following function

    In a standard module:
    Code:
    Public Function CONCAT(rng As Range, sep As String) As String
    Dim cell As Range, strng As String
    For Each cell In rng
        If cell <> "" Then
            If strng = "" Then
                strng = Format(cell, Chr(163) & " " & "##0.00")
            Else:
                strng = strng & sep & Format(cell, Chr(163) & " " & "##0.00")
            End If
        End If
    Next cell
    CONCAT = strng
    End Function
    In the cell where you want to merge across, enter the formula =CONCAT(Range, Separator) where
    Range= range of cells to merge
    Separator= character to separate values

    example: in cell L2, enter the formula =CONCAT(B2:K2,", ") which will merge all the cells in B2 to K2 if one of the cells has a value and using the comma + <space> separator. Then, copy down. The concatenation will be formatted using the "" symbol and accounting format. Chang the separator to any character you like (see image).

    HTH,
    Maud

    Doug1.png
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Maudibe: With your workbook, just using the formula:
    =B2&C2&D2&E2&F2&G2&H2&I2&J2&K2
    in L2 and copying down as far as needed is all Doug should need to test whether any cell in B:K is populated. The mailmerge query could then just test whether column L is empty.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I guess I was under the impression that the resulting string was to be used in some fashion. After re-reading the post, I see that it is merely used as a test.

    Thanks for pointing that out.

    Maud

Tags for this Thread

Posting Permissions

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