Send data From word to Excel

[A1pro]

Hello,

I'm a total novice with VBA

I have got a word document template. The data inputted needs to be
transfered to a excel spreadsheet.

What I have done so far is to bookmark the data that I want to send to the
spreadsheet.

I need to do two things:

- Send the data to the spreadsheet
- Put the data on the next available space on the column (e.g. there's data
on cells a1-10, the data needs to go to cell a11)

I can do it the other way round, i.e. get the data of an excel spreadsheet
on to word, but it's not convenient at all because the document template has
a lot of fields that are not on the spreadsheet.

Thanks In Advance

 

[Greg Maxey]

A1pro,

I am about a novice with Excel VBA myself. You can use the following
to write data to a cell at row 1 column 11:

Sub ScratchMacro()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\TestBook1.xls")
Set xlWS = xlWB.Worksheets(1)
xlWS.Cells(1, 11) = "Write in this text"
xlWB.Save
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Which row you write to is up to you ;-)

 

[Helmut Weber]

Hi,

Early binding assumed,
Excel already running,
workbook open.
Target is column 1 in activesheet in activeworkbook.

Sub Macro3()
Dim oExc As Excel.Application
Dim oWrk As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim rDcm As Word.Range
Dim oBkm As Word.Bookmark
Dim lRow As Long
Set rDcm = ActiveDocument.Range
Set oExc = GetObject(, "Excel.application")
Set oWrk = oExc.ActiveWorkbook
Set oSht = oExc.ActiveSheet
lRow = oSht.Cells(oSht.Rows.Count, 1).End(xlUp).Row
' gets the last cell with a value in column 1
For Each oBkm In rDcm.Bookmarks
lRow = lRow + 1
oSht.Cells(lRow, 1).Value = oBkm.Range.Text
Next
End Sub

See also:
http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm
http://word.mvps.org/faqs/interdev/controlxlfromword.htm

There is a lot more to it,
if you are striving for perfection.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[A1pro]

Thanks for the quick reply

Unfortunately it does not seem to be doing anything

I have both excel and word open, with the appropriate documents are open and
nothing happens.

The macro runs fine but nothing happens.

I'm not sure I explained myself too well, anyway.

There are several bookmarks, and each bookmark will go into the first empty
cell of each column, rather than to the same column, which is what the macro
does, i think

Thanks for your help

 

[Helmut Weber]

Hi,

I've defined first empty cell
as the first empty cell after the last cell
containing a value in a column,
which is *not* always the first empty cell,
strictly speaking.

This puts the text from the bookmarks
in these cells in one column after the other,
at least here and now.

Sub Macro3a()
Dim oExc As Excel.Application
Dim oWrk As Excel.Workbook
Dim oSht As Excel.Worksheet
Dim rDcm As Word.Range
Dim oBkm As Word.Bookmark
Dim lRow As Long ' row
Dim lClm As Long ' column
Set rDcm = ActiveDocument.Range
Set oExc = GetObject(, "Excel.application")
Set oWrk = oExc.ActiveWorkbook
Set oSht = oExc.ActiveSheet
For Each oBkm In rDcm.Bookmarks
lClm = lClm + 1
lRow = oSht.Cells(oSht.Rows.Count, lClm).End(xlUp).Row + 1
oSht.Cells(lRow, lClm).Value = oBkm.Range.Text
Next
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Helmut Weber]

Hi A1pro,

are you sure you got the right bookmarks?
There are two kinds of them, including and excluding.
excluding ones look like ][ and do not contain anything,
including ones look like [text text text] and contain text
or something else.

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Curt]

I open a word document Then type in data needed. Want to then save the word.
document to a cell in excel. Have no trouble getting to word. Only have never
tried to save back to excel. Would be working in the excel sheet going to
word and back for more work in excel.
Also when I go to word I have to click the mouse befor I can start entering
text. Must be missing something in my macro.
This is a good learning experience for me.
Thanks Much

 

[Helmut Weber]

Hi Curt,

I open a word document

Then Word is the active application, usually.

Then type in data needed.
Want to then save the word.document to a cell in excel.

Some text, I assume, not to put a Word.doc-object in a table cell.

Have no trouble getting to word.

So you start from Excel?

Sorry, pretty confusing.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"