none
mailmerge from EXcel.xls data source with Cells that have comma seperated values in rows(records) RRS feed

  • Question

  • 0 down vote favorite Hi, I'm an absolute novice at this, but I've got an Excel'02 file with Cells in rows that have comma seperated values which I need for word mailmerge to split and add to newly inserted rows.... Now I've done this in Excel ( using .... Sub tst() Dim X As Variant X = Split(Range("A1").Value, ",") Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X) End Sub How do I get word to do just the same .... please? Also pls include the type of sub i should be using and how to reference anything like tables, cells, ranges etc many thanks Regds
    • Moved by Kee Poppy Tuesday, May 24, 2011 8:08 AM (From:Visual Basic General)
    Thursday, May 19, 2011 3:33 PM

Answers

  • Well, here's an outline and some starting point code, based on the example you gave.

    There are many ways you could approach this - I don't say this is the best, or necessarily even a particularly good one. It also needs error check and possibly other code to be added. 

    Since we are splitting up your data columns, we can't use MERGEFIELD fields to insert the values, so we have to do it another way. One way is to use { DOCVARIABLE } fields and populate Document Variables with the values you need.

    Let's suppose your sheet has columns with names CustName, Product, Qty and Price.

    In your Mail Merge Main Document, you'll have

    Cust Name: { MERGEFIELD CustName }

    then a table with 3 columns and 4 rows, laid out as follows:

    Product Qty Price

    { DOCVARIABLE "f1-{ SEQ f1 }" } { DOCVARIABLE "f2-{ SEQ f2 }" } { DOCVARIABLE "f3-{ SEQ f3 }" }

    (blank row)
    Total: { DOCVARIABLE "f3-total" }

    (i.e. "Total:" should be right-justified in the second cell of row 4)

    The fields like { DOCPROPERTY "f1-{ SEQ f1 }" } are nested fields - each pair of {} need to be the special field code braces that you can insert using ctrl-F9.

    During the merge, our MailMergeBeforeRecordMerge event code needs to:

     a. split the fields with multiple values and discover how many values there are per cell in the row. Let's suppose that in the current row there are 4 values

     b. add 3 copies of the 2nd table row, after row two

    At that point, for example, column 1 wil have DOCVARIABLE fields like this (once the { SEQ } fields have been resolved):

    { DOCVARIABLE "f1-1" }

    { DOCVARIABLE "f1-2" }

    { DOCVARIABLE "f1-3" }

    { DOCVARIABLE "f1-4" }

     c. so we also need to populate the Document variables f1-1, f1-2, f1-3 and f1-4 with the 4 values from the Product cell

    Our MailMergeAfterRecordMerge Event code needs to remove the rows we added

    So create a VBA Event module in your Mail Merge Main document, with the following content:

    '----------------

     

    ' The table number of the table we want to modify

    Private Const intTable As Integer = 1

    ' One of these for each database column name

    ' The names need to match the column names

    ' in the data source exactly - i.e., they

    ' are case-sensitive

    Private Const strColumnName1 As String = "Product"

    Private Const strColumnName2 As String = "Qty"

    Private Const strColumnName2 As String = "Price"

    ' One of these for each Document Variable name stem

    Private Const strVariable1 As String = "f1-"

    Private Const strVariable2 As String = "f2-"

    Private Const strVariable3 As String = "f3-"

    Private Const strDelimiter As String = ","

    ' The row we want to replicate

    ' and the number of rows not to delete

    ' at the beginning

    Private Const intRowToReplicate As Integer = 2

    ' The number of rows not to delete at the end

    Private Const intRowsToLeave As Integer = 2

     

    Public WithEvents App As Word.Application

     

    Private Sub App_MailMergeAfterRecordMerge(ByVal Doc As Document)

    Dim i As Integer

    With Doc

      For i = .Tables(intTable).Rows.Count - intRowsToLeave To intRowToReplicate + 1 Step -1

        .Tables(intTable).Rows(i).Delete

      Next

    End With

    End Sub

     

    Private Sub App_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)

     

    Dim i As Integer

    ' One of these for each column

    Dim strColumnValues1() As String

    Dim strColumnValues2() As String

    Dim strColumnValues3() As String

    ' one of these for each total

    Dim dblColumn3Total As Double

    With Doc

      With .MailMerge.DataSource

        strColumnValues1 = Split(.DataFields(strColumnName1).Value, strDelimiter)

        strColumnValues2 = Split(.DataFields(strColumnName2).Value, strDelimiter)

        strColumnValues3 = Split(.DataFields(strColumnName3).Value, strDelimiter)

      End With

      dblColumn3Total = 0#

      ' here, we are assuming all the columns we

      ' are splitting have the same number of values

      For i = LBound(strColumnValues1, 1) To UBound(strColumnValues1, 1)

        .Variables(strVariable1 & CStr(i + 1)).Value = strColumnValues1(i)

        .Variables(strVariable2 & CStr(i + 1)).Value = strColumnValues2(i)

        .Variables(strVariable3 & CStr(i + 1)).Value = strColumnValues3(i)

        dblColumn3Total = dblColumn3Total + CDbl(strColumnValues3(i))

      Next

      .Variables(strVariable3 & "total").Value = dblColumn3Total

      .Tables(intTable).Rows(intRowToReplicate).Range.Copy

      For i = LBound(strColumnValues1, 1) To UBound(strColumnValues1, 1) - 1

        .Tables(intTable).Rows(intRowToReplicate).Range.Paste

      Next

      .Tables(intTable).Range.Fields.Update

    End With

    Cancel = False

     

    End Sub

    '----------------

    In the ThisDocument module of the document, put the following code:

    '----------------

     

    Dim X As New EventClassModule
    Sub EnableEvents()
      Set X.App = Word.Application
    End Sub
    Sub DisableEvents()
      Set X.App = Nothing
    End Sub

    '----------------

     

    Save the document.
    In the Mailings tab, attach the data source.
    Run the EnableEvents Sub
    In the Mailings tab, merge to a new document
    Run the Disable Events Sub.
    See how it goes.


    Peter Jamieson
    • Marked as answer by Bruce Song Thursday, June 2, 2011 10:59 AM
    Monday, May 30, 2011 9:43 PM

