macro to place comma delimited text into a table

W

WayneBurrows

I want to be able to select some comma delimited text and then place
it into a table with each item that is separated by the commas to be
put into a separate cell.

Any ideas how to do this?
 
G

Graham Mayor

Convert Text to Table would be the first place to look.
If that doesn't work for you give us some more information about the text
and how you want it arranged. The Word version would help also!
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
W

WayneBurrows

I have the text like this

one,two,three,four

and I want it to be manipulated into four particular rows (not
columns) in a larger table.
 
G

Graham Mayor

It's like drawing teeth! :) Which particular rows of which table in which
Word version?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
W

WayneBurrows

Sorry about that

Currently I am using word 2002

The table is irregular

Lets say for example I have two rows of data separated by commas

I want to put the first row in the third column of rows 1 to 4

And the second row in the second column of rows 5 to 8

Something like that.

I don't know how to specify precisely which table. I have the tables
as a autotext options show I can add a blank table and then want to
populate the cells with data in the stated format.

Thanks
 
G

Graham Mayor

OK without knowing more about how your tables are used it is difficult to
suggest how to identify which table you wish to add the data to, however the
following example may help point the way. The macro is run with the data
selected and adds a table to the end of the document to accept the data.


Dim oRng As Range
Dim oEnd As Range
Dim oPara1, oPara2 As Variant
Dim oTable As Table
'Locate the end of the document
Set oEnd = ActiveDocument.Range
oEnd.Start = oEnd.End
'Select the two rows of comma delimited text
Set oRng = Selection.Range
'Check that the data is selected
If Len(oRng) = 0 Then
MsgBox "Select the data first!", vbCritical, "Error"
Exit Sub
End If
'Assign each of the two rows to a variable
oPara1 = Split(oRng.Paragraphs(1).Range.Text, ",")
oPara2 = Split(oRng.Paragraphs(2).Range.Text, ",")
'Add an 8 x 3 table at the end of the document
Set oTable = ActiveDocument.Tables.Add(oEnd, 8, 3)
'Write the entries from the first row to the appropriate cells
For i = 0 To UBound(oPara1)
oPara1(i) = Replace(oPara1(i), Chr(13), "")
oTable.Cell(1 + i, 2).Range.Text = Trim(oPara1(i))
Next i
'Write the entries from the second row to the appropriate cells
For j = 0 To UBound(oPara2)
oPara2(j) = Replace(oPara2(j), Chr(13), "")
oTable.Cell(5 + j, 3).Range.Text = Trim(oPara2(j))
Next j


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
W

WayneBurrows

OK without knowing more about how your tables are used it is difficult to
suggest how to identify whichtableyou wish to add the data to, however the
following example may help point the way. The macro is run with the data
selected and adds atableto the end of the document to accept the data.

Dim oRng As Range
Dim oEnd As Range
Dim oPara1, oPara2 As Variant
Dim oTable AsTable
'Locate the end of the document
Set oEnd = ActiveDocument.Range
oEnd.Start = oEnd.End
'Selectthe two rows of comma delimited text
Set oRng = Selection.Range
'Check that the data is selected
If Len(oRng) = 0 Then
    MsgBox "Selectthe data first!", vbCritical, "Error"
    Exit Sub
End If
'Assign each of the two rows to a variable
oPara1 = Split(oRng.Paragraphs(1).Range.Text, ",")
oPara2 = Split(oRng.Paragraphs(2).Range.Text, ",")
'Add an 8 x 3tableat the end of the document
Set oTable = ActiveDocument.Tables.Add(oEnd, 8, 3)
'Write the entries from the first row to the appropriate cells
For i = 0 To UBound(oPara1)
    oPara1(i) = Replace(oPara1(i), Chr(13), "")
    oTable.Cell(1 + i, 2).Range.Text = Trim(oPara1(i))
Next i
'Write the entries from the second row to the appropriate cells
For j = 0 To UBound(oPara2)
    oPara2(j) = Replace(oPara2(j), Chr(13), "")
    oTable.Cell(5 + j, 3).Range.Text = Trim(oPara2(j))
