Convert Text to Table - For A Specific Text Background Color

[saphire99]

Hello to all,

Hope everyone is having a great day.

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

What I am trying to achieve:

1. 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
2. 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.


image.jpg

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!


Before

before.jpg






After - Graham's Magic

After.png



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.

This is Wonderfully! Awesome!

I could not have done it at all!!

This VBA language is very confusing, although I try - 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!




Have a fantastic day!




Saphire

xoxox


And Happy New Year - 2016!




Please Mark as Solved!