PDA

View Full Version : Solved: Word bookmark Help



lifeson
07-03-2008, 06:53 AM
This could go inot one of 3 forums
Excel help
word help
Auotmation help

Seen as its excel where the coding is taking place I stuck it here.

I am trying to export selected lines from a worksheet into a table in a word document template
The word template has a single line table on it wih a bookmark named 'Table1' in the first cell
I am using the following code to loop through a range of cells and add the correct bit of data to the word template
Sub PrintQuote()
Dim wdApp As Object
Dim bkMrk As String
'Dim wdDoc As word.document
Dim ws As Worksheet
Dim r As Long, x As Long, rw As Long
Dim ID As String, iDesc As String, fName As String, file As String
Dim qty As Integer, key As Integer
Dim Price As Single, vat As Single, unit As Single, total As Single, upUnit As Single
Dim unitInc As Single, sTotalExc As Single, sTotalInc, sTotalVat As Single, uplift As Single
Application.Cursor = xlWait
Set ws = ThisWorkbook.Worksheets("treeview")
sTotalExc = 0
sTotalInc = 0
uplift = frmQuoteBuilder.uplift / 100
'set file name
fName = ThisWorkbook.path & "\" & "PrintTemplate.doc"
' Start Word and create an object
Set wdApp = CreateObject("Word.Application")
With wdApp
.documents.Open (fName)
With ws
r = .Cells(Rows.Count, "A").End(xlUp).Row
For x = 2 To r
key = .Cells(x, "E").Value
Select Case key
Case 1: 'item is a component
'MsgBox "checking row:" & x
ID = .Cells(x, "F").Value
iDesc = .Cells(x, "G").Value
qty = .Cells(x, "D").Value
unit = .Cells(x, "H").Value
upUnit = (unit * uplift) + unit
vat = .Cells(x, "I").Value / 100
unitInc = (upUnit * vat) + upUnit
total = qty * unitInc

sTotalExc = sTotalExc + (upUnit * qty)
sTotalInc = sTotalInc + (total)
'Send commands to Word
With wdApp
With .Selection
.Goto what:=wdgotobookmark, Name:="Table1"
.TypeText Text:=ID
.MoveRight unit:=wdCell
.TypeText Text:=iDesc
.MoveRight unit:=wdCell
.TypeText Text:=qty
.MoveRight unit:=wdCell
.TypeText Text:=upUnit
.MoveRight unit:=wdCell
.TypeText Text:=Format(total, "? #,##00.00")
.MoveRight unit:=wdCell
End With 'selection
End With 'wordapp
End Select
Next x
End With 'ws
.Application.PrintOut
'.activedocument.SaveAs FileName:=fName
End With 'word app

This is where it goes wrong
With .Selection
.Goto what:=wdgotobookmark, Name:="Table1"

What am I doing wrong?

As an aside
How do I make the Select Case statement look for 2 items
e.g.

Select Case Key
case 1: or Case 2 ?

lifeson
07-03-2008, 08:55 AM
Just realised that loop is not going to work anyway :(

Even if I could find the bookmark it would kepp returning to the bookmark and overwriting the previous entry :banghead:

The problem still exists then :doh:

How to transfer a set of data to a word table via vba.

Oh and my multiple select case question as well. :whistle:

qazpl
07-03-2008, 11:00 PM
CASE 1 to 2

qazpl
07-03-2008, 11:16 PM
Is bookmark selected? if not try ActiveDocument.Bookmarks("Table1").Range.Select Also move this code before loop.

Dave
07-04-2008, 04:49 AM
Here's a link with some XL to Word table code. No need to use bookmarks... just directly identify the table cell location that you want to place the XL data in. HTH. Dave
http://www.vbaexpress.com/forum/showthread.php?t=17784

lifeson
07-04-2008, 07:11 AM
Here's a link with some XL to Word table code. No need to use bookmarks... just directly identify the table cell location that you want to place the XL data in. HTH. Dave
http://www.vbaexpress.com/forum/showthread.php?t=17784

Thanks Dave looks like a usefull bit of code
I'll have a play with that and see if I can break it adapting it for my purpose:rotlaugh: :rotlaugh:

lifeson
07-04-2008, 07:12 AM
CASE 1 to 2

:doh: So simple - Thanks

mdmackillop
07-04-2008, 07:20 AM
CASE 1 to 2
or

Case 1, 2, 3, 7

lifeson
07-04-2008, 08:45 AM
or

Case 1, 2, 3, 7


Even simpler :thumb