Export a Word Table to Excel

[Nina]

Hi -

I have a 1 page questionaire that was designed in Word and would like to export this information to Excel. I used the Text Import Wizard and didn't have any luck.

Any suggestions would be greatly appreciated.

Thank you very much.

Nina

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Nina,

Try copy and paste. It will work for a simple table.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi -

I have a 1 page questionaire that was designed in Word and would like to

export this information to Excel. I used the Text Import Wizard and didn't
have any luck.

 

[Nina]

Hi Doug,

Thank you very much for your response. I did try to copy and paste, and it wasn't a clean pasteas my table is not a simple table. The table can best be described as a registration form that has 7 rows. Some of these rows are divided into 2 and 3 cells. I'd like to take the data that is in the 14 or so cells and export them to Excel.

Nina

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Nina,

How is it then determined into which columns in the spreadsheet the data
from two cell rows is to be inserted? Or do you really only need to insert
the data from the last cell in each row of the Word table?

It is going to have to be done by use of a macro, but to create the macro,
the above must be known/
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi Doug,

Thank you very much for your response. I did try to copy and paste, and

it wasn't a clean pasteas my table is not a simple table. The table can
best be described as a registration form that has 7 rows. Some of these
rows are divided into 2 and 3 cells. I'd like to take the data that is in
the 14 or so cells and export them to Excel.

 

[Nina]

Very well. I figured it would involve the creation of a macro. Thanks again Doug.

Cheers,

Nina

 

[raj]

Nina,

....had beginner's luck. Try this code. It's for exporting
all tables from a word doc to excel, one after another. In
case anyone refines this, drop me a note please.

Sub export()
'excel variables
Dim aex As Excel.Application
Dim wbex As Excel.Workbook
Dim shex As Excel.Worksheet
Dim raex As Excel.Range

Set aex = New Excel.Application
Set wbex = aex.Workbooks.Add
Set shex = wbex.Worksheets.Add
wbex.Worksheets("Sheet1").Delete
wbex.Worksheets("Sheet2").Delete
wbex.Worksheets("Sheet3").Delete


shex.Unprotect

'word variables
Dim t As Word.Table

'word properties
Dim nRows As Integer
Dim ncols As Integer
Dim cRow As Integer, cCol As Integer
Dim scont As String
shex.Name = "Extracted"

For Each t In ActiveDocument.Tables
nRows = t.Rows.Count
ncols = t.Columns.Count

Dim neRows As Integer, neCols As Integer 'excel

Set shex = wbex.Worksheets(1)

DetermineUsedRange wbex.Worksheets("Extracted"), raex

If raex Is Nothing Then
neRows = 0
neCols = 0
Else
neRows = raex.Rows.Count + 1
neCols = raex.Columns.Count
End If

For cRow = 1 To nRows
For cCol = 1 To ncols
scont = Trim(CStr(t.Cell(cRow,
cCol).Range.Text))
shex.Cells(neRows + cRow, cCol) = Left
(scont, Len(scont) - 2)
'shex.Cells(cRow, cCol).Formula = Left
(scont, Len(scont) - 2)
Next cCol
Next cRow

Next t
wbex.SaveAs "c:\temp\newfile.xls"
wbex.Close
End Sub

Sub DetermineUsedRange(ByRef xs As Excel.Worksheet, ByRef
theRng As Excel.Range)
Dim nFirstRow As Integer, nFirstCol As Integer, _
nlastrow As Integer, nlastcol As Integer
On Error GoTo handleError
nFirstRow = xs.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
nFirstCol = xs.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
nlastrow = xs.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
nlastcol = xs.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = xs.Range(xs.Cells(nFirstRow, nFirstCol), _
xs.Cells(nlastrow, nlastcol))

Exit Sub
handleError:
End Sub

cheers,
raj