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
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
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
'...
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
consolidate from multiple folders | 5 | 39 | |
Match formula returns N/A | 5 | 25 | |
DBF to ... Converter | 5 | 29 | |
Move entire row to another workbook based on cell value | 20 | 38 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
6 Experts available now in Live!