Solved

Word VBA: Concatenation help please

Posted on 2008-06-16
8
547 Views
Last Modified: 2011-10-19
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
Comment
Question by:jgerbitz
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21803835
could you upload the sample "input" document, and the matching requested output file, so it's easier to "see" what is requested, please?
0
 

Author Comment

by:jgerbitz
ID: 21807519
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21807574
thanks.
I will look into this tomorrow, I am about to go to bed (eyes are issuing a closing request :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jgerbitz
ID: 21807587
Excellent.  Have a good night.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816785
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
 

Author Comment

by:jgerbitz
ID: 21818264
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21819082
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
 

Author Comment

by:jgerbitz
ID: 21970585
Sorry for the late reply, thanks for your help, it was much appreciated.
Jens
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question