CONVERTING MS WORD FORMATTED DATA INTO EXCEL WORKSHEET

[NSNR]

Hi
I have some data entered in MS word (Office XP) in tabular form(formatted
data) using tables. This data have to be copied in MS excel worksheet and
then sorted based on few columns. Can anyone suggest methods to do this.

Since copying/pasting each row of a table in MS word on to a row in MS excel
is time consuming.

 

[Joel]

It depends on how often you need to perform this task if it is worth writing
a macro. You can open a word application from excel VBA. Then you can find
each table in the word document and copy all the data from each table into
excel. It get more complicated if you want to only copy some of the rows
from some of the tables.

You can same the word document as text files and then have excel open the
text files and read the data. Again it depends how much of the text you need
to read in excel how complicated the macro is going to be.

 

[NSNR]

This task we do very often. The document of 5 to 10 pages contains bill of
quantities of materials used for manufacturing a product. In word we create
a table having rows and columns. each columns represents a parameter of a
item. I wish to retain these data with the same columns in MS excel
worksheet.

NSNRao

 

[Joel]

I had a sample of a word table macro at home and I was at work. This should
get you started. The code will get every table from the word document and
place the results in consecutive rows in Excel. The code gets the text
without any formating.

Sub Test()
Set ExSht = ActiveSheet
'
FName = "c:\temp\abc.doc"
WordWasRunning = True

On Error Resume Next
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WDApp = CreateObject("Word.Application")
WordWasRunning = False
End If

WDApp.Visible = True 'at least for testing!

Set WDDoc = WDApp.documents.Open(Filename:=FName)
RowCount = 1
For Each Table In WDDoc.tables
For Each rw In Table.Rows
ColCount = 1
For Each Cell In rw.Cells

Set rngtext = Cell.Range
rngtext.MoveEnd Unit:=wdCharacter, Count:=-1
ExSht.Cells(RowCount, ColCount) = rngtext
ColCount = ColCount + 1
Next Cell
RowCount = RowCount + 1
Next rw
Next Table
Set WordTable = WDDoc.tables(1)
WDDoc.Close
WDApp.Quit
End Sub