Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Word VBA: Concatenation help please

Posted on 2008-06-16
8
546 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
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.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Issues 11 41
Writiing to Word document bookmark from Excel VBA 6 24
EXCEL formula that pulls formatting as well 12 43
V-Lookup 11 16
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

790 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