Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Word VBA: Concatenation help please

Hi,

I have a series of nearly-identical Word 2003 tables (always 3 columns with slightly varying numbers of rows) from which I am extracting data for input into an Excel spreadsheet.

I am walking through the individual table cells using a For Each... Next structure:

            For Each oTbl In rng.Tables
                For Each oRow In oTbl.Rows
                    Select Case oRow.Cells(1).Range.Font.Size
                        ' ...

This is working well, although I now have a problem.  The 'varying' bit I mentioned above (in relation to the number of rows each table has) is in a section of anywhere between 1 and more rows.  The section is graphically delimited by two rows that each have the same Shading.BackgroundPatternColor property of 25%gray.  (The rows from which I want to pull and concatenate the data are all the normal white background.)

What I require:

     1. A way to work with only the rows between the two 25% gray rows for this particular concat.  (Define a Range?)
             -- FWIW.... Besides the 25% gray background, both rows have text in column 1: the 1st --   "Business Rules"  The 2nd-- "Privacy Rules"

     2. A way to extract the text from each row that is between the 2 mentioned above.  All the text is in the 2nd table column.

     3. The text from each row must be delimited, preferably with double pipes (||).

     4. This must be done keeping in mind that - up until this problem section - the text from each row in the Word table was placed into a separate cell in the Excel worksheet.  Now I have text that I want to concatenate from _at least_ one row in word and place into one cell in Excel.

Hope this is enough info.  I've pasted some of my code below.

Thanks,
Jens
For Each oTbl In rng.Tables
                For Each oRow In oTbl.Rows
                    Select Case oRow.Cells(1).Range.Font.Size
                        Case 9
                            Select Case CleansedText(oRow.Cells(1).Range.Text)
                                Case "Component Name"
                                    xlWS.Cells(intXLRow, intComponentNameColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                Case "Component Version"
                                    xlWS.Cells(intXLRow, intComponentVersionColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                Case "Data Element Name"
                                    xlWS.Cells(intXLRow, intDataElementNameColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                '...
                                Case "Business Rules"
                                    If oRow.Shading.BackgroundPatternColor = -1 Then
                                        If oRow.Cells(2).Range.Text <> "" Then
                                            xlWS.Cells(intXLRow, intBusinessRulesColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                        '...                                    
                                        End If
                                    End If

Open in new window

0
jgerbitz
Asked:
jgerbitz
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you upload the sample "input" document, and the matching requested output file, so it's easier to "see" what is requested, please?
0
 
jgerbitzAuthor Commented:
Sorry for the late reply angelIII.

Per your request, I've attached both the Word and Excel files.

The Word doc is the input file.  The tables displayed are a small subset of the total.  My main concern is the text in each table's Business Rules section.  Specifically, I'd like to concatenate the strings that appear in separate rows (usually one BR per row) so that all BRs fit into one cell in Excel.  The BRs should be separated by a delimiter (|| preferred).

I've also attached the Excel WB, the BRs are in column V.  It looks pretty ugly since it's raw unformatted test data in there.

Thanks for your help,
Jens
ee-Concat.doc
ee-concat.xls
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
thanks.
I will look into this tomorrow, I am about to go to bed (eyes are issuing a closing request :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jgerbitzAuthor Commented:
Excellent.  Have a good night.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I would go with something like this:
Dim blnAddRules as boolean
blnAddRules = false
 
For Each oTbl In rng.Tables
                For Each oRow In oTbl.Rows
                    Select Case oRow.Cells(1).Range.Font.Size
                        Case 9
                            Select Case CleansedText(oRow.Cells(1).Range.Text)
                                Case "Component Name"
                                    xlWS.Cells(intXLRow, intComponentNameColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                Case "Component Version"
                                    xlWS.Cells(intXLRow, intComponentVersionColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                Case "Data Element Name"
                                    xlWS.Cells(intXLRow, intDataElementNameColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
                                '...
                                Case "Business Rules"
                                    If oRow.Shading.BackgroundPatternColor = -1 Then
                                        If oRow.Cells(2).Range.Text <> "" Then
                                          blnAddRules = true
                                          xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 = empty
                                        End If
                                    End If
     ....
 
                                Case "Privacy Rules" 
                                  blnAddRules = false
 
                                Case else
                                   if blnAddRules then
                                            xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 = xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 & vbcrlf & CleansedText(oRow.Cells(2).Range.Text)
                                   end if

Open in new window

0
 
jgerbitzAuthor Commented:
Thanks angelIII, but I don't think that I explained what I need well enough!

Basically, I need to concatenate strings that are within the "Business Rules" section in each table.  

All 5 tables that are in the Word doc I posted share an almost identical structure; the only variation between the tables is the number of rows within the "Business Rules" section.  Like this:

Table 1 has 1 row = 1 business rule (BR)
Table 2 has 1 row = 0 BRs (but contains a note: "None supplied.")
Table 3 has 4 rows = 4 BRs
Table 4 has 1 row = 1 BR
Table 5 has 4 rows = 4BRs

For example, for Table 3 I'd like to concatenate the BRs contained in column 2 and rows 26, 27, 28, 29.  That's how it would then be sent to Excel and stored in a single cell.

Also, I would like to delimit the BRs with || and mark hard returns within a cell so that if the data is ever exported back to an MS Word table the formatting is preserved as well as possible (like the 4th BR in Table 3).

Hope that makes it all more clear!
Thanks for your help,
Jens

PS- FYI I've changed my code a bit, but it does the same thing.


For Each oRow In oTbl.Rows
    If oRow.Index <= 26 Then
    Dim blnAddRules As Boolean
    blnAddRules = False
        Select Case oRow.Index
            Case 2 'Component Name
                xlWS.Cells(intXLRow, intComponentNameColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
            Case 3 'Component Description
                xlWS.Cells(intXLRow, intDefinitionColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
            Case 4 'Component Version
                xlWS.Cells(intXLRow, intComponentVersionColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
            Case 6 'Data Element Name
                xlWS.Cells(intXLRow, intDataElementNameColumn).Formula = StrConv(CleansedText(oRow.Cells(2).Range.Text), vbProperCase)
            Case 7 'Data Element Description
                xlWS.Cells(intXLRow, intDefinitionColumn).Formula = CleansedText(oRow.Cells(2).Range.Text)
            Case 8 'Data Element Version
            '...

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, In understood that requirement, and my code suggestion should do that...

let me explain:

Case "Business Rules"
                                    If oRow.Shading.BackgroundPatternColor = -1 Then
                                        If oRow.Cells(2).Range.Text <> "" Then
                                          blnAddRules = true
                                          xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 = empty
                                        End If
                                    End If

will initiate the cell that will receive the business rules to empty, and set the flag to add more rules to true

 Case "Privacy Rules"
                                  blnAddRules = false

when meeting the privacy rules row, clear the flag


and finally:
 Case else
                                   if blnAddRules then
                                            xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 = xlWS.Cells(intXLRow, intBusinessRulesColumn).Value2 & vbcrlf & CleansedText(oRow.Cells(2).Range.Text)
                                   end if

for any row, where the flag is set, we shall be between the row "Business Rules" and the "privacy rules". hence, add (concatenate) it.


as I don't have the rest of the code, I could not really test it...
0
 
jgerbitzAuthor Commented:
Sorry for the late reply, thanks for your help, it was much appreciated.
Jens
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now