Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Other Office > Word VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Word VBA Discuss using VBA to program Word.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Word VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 2nd, 2011, 10:07 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation excel data into word

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
Reply With Quote
  #2 (permalink)  
Old June 6th, 2011, 06:08 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #3 (permalink)  
Old June 8th, 2011, 02:46 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Here is the solution close to your expectation:

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

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #4 (permalink)  
Old June 13th, 2011, 04:48 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default runtime error 424

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
Reply With Quote
  #5 (permalink)  
Old June 13th, 2011, 05:12 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation

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
Reply With Quote
  #6 (permalink)  
Old June 13th, 2011, 05:35 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
The Following User Says Thank You to Shasur For This Useful Post:
cjcant (June 13th, 2011)
  #7 (permalink)  
Old June 13th, 2011, 06:02 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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)

Last edited by cjcant; June 13th, 2011 at 06:29 AM..
Reply With Quote
  #8 (permalink)  
Old June 13th, 2011, 06:30 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
The Following User Says Thank You to Shasur For This Useful Post:
cjcant (June 13th, 2011)
  #9 (permalink)  
Old June 13th, 2011, 06:38 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Sorry for taking up a lot of your time but the bookmark has now come up with a error 5941.
Reply With Quote
  #10 (permalink)  
Old June 13th, 2011, 06:42 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Do you have a bookmark called 'Name'

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

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to Word VBA genericcereal Word VBA 1 May 11th, 2009 08:25 PM
Excel Charts to Word Sanjo VB How-To 1 March 31st, 2007 12:07 AM
Word to Excel hanzo Excel VBA 2 March 7th, 2007 05:18 AM
Excel to word sdowen Excel VBA 2 June 20th, 2005 03:41 AM
Merge to word from excel Kevinsharrison Access VBA 1 April 26th, 2005 07:40 AM



All times are GMT -4. The time now is 04:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.