Convert Text to Table - For A Specific Text Background Color

[saphire99]

Hello to all,

Hope everyone is having a great day. :hi:

I have text to table problem I am trying to solve.:confused:

What I am trying to achieve:



If the text has an RGB back ground of lets say 220,220,220 - This text will need to be converted to a 3 Column table
If the text has a background color of RGB of 209,209,209 - This text will be converted to a 2 Column Table




I will use a pipe | as a delimiter.






Sub ConvertTextToTable()


Dim opara As String, i as long


'====Create a 3 Column Table

IF

opara.Range.Shading.BackgroundPatternColor = RGB(220,220,220)

Then

Selection.ConvertToTable Separator:=wdSeparateByDefaultListSeparator, _
NumColumns:=3, NumRows:=3, AutoFitBehavior:=wdAutoFitFixed
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False

'=======Create a 2 Column Table

Else if


opara.Range.Shading.BackgroundPatternColor = RGB(209, 209, 209)

Selection.ConvertToTable Separator:=wdSeparateByDefaultListSeparator, _
NumColumns:=2, NumRows:= , AutoFitBehavior:=wdAutoFitFixed
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False



End With
End Sub



I thought of using bookmarks but - it becomes a bit messy as each document may have loads of different tables and managing the bookmark names -inserting, deleting may cause confusion later.


I don't know how many rows each table may have. So it needs a variable - of some kind - which I am yet to be acquainted with.

Later on It would be useful for me to convert back these tables to text.

I would really really appreciate the kind help from the professionals as I don't know how to complete this.

thank you so much in advance for helping me

Saphire

[gmayor]

Are you planning to pre-select the region to be converted, or do you expect to search for text formatted with a background colour? If the latter, how is the text formatted in the document? Presumably you have groups of paragraphs formatted with coloured backgrounds, each paragraph comprising a list of items separated by the pipe character?

[saphire99]

Hello Graham,

thank you for looking over this problem.


15041

The table text will have a shading back ground - hence choosing a specific RGB color so there is no mistake when converting text to table.

The 2 column table will have a different RGB back ground color.

Each document may have as many tables of each.

So I would have to search for that background color - and then convert to text.

Thank you

Saphire

[gmayor]

Unless there is at least one unformatted paragraph between each coloured block, the results are likely to be unpredictable, but the following might work for you


Option Explicit

Sub Macro1()
Dim oRng As Range, oTable As Range
Set oRng = ActiveDocument.Range
With oRng.Find
Do While .Execute("|")
If oRng.Paragraphs(1).Range.ParagraphFormat.Shading.BackgroundPatternColor = _
RGB(220, 220, 220) Then
Set oTable = oRng.Paragraphs(1).Range
Do While oTable.Next.ParagraphFormat.Shading.BackgroundPatternColor = RGB(220, 220, 220)
oTable.End = oTable.Next.End
Loop
oRng.End = oTable.End
oTable.Text = Replace(oTable.Text, "|", "")
oTable.ConvertToTable NumColumns:=3, NumRows:=3, AutoFitBehavior:=wdAutoFitFixed
With oTable.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
.Range.ParagraphFormat.Shading.BackgroundPatternColor = wdColorAutomatic
.Shading.BackgroundPatternColor = RGB(220, 220, 220)
End With
End If
oRng.Collapse 0
Loop
End With
Set oRng = ActiveDocument.Range
With oRng.Find
Do While .Execute("|")
If oRng.Paragraphs(1).Range.ParagraphFormat.Shading.BackgroundPatternColor = _
RGB(209, 209, 209) Then
Set oTable = oRng.Paragraphs(1).Range
Do While oTable.Next.ParagraphFormat.Shading.BackgroundPatternColor = RGB(209, 209, 209)
oTable.End = oTable.Next.End
Loop
oRng.End = oTable.End
oTable.Text = Replace(oTable.Text, "|", "")
oTable.ConvertToTable NumColumns:=2, NumRows:=2, AutoFitBehavior:=wdAutoFitFixed
With oTable.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
.Range.ParagraphFormat.Shading.BackgroundPatternColor = wdColorAutomatic
.Shading.BackgroundPatternColor = RGB(209, 209, 209)
End With
End If
oRng.Collapse 0
Loop
End With
End Sub

[saphire99]

Hello Graham,

I hope you are having a fantastic day today!

Thank you so much for taking the time out of your day to help me.

This is outstandingly breath taking! :clap2:


Before

15044






After - Graham's Magic

15045



This is simply awesome - I can select which text to convert to a table based on the RGB color - that way I do not have to convert them all If I don't want to.

With raw work in progress documents - a lot of time is wasted trying to convert tables to text and vice versa - especially if you are trying to view information on the go on mobile screens.

I had this problem months ago and at the back of my mind kept thinking about it. Not knowing how to go about executing it.

The problem with text to table conversion is manually highlighting the text - then you have formatting marks creating problems for no reason
ughhh I was getting really tired.

I tried recording like 10 macros and adapting it - nothing worked.

Sometimes you have to admit defeat. :crying:

This is Wonderfully! Awesome!

I could not have done it at all!!

This VBA language is very confusing, although I try :blush - however it has a mind of its own, when I follow the instructions to the T - it still goes off on one.

I appreciate the coding finesse and time taken to thoughtfully create a working macro for me.

I am thrilled!

Look at how many lines of code!

My last task for another day will be to convert my tables to text - I think that should be not to hard

Select all tables convert to text.

But anyhow that's for another day!


:sparkle:

Have a fantastic day! :biggrin:


:beerchug:

Saphire

xoxox


And Happy New Year - 2016!

:wavey:


Please Mark as Solved!