How to convert pipe delimited table data back into proper tables?

[Walentyn]

We produce accessible format files for blind folks. We strip out all formatting from source files (from OCR etc) in order to start from scratch. I have a procedure/Sub which copies the contents of a file and pastes them into a new document as text, but this also removes tables. I've now added a Sub which turns any tables in the source file into pipe delimited data before pasting as text. But I need help writing VBA to convert these lines of pipe delimited data back into proper tables. Any help would be very much appreciated.

[gmaxey]

Unless it is a state secret, it would help if you should an example of what this pipe delimited text looks like.


But in the absence of that, assume the following selected text represented a table:
Title||Apples|Blueberries|Cherries|Dates||Eggplant|Figs|Ginger|Hops

where "||" delimits a row and "|" delimits a column.


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim arrMajor() As String, arrMinor() As String
Dim strText As String
Dim oRng As Range
Dim oTbl As Table
Dim lngIndex As Long, lngRows As Long
Dim lngEval As Long, lngCol As Long
Set oRng = Selection.Range
strText = oRng.Text
arrMajor = Split(strText, "||")
lngRows = UBound(arrMajor) + 1
Set oTbl = oRng.Tables.Add(oRng, lngRows, 1)
For lngIndex = 0 To UBound(arrMajor)
arrMinor = (Split(arrMajor(lngIndex), "|"))
oTbl.Rows(lngIndex + 1).Cells(1).Split 1, UBound(arrMinor) + 1
For lngCol = 0 To UBound(arrMinor)
oTbl.Cell(lngIndex + 1, lngCol + 1).Range.Text = arrMinor(lngCol)
Next lngCol
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

[Walentyn]

Well, not quite a state secret , but here is an example from a test file:

Column 1 | Column 2 | Column 3 | Column 4 | Column 5
Row 1 | Table cell | Table cell | Table cell | Table cell
Row 2 | Table cell | Table cell | Table cell | Table cell

This is generated from a procedure whose guts look like this:

[vba] For Each myTable In ActiveDocument.Tables myTable.Select
Selection.Style = ActiveDocument.Styles("Normal")

Application.DefaultTableSeparator = "|"
Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
NestedTables:=True

Next myTable
[\VBA]

[gmaxey]

Okay, in your example a Chr(13) (Paragraph mark) would be used to delimit the rows and you would select everything but the last paragraph mark

25987

[Walentyn]

Thanks so much for that code Greg, not something I could've done as a relative beginner. Will pick this thread up next week when back at work and have had a chance to install and test.

[macropod]

Try:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "[!^13]@|*^13"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
.ConvertToTable "|"
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
End Sub

[Walentyn]

Thanks so much Paul. That works perfectly.
I've added a bit of table formatting to put the cell borders back in, as well as removed unwanted spaces left over from the pipe delimiters. So now the cleanup process is now seamless, all at a click of a button from the ribbon!
Thanks again.

[macropod]

removed unwanted spaces left over from the pipe delimiters.
I wasn't sure whether those extra spaces were just part of your representation here, since the code you posted doesn't include them. That part can probably be achieved by changing:

.ConvertToTable "|"

to:
.ConvertToTable " | "

[Walentyn]

That part can probably be achieved by changing:

.ConvertToTable "|"

to:
.ConvertToTable " | "


After a bit of testing I've concluded VBA doesn't like any spaces here. It throws up an error every time: "Invalid procedure call or argument."

So I'm calling a find and replace function to remove the extra spaces around the pipe character, which are indeed inserted by a procedure that turns tables into pipe delimited data. That procedure is doing double duty here, where the spaces are undesirable.

In any case, your code is simple and elegant, and right down to the wildcard search term, teaches me a lot.

So, most grateful to you, thank you.