All replies

  • Hello,

    Why not using OleDB?

    Look at this sample on our website

    http://www.vb-tips.com/CSVDataSet.aspx


    Success
    Cor
    Monday, May 23, 2011 5:14 AM
  • Hi tomcatonnet99,

    I am moving this thread to Word for Developers forum for getting better support.

    Thanks for your understanding!


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 24, 2011 8:08 AM
  • Hi Cor,

    Ok, I'm not quite sure how to implement the code for just a few cells or a row which are comma delimited... How exactly would I get that done with OLeDB

    I would rather a straightforward method of direct coding in VB to get that job done...

    cheers

    PS: Would appreciate any help plz

     

     


    Tom
    Tuesday, May 24, 2011 1:37 PM
  • Dear Domcatonnet99,

    You can use OleDb to read the data from the xls file even it is comma seperated. Please refer to this article:

    http://www.switchonthecode.com/tutorials/csharp-tutorial-using-the-built-in-oledb-csv-parser

    After getting the data, you can use word PIA to insert these values to the word document.

    Hope this helps.

    Regards,


    Be happy.
    Wednesday, May 25, 2011 7:11 AM
  • In the context of a Word mailmerge, you would probably need to use Word's MailMerge events, specifically the MailMergeBeforeRecordMerge event, to get the relevant field value from (say) Doc.Mailmerge.Datasource.Datafields, then split it.

    What's rather more difficult is what you then do with it. that depends entirely on how your mailmerge main document is structured. Suppose, for example, that in one row in your data, a field has three separate comma-delimited values. Then maybe in your output you want to have three copies of a table row, but with a different value in one column in each row. Or maybe you want to duplicate the row, but /only/ have data in that column in the 2nd and 3rd rows. Each of those scenarios would require different coding (you might, for example, bookmark the first table row, then copy/paste it in your VBA code). You might also have to deal with a case where you have two columns, each of which contains a different number of values. Then you'd have to work out the maximum number of extra rows to add, and get the right data into the right rows.

    Alternatively, it might be easier to create a new data source from the existing one, so you have one row for each value (if there's only one column), then design your mail merge main document around that. Word isn't really geared for that, but the methods described/referenced at http://www.gmayor.com/ManyToOne.htm may help.

    Or you can consider "rolling your own" merge, directly controlling Word from Excel (say).

     


    Peter Jamieson
    Monday, May 30, 2011 1:37 PM
  • In the context of a Word mailmerge, you would probably need to use Word's MailMerge events, specifically the MailMergeBeforeRecordMerge event, to get the relevant field value from (say) Doc.Mailmerge.Datasource.Datafields, then split it.

    What's rather more difficult is what you then do with it. that depends entirely on how your mailmerge main document is structured. Suppose, for example, that in one row in your data, a field has three separate comma-delimited values. Then maybe in your output you want to have three copies of a table row, but with a different value in one column in each row. Or maybe you want to duplicate the row, but /only/ have data in that column in the 2nd and 3rd rows. Each of those scenarios would require different coding (you might, for example, bookmark the first table row, then copy/paste it in your VBA code). You might also have to deal with a case where you have two columns, each of which contains a different number of values. Then you'd have to work out the maximum number of extra rows to add, and get the right data into the right rows.

    Alternatively, it might be easier to create a new data source from the existing one, so you have one row for each value (if there's only one column), then design your mail merge main document around that. Word isn't really geared for that, but the methods described/referenced at http://www.gmayor.com/ManyToOne.htm may help.

    Or you can consider "rolling your own" merge, directly controlling Word from Excel (say).

     


    Peter Jamieson

    Hi Peter,

    Many thanks for your input, truly appreciate it....

    Well as regds the various probably outputs you mentioned... allow me to clarify .. whilst every row in excel with cells bearing CSV may be different in their number of comma delimited values; they will have exactly the same correspondence right through the row...

    Now the question is how do I get Word (by using VBA) to

    1) Split the cell values ..

    2) based on the count of above, INSERT new rows (as required ) - and this needs to be done only for the first column (as the rest would already be taken care of - every cell in a particular row has exactly the same number of corresponding comma delimited values...)

    3) Split the cell values for the rest of the columns into already created (Inserted) rows..

    e.g:

    EXCEL : (example of a single row... with CSV cells)

    Cust Name   Product            Qty             Price 

    xyz ltd         A1,B1,C1         10,20,10      5.50,6.50,7.50 

     

    to Appear in Word Mail merge as:

    Cust Name: xyz ltd

    Product            Qty               Price

    A1                   10                  5.50

    B1                   20                  6.50

    C1                   10                 7.50

                               TOTAL:     19.50

    (should automatically delete any rows in excess of requirement for the next record.... and then all I intend to do is either merge print or merge to another file ...)

     

    PLS all help here would be greatly appreciated...

    Regds

     

    Tom

     

     

     

     


    Tom
    Monday, May 30, 2011 3:32 PM
  • Well, here's an outline and some starting point code, based on the example you gave.

    There are many ways you could approach this - I don't say this is the best, or necessarily even a particularly good one. It also needs error check and possibly other code to be added. 

    Since we are splitting up your data columns, we can't use MERGEFIELD fields to insert the values, so we have to do it another way. One way is to use { DOCVARIABLE } fields and populate Document Variables with the values you need.

    Let's suppose your sheet has columns with names CustName, Product, Qty and Price.

    In your Mail Merge Main Document, you'll have

    Cust Name: { MERGEFIELD CustName }

    then a table with 3 columns and 4 rows, laid out as follows:

    Product Qty Price

    { DOCVARIABLE "f1-{ SEQ f1 }" } { DOCVARIABLE "f2-{ SEQ f2 }" } { DOCVARIABLE "f3-{ SEQ f3 }" }

    (blank row)
    Total: { DOCVARIABLE "f3-total" }

    (i.e. "Total:" should be right-justified in the second cell of row 4)

    The fields like { DOCPROPERTY "f1-{ SEQ f1 }" } are nested fields - each pair of {} need to be the special field code braces that you can insert using ctrl-F9.

    During the merge, our MailMergeBeforeRecordMerge event code needs to:

     a. split the fields with multiple values and discover how many values there are per cell in the row. Let's suppose that in the current row there are 4 values

     b. add 3 copies of the 2nd table row, after row two

    At that point, for example, column 1 wil have DOCVARIABLE fields like this (once the { SEQ } fields have been resolved):

    { DOCVARIABLE "f1-1" }

    { DOCVARIABLE "f1-2" }

    { DOCVARIABLE "f1-3" }

    { DOCVARIABLE "f1-4" }

     c. so we also need to populate the Document variables f1-1, f1-2, f1-3 and f1-4 with the 4 values from the Product cell

    Our MailMergeAfterRecordMerge Event code needs to remove the rows we added

    So create a VBA Event module in your Mail Merge Main document, with the following content:

    '----------------

     

    ' The table number of the table we want to modify

    Private Const intTable As Integer = 1

    ' One of these for each database column name

    ' The names need to match the column names

    ' in the data source exactly - i.e., they

    ' are case-sensitive

    Private Const strColumnName1 As String = "Product"

    Private Const strColumnName2 As String = "Qty"

    Private Const strColumnName2 As String = "Price"

    ' One of these for each Document Variable name stem

    Private Const strVariable1 As String = "f1-"

    Private Const strVariable2 As String = "f2-"

    Private Const strVariable3 As String = "f3-"

    Private Const strDelimiter As String = ","

    ' The row we want to replicate

    ' and the number of rows not to delete

    ' at the beginning

    Private Const intRowToReplicate As Integer = 2

    ' The number of rows not to delete at the end

    Private Const intRowsToLeave As Integer = 2

     

    Public WithEvents App As Word.Application

     

    Private Sub App_MailMergeAfterRecordMerge(ByVal Doc As Document)

    Dim i As Integer

    With Doc

      For i = .Tables(intTable).Rows.Count - intRowsToLeave To intRowToReplicate + 1 Step -1

        .Tables(intTable).Rows(i).Delete

      Next

    End With

    End Sub

     

    Private Sub App_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)

     

    Dim i As Integer

    ' One of these for each column

    Dim strColumnValues1() As String

    Dim strColumnValues2() As String

    Dim strColumnValues3() As String

    ' one of these for each total

    Dim dblColumn3Total As Double

    With Doc

      With .MailMerge.DataSource

        strColumnValues1 = Split(.DataFields(strColumnName1).Value, strDelimiter)

        strColumnValues2 = Split(.DataFields(strColumnName2).Value, strDelimiter)

        strColumnValues3 = Split(.DataFields(strColumnName3).Value, strDelimiter)

      End With

      dblColumn3Total = 0#

      ' here, we are assuming all the columns we

      ' are splitting have the same number of values

      For i = LBound(strColumnValues1, 1) To UBound(strColumnValues1, 1)

        .Variables(strVariable1 & CStr(i + 1)).Value = strColumnValues1(i)

        .Variables(strVariable2 & CStr(i + 1)).Value = strColumnValues2(i)

        .Variables(strVariable3 & CStr(i + 1)).Value = strColumnValues3(i)

        dblColumn3Total = dblColumn3Total + CDbl(strColumnValues3(i))

      Next

      .Variables(strVariable3 & "total").Value = dblColumn3Total

      .Tables(intTable).Rows(intRowToReplicate).Range.Copy

      For i = LBound(strColumnValues1, 1) To UBound(strColumnValues1, 1) - 1

        .Tables(intTable).Rows(intRowToReplicate).Range.Paste

      Next

      .Tables(intTable).Range.Fields.Update

    End With

    Cancel = False

     

    End Sub

    '----------------

    In the ThisDocument module of the document, put the following code:

    '----------------

     

    Dim X As New EventClassModule
    Sub EnableEvents()
      Set X.App = Word.Application
    End Sub
    Sub DisableEvents()
      Set X.App = Nothing
    End Sub

    '----------------

     

    Save the document.
    In the Mailings tab, attach the data source.
    Run the EnableEvents Sub
    In the Mailings tab, merge to a new document
    Run the Disable Events Sub.
    See how it goes.


    Peter Jamieson
    • Marked as answer by Bruce Song Thursday, June 2, 2011 10:59 AM
    Monday, May 30, 2011 9:43 PM
  • many thanks indeed, I'm sorry I haven't yet tried it out but will do so asap and get back to you ....Much appreciate your time and effort ...

     

    Regds

     

    Tom


    Tom
    Tuesday, May 31, 2011 12:11 PM