Next j

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web sitewww.gmayor.com
Word MVP web sitehttp://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>











- Show quoted text -

That is brilliant thanks.

If I use the macro to insert the table where the selected text is with
a command like

ActiveDocument.AttachedTemplate.AutoTextEntries("TableOne").Insert
Where:=Selection.Range

How do I then set the oTable variable to this inserted table so that I
can then insert the text.

Thanks again
 
G

Graham Mayor

WayneBurrows said:
That is brilliant thanks.

If I use the macro to insert the table where the selected text is with
a command like

ActiveDocument.AttachedTemplate.AutoTextEntries("TableOne").Insert
Where:=Selection.Range

How do I then set the oTable variable to this inserted table so that I
can then insert the text.

I take it that you want to replace the selected text (the comma delimited
items) with the autotext table?
In that case, the table is the first table in the selection.

Dim oRng As Range
Dim oEnd As Range
Dim oPara1, oPara2 As Variant
Dim oTable As Table
'Locate the end of the document
Set oEnd = ActiveDocument.Range
oEnd.Start = oEnd.End
'Select the two rows of comma delimited text
Set oRng = Selection.Range
'Check that the data is selected
If Len(oRng) = 0 Then
MsgBox "Select the data first!", vbCritical, "Error"
Exit Sub
End If
'Assign each of the two rows to a variable
oPara1 = Split(oRng.Paragraphs(1).Range.Text, ",")
oPara2 = Split(oRng.Paragraphs(2).Range.Text, ",")
'Add the autotext table in place of the data
ActiveDocument.AttachedTemplate.AutoTextEntries("TableOne") _
.Insert Where:=oRng
Set oTable = oRng.Tables(1)
'Write the entries from the first row to the appropriate cells
For i = 0 To UBound(oPara1)
oPara1(i) = Replace(oPara1(i), Chr(13), "")
oTable.Cell(1 + i, 2).Range.Text = Trim(oPara1(i))
Next i
'Write the entries from the second row to the appropriate cells
For j = 0 To UBound(oPara2)
oPara2(j) = Replace(oPara2(j), Chr(13), "")
oTable.Cell(5 + j, 3).Range.Text = Trim(oPara2(j))
Next j


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
W

WayneBurrows

I take it that you want to replace the selected text (the comma delimited
items) with the autotexttable?
In that case, thetableis the firsttablein the selection.

Dim oRng As Range
Dim oEnd As Range
Dim oPara1, oPara2 As Variant
Dim oTable AsTable
'Locate the end of the document
Set oEnd = ActiveDocument.Range
oEnd.Start = oEnd.End
'Selectthe two rows of comma delimited text
Set oRng = Selection.Range
'Check that the data is selected
If Len(oRng) = 0 Then
    MsgBox "Selectthe data first!", vbCritical, "Error"
    Exit Sub
End If
'Assign each of the two rows to a variable
oPara1 = Split(oRng.Paragraphs(1).Range.Text, ",")
oPara2 = Split(oRng.Paragraphs(2).Range.Text, ",")
'Add the autotexttablein place of the data
ActiveDocument.AttachedTemplate.AutoTextEntries("TableOne") _
    .Insert Where:=oRng
Set oTable = oRng.Tables(1)
'Write the entries from the first row to the appropriate cells
For i = 0 To UBound(oPara1)
    oPara1(i) = Replace(oPara1(i), Chr(13), "")
    oTable.Cell(1 + i, 2).Range.Text = Trim(oPara1(i))
Next i
'Write the entries from the second row to the appropriate cells
For j = 0 To UBound(oPara2)
    oPara2(j) = Replace(oPara2(j), Chr(13), "")
    oTable.Cell(5 + j, 3).Range.Text = Trim(oPara2(j))
Next j

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web sitewww.gmayor.com
Word MVP web sitehttp://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Thanks this is working a treat.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top