excel data into word

cjcant · June 2nd, 2011, 10:07 AM

Hi,

I have seen several pieces of code for importing data from excel into word. However, I am new to programming and I do not understand the code that well (also I am looking for a code that will work in word 2007 all the posts I have seen are older).

Is it possible for somebody to post something that has fairly detailed notes?

I basically want to look up a particular cell then insert that into a bookmark.

Please help!

Thanks

Shasur · June 6th, 2011, 06:08 AM

There are two ways to do this

1) Reading the value and updating the bookmark

2) Linking the Excel File cell content to Worddocument (whenever the value in excel gets changed that will be updated in the document - it will prompt for updating links)

for both you need to add are reference to Excel object library and use the normal way to extract the cell content

Cheers
Shasur

Shasur · June 8th, 2011, 02:46 AM

Hi

Here is the solution close to your expectation:

http://vbadud.blogspot.com/2009/08/u...ith-excel.html

Cheers
Shasur

cjcant · June 13th, 2011, 04:48 AM

Hi,

Thanks for getting back to me. I have tried to do it using the following code but there is a runtime error 424 coming up on the start of the loop (For i = 1 ...)

Do you have any idea what I am doing wrong?

Private Sub btnExcel_Click()
Dim wApp As Word.Application
Dim wDoc As Word.Document

Set wApp = New Word.Application
Set wDoc = wApp.Documents.Add("H:\proposalDocumentDevelopment \HVAC\experiment\excelDataTest.xlsx")

For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row

wDoc.Bookmarks("para1").Range.Text = Cells(i, A1)
wDoc.Bookmarks("para2").Range.Text = Cells(i, A2)
wDoc.Bookmarks("para3").Range.Text = Cells(i, A3)
Next i
End Sub

cjcant · June 13th, 2011, 05:12 AM

Hi,

Sorry but I think I have just realised what the code means. What I am actually wanting to do is to press a button on the userForm in a word document.

When that button is pressed (in the word document) I want it to do the following:
1) Find a cell (A1) in an excel document.
2) Take the data stored in A1.
3) Insert that data into a bookmark ("para1") within the document (which has the userForm button)

I am not just wanting to copy all the data straight in as the user should be able to select certain items and then insert it into a certain place in the document (while the document is visible so it can be edited). The word document must be where the userForm is.

Thanks

Shasur · June 13th, 2011, 05:35 AM

Hi

Here is a sample .. this takes from First sheet of the Workbook and places in the bookmark

Code:

Sub CopY_Data_From_Excel_To_Word()
 
Dim oXA As Excel.Application
Dim oXB As Excel.Workbook
 
 
 
Set oXA = New Excel.Application
 
Set oXB = oXA.Workbooks.Open("H:\proposalDocumentDevelopment \HVAC\experiment\excelDataTest.xlsx")
 

ActiveDocument.Bookmarks("Name").Range.Text = oXB.Sheets(1).Cells(i1, 1)

'Code for saving the document
 

 
oXB.Close (False)
oXA.Quit
If Not oXB Is Nothing Then Set oXB = Nothing
If Not oXA Is Nothing Then Set oXA = Nothing

' Releasing objects etc
End Sub

Cheers
Shasur

cjcant · June 13th, 2011, 06:02 AM

Thanks

Just one question:
How does it know to select cell A1?

Also, I am getting a runtime error 1004 on ActiveDocument.Bookmarks("para1").Range.Text = oXB.Sheets(1).Cells(i1, 1)

Shasur · June 13th, 2011, 06:30 AM

The statement

ActiveDocument.Bookmarks("Name").Range.Text = oXB.Sheets(1).Cells(1, 1)

tells that it has to take the value from Cell (1,1) - First Row, First Column - A1

Cheers
Shasur

cjcant · June 13th, 2011, 06:38 AM

Sorry for taking up a lot of your time but the bookmark has now come up with a error 5941.

Shasur · June 13th, 2011, 06:42 AM

Do you have a bookmark called 'Name'

The statement expects a bookmark - Name
ActiveDocument.Bookmarks("Name").Range.Text = "Sample"

Cheers
Shasur