Microsoft Word data to Excel Columns

[HockeyDiablo]

Is it possible at all to cut the first top 5 lines in a Word Document and have them send over to an Excel table?

Word Data:

ALL100 ALL RECREATION
1111 LAYES DRIVE BURNSVILLE
MN 55337- JERRY KERRY
(952)890-7000 (952)700-1067
WHIRLPOOL TUBS, BARS, STOOLS, POOL & FOOSBALL TABLES

In Excel as:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ACCT[/TD]
[TD]EXHIBITOR[/TD]
[TD]ADDRESS[/TD]
[TD]CITY[/TD]
[TD]STATE[/TD]
[TD]ZIP[/TD]
[TD]CONTACT[/TD]
[TD]PHONE[/TD]
[TD]CELL[/TD]
[TD]DESCRIPTION[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ALL100[/TD]
[TD]ALL RECREATION[/TD]
[TD]1111 LAYES DRIVE[/TD]
[TD]BURNSVILLE[/TD]
[TD]MN[/TD]
[TD]55337[/TD]
[TD]JERRY KERRY[/TD]
[TD]952-890-7000[/TD]
[TD]952-700-1067[/TD]
[TD]WHIRLPOOL TUBS, BARS, STOOLS, POOL & FOOSBALL TABLES[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance. Im not sure this is possible, or I am going at this the wrong way...

-J

 

[Macropod]

For the most part, this is fairly straightforward. However, this is no reliable way of separating the address and city from a string like that, since you don't have anything to indicate where the address ends and the city name (which might have two or more words) begins.

 

[HockeyDiablo]

The information has similar spaces involved between the fields. I thought I could run a replace and place like a ¥ symbol and use that as a delimiter once in Excel

 

[Macropod]

The information has similar spaces involved between the fields

That's irrelevant when you can't tell from the Word document, what's an address and what's a city name. For example, try the following with the data you posted:

Sub Demo()
Dim xlApp As Object, xlWkBk As Object, xlWkSht As Object, StrTxt As String
' Test whether Excel is already running.
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
  Set xlApp = CreateObject("Excel.Application")
  If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  End If
End If
On Error GoTo 0
xlApp.Visible = True
Set xlWkBk = xlApp.Workbooks.Add
Set xlWkSht = xlWkBk.Sheets(1)
' Update the workbook.
With ActiveDocument
  StrTxt = Split(.Paragraphs(1).Range.Text, vbCr)(0)
  xlWkSht.Range("A1").Value = Split(StrTxt, " ")(0)
  xlWkSht.Range("B1").Value = Replace(StrTxt, Split(StrTxt, " ")(0), "")
  StrTxt = Split(.Paragraphs(2).Range.Text, vbCr)(0)
  xlWkSht.Range("D1").Value = Split(StrTxt, " ")(UBound(Split(StrTxt, " ")))
  xlWkSht.Range("C1").Value = Trim(Replace(StrTxt, xlWkSht.Range("D1").Value, ""))
  StrTxt = Split(.Paragraphs(3).Range.Text, vbCr)(0)
  xlWkSht.Range("E1").Value = Split(StrTxt, " ")(0)
  xlWkSht.Range("F1").Value = Split(StrTxt, " ")(1)
  xlWkSht.Range("G1").Value = Trim(Replace(Replace(StrTxt, xlWkSht.Range("E1").Value, ""), xlWkSht.Range("F1").Value, ""))
  StrTxt = Split(.Paragraphs(4).Range.Text, vbCr)(0)
  xlWkSht.Range("H1").Value = Split(StrTxt, " ")(0)
  xlWkSht.Range("I1").Value = Split(StrTxt, " ")(1)
  StrTxt = Split(.Paragraphs(5).Range.Text, vbCr)(0)
  xlWkSht.Range("J1").Value = StrTxt
End With
' Switch to the Excel workbook
Set xlWkBk = Nothing: Set xlApp = Nothing
End Sub

Then try it again with a city whose name has two words.

Ask yourself: How would a macro recognise how many words a city name has?