Solved

Word VBA: Concatenation help please

Posted on 2008-06-16
8
541 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 142

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 142

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
 

Author Comment

by:jgerbitz
ID: 21807587
Excellent.  Have a good night.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now