Picking up data in Word, putting into Excel?!? 1

[LizF]

Hello people,

Does anyone know if it's possible to pick up data from Word (I think the bits I need are in a Word table...) and paste the value into a cell in Excel?

If you could tell me how that would save me much worry, as if this is not possible I have to convince some senior people that they must change this document to Excel!! Actually, I told them before they spent months creating it that it really needed to be Excel but do they ever listen?!!!

Thankyou all once again for the superb help you give so freely - it has made an enormous difference to me!

Liz

 

[RobBroekhuis]

Yes, you can. How easy it is depends on how standardized your word document is. If the info is always in the same table, in the same cell, it's not too difficult.
Rob

 

[LizF]

Hello Rob,

Phew, that's a relief!!

Yes, the info will always come from the same place in each Word document. Don't suppose you have any example code which would give me the syntax for doing this??

Many Thanks,
Liz

 

[RobBroekhuis]

The following code shows how you might go about it:

Dim doc As Object
Set doc = GetObject("c:\wordexample.doc&quot
ActiveCell = WorksheetFunction.Clean(doc.tables(2).Cell(3, 2).Range)
Set doc = Nothing

This example uses late binding, so that I don't need a reference to the Microsoft Word object model. If you want to do more extensive programming, it will be easier to use early binding, in which case you want to set the reference using Tools-References in VBE. This example puts the value in the cell at row 3, column 2 from the 2nd table in the Word document into the active cell of your Excel sheet. The clean function removes any carriage returns/line feeds/end of cell markers that may come along.
Rob

 

[LizF]

Yikes! It's like a different dialect of the same language isn't it?!

I think I follow that code - I'll have a go as soon as I get chance and ask again if I can't write the code properly. Thanks (again!) Rob -----> STAR!

Liz

 

[LikeThisName]

May I ask a question?
=) okay, now my second silly question is

Where are you inserting:
Dim doc As Object
Set doc = GetObject("c:\wordexample.doc&quot
ActiveCell = WorksheetFunction.Clean(doc.Tables(2).Cell(3, 2).Range)
Set doc = Nothing

Thanks a bunch

 

[RobBroekhuis]

Those lines would be part of a macro in Excel VBA. I'm assuming you're running the macro from Excel, and only temporarily need to open Word to grab the info from the tables. Arbitrarily, the code puts the info from Word into the active cell - you'll obviously want to put it in the right place in your workbook.
Rob

 

[priyat]

Here is what I can share with you Liz if you are looking for macro writting.

- Priya


The two example macros below demonstrates how you can send information to Excel from Word (e.g. creating a new workbook) and how you can retrieve information from Excel (e.g. reading information from a workbook).

Note! Read and edit the example code before you try to execute it in your own project!

Sub CreateNewExcelWB()
' to test this code, paste it into a Word module
' add a reference to the Excel-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Set xlApp = CreateObject("Excel.Application&quot
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
'Set xlWB = xlApp.Workbooks.Open("C:\Foldername\Filename.xls&quot ' open an existing workbook
' example excel operations
With xlWB.Worksheets(1)
For i = 1 To 100
.Cells(i, 1).Formula = "Here is a example test line #" & i
Next i
If Dir(&quot;C:\Foldername\MyNewExcelWB.xls&quot <> &quot;&quot; Then
Kill &quot;C:\Foldername\MyNewExcelWB.xls&quot;
End If
.SaveAs (&quot;C:\Foldername\MyNewExcelWB.xls&quot
End With
xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub


Sub OpenAndReadExcelWB()
' assumes that the previous procedure has been executed
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim tString As String, r As Long
Documents.Add ' create a new document
Set xlApp = CreateObject(&quot;Excel.Application&quot
'xlApp.Visible = True
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(&quot;C:\Foldername\MyNewExcelWB.xls&quot ' open an existing workbook
' example excel operations
r = 1
With xlWB.Worksheets(1)
While Cells(r, 1).Formula <> &quot;&quot;
tString = Cells(r, 1).Formula
With ActiveDocument.Content
.InsertAfter tString
.InsertParagraphAfter
End With
r = r + 1
Wend
End With
xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub