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.S
' ...
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.BackgroundPatternC
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
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.S
' ...
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.BackgroundPatternC
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
could you upload the sample "input" document, and the matching requested output file, so it's easier to "see" what is requested, please?
ASKER
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
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
thanks.
I will look into this tomorrow, I am about to go to bed (eyes are issuing a closing request :)
I will look into this tomorrow, I am about to go to bed (eyes are issuing a closing request :)
ASKER
Excellent. Have a good night.
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 ifASKER
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.
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
'...ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Sorry for the late reply, thanks for your help, it was much appreciated.
Jens
Jens