PDA

View Full Version : Excel data to Word Bookmark



bryVA
12-18-2008, 10:50 AM
Hello guys,

I have an excel file that keeps a log that has 15 Columns of data. This data also needs to be inserted in a word document. I would like to have the userform that populates the log to pull up a word template and fill in the data into bookmarks. I have tried to do this in which it populates the log however I can't get it to populate into Word Bookmarks. It will open up the document but not a template either. :banghead: Here is my code:


Dim wdApp As Word.Application, wdDoc As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If

VAOff = TextBox10.Value
Initial = TextBox11.Value
Tele = TextBox12.Value
wdApp.Documents.Open ("C:\Documents and Settings\vscbbroo\Desktop" _
& "\MOD 119.doc")

wdApp.Visible = True



'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=VAOffice

'Copy the data from Thisworkbook
Sheets("Sheet1").Range("IV10").Value = VAOff
Sheets("Sheet1").Range("IV10").Copy

'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Filenum
'Copy the data from Thisworkbook
Sheets("Sheet1").Range("IV11").Value = TextBox1.Value
ThisWorkbook.Sheets("IV11").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=VetName
'Copy the data from Thisworkbook
Range("IV12").Value = TextBox2.Value
ThisWorkbook.Sheets("IV12").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Date
'Copy the data from Thisworkbook
Range("IV13").Value = Now
ThisWorkbook.Sheets("IV13").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=ContactName
'Copy the data from Thisworkbook
Range("IV14").Value = TextBox3.Value
ThisWorkbook.Sheets("IV14").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Addr1
'Copy the data from Thisworkbook
Range("IV15").Value = TextBox5.Value
ThisWorkbook.Sheets("IV15").Copy
'Paste into Word
wdApp.Selection.Paste
'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Addr2
'Copy the data from Thisworkbook
Range("IV16").Value = TextBox6.Value
ThisWorkbook.Sheets("IV16").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=City
'Copy the data from Thisworkbook
Range("IV17").Value = TextBox7.Value
ThisWorkbook.Sheets("IV17").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=State
'Copy the data from Thisworkbook
Range("IV18").Value = ComboBox3.Value
ThisWorkbook.Sheets("IV18").Copy
'Paste into Word
wdApp.Selection.Paste
'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Zip
'Copy the data from Thisworkbook
Range("IV19").Value = TextBox8.Value
ThisWorkbook.Sheets("IV19").Copy
'Paste into Word
wdApp.Selection.Paste

'Tell Word to goto the bookmark assigned to the variable
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=Surviving1
'Copy the data from Thisworkbook
Range("IV20").Value = ComboBox2.Value
ThisWorkbook.Sheets("IV20").Copy
'Paste into Word
wdApp.Selection.Paste

Am I doing this wrong? I am running Word 2000.

Thanks guys,

Bryan
Edit Lucas: VBA tags added to code.

lucas
12-18-2008, 11:04 AM
Check out post #2 in this thread.....see if it does what you want. Remember to go ahead and print the three sheets so you will see what it is doing.
http://vbaexpress.com/forum/showthread.php?t=24303

lucas
12-18-2008, 11:08 AM
Bryan, I just noticed you are new to the forum. Welcome. You can select your code and hit the vba button to format it as I have done to your code in your first post.

bryVA
12-18-2008, 11:10 AM
Thank you, I didn't now that. Thanks for the link I am currently placing this in to see if it will work.

Bryan

lucas
12-21-2008, 01:17 PM
Hi Bryan,
Made a couple of changes to this. With the attached version you can select which rows to send to file or print by clicking in column A which will put a check mark next to the ones you want to print or save to word file.

It is set up right now to save all the files to the same directory as the excel file and the word template(.dot) files. You can change that in the code.

You can also comment out the code to save to file after you get it set up and let it go ahead and print them without saving the files.....see the comments in the code.

If you want to show or hide the bookmarks so you can see them just run this from word:

Option Explicit
Sub a()
ActiveWindow.View.ShowBookmarks = True
End Sub
Sub b()
ActiveWindow.View.ShowBookmarks = False
End Sub