Solved

create and insert bookmarks into word document using VBA

Posted on 2008-10-31
10
3,654 Views
Last Modified: 2012-06-27
Hi all,

I have a template for creating invoices. However i want to be able to add a bill of materials to it. at the moment i prompt for all the needed information for labour and transport in the following format;

LABOUR <labourcost>      <total1>
TRANSPORT <transcost>  <total2>
                                           <inv total>

this is in a simple table of 6 cells (mainly to align the totals on the right)

i want to be invoice to look (eventually) like the following;

LABOUR <labourcost>      <total1>
TRANSPORT <transcost>  <total2>
MATERIALS <material1>    <mattotal1>
...                                       ...
MATERIALS <materialN>  <mattotalN>
                                           <inv total>

now i have added the folowing code;

    mCount = 1
    vAnswer = MsgBox(Prompt:="Are there any Materials to enter?", Buttons:=vbYesNo)
     
    While vAnswer = 6
        AddMaterials (mCount)
        mCount = mCount + 1
        vAnswer = MsgBox(Prompt:="Are there any further Materials to enter?", Buttons:=vbYesNo)
    Wend

and the AddMaterials method

Sub AddMaterials(ByVal count As Integer)
        vVar = InputBox(Prompt:="Please enter description of the materials and click ok.")
       
        'build bookmark name
        Dim Name As String
        Name = "MATERIAL" + CStr(count)
        ActiveDocument.Bookmarks.Add Name, Selection.Range
        ActiveDocument.FormFields(Name).Result = vVar
       
        vVar = InputBox(Prompt:="Please enter cost of the materials and click ok.")
       
        Name = "MATERIALCOST" + CStr(count)
        ActiveDocument.Bookmarks.Add Name
        ActiveDocument.FormFields(Name).Result = vVar
End Sub

now i can't see how to ;

a) create a new row in the table (i can remove this table if needed) in VBA
b) once the new row has been created add the detail and the cost.

Any ideas?

Many thanks in advance,

Matt.
0
Comment
Question by:flynny
  • 6
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22848863
Your strategy is a bit confusing to me - a mixture of FormFields, Bookmarks and Tables.

To use FormFields, you need the document to be protected for forms. Their purpose is to allow the user to enter data only in particular places, but to add rows and change data elsewhere, including at non-formfield bookmarks, the document must be unprotected.

Also, If you are using tables, you can address the rows and cells directly, so you probably don't need bookmarks.

This demonstrates how to add a row to the end of a table.

Dim tbl As Table

Dim rw As Row

Set tbl = ActiveDocument.Tables

Set rw = tbl.Rows.Add

rw.Cells(1).Range.Text = "Some Text"

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22848874
Sorry. Line 3 should read
Set tbl = ActiveDocument.Tables(1) ' for the first table in the document.
0
 

Author Comment

by:flynny
ID: 22848998
hi thanks for the post. Sorry about the confusion. yes it is formfield i want to use.

ok, i've added the following code to add the additional line with the detail and the cost

        vText = InputBox(Prompt:="Please enter description of the materials and click ok.")
         
        Dim tbl As Table
        Dim rw As Row
        Set tbl = ActiveDocument.Tables(1)
        Set rw = tbl.Rows.Add
        rw.Cells(1).Range.Text = vText
       
        vText = InputBox(Prompt:="Please enter cost of the materials and click ok.")
     
        'now access cell
        Set oRange = rw.Cells(2).Range
        Set oFormField = ActiveDocument.FormFields.Add(Range:=oRange, Type:=wdFieldFormTextInput)
       
        'build the fields name
        Dim fieldName As String
        fieldName = "MATERIAL" + CStr(count)
        oFormField.Name = fieldName
        oFormField.Result = vVar

however i get a run time error '91'

object variable or with block variable not set.

any ideas what this means?

0
 

Author Comment

by:flynny
ID: 22849004
sorry, the error occurs at this line

oFormField.Name = fieldName
0
 

Author Comment

by:flynny
ID: 22849112
also just to add the program does create the formfield in the relevant cell.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22849680
Sorry for the delay. She decided we had to go to Tesco's.

The problem is that the form field addition failed without warning you.

The reason that it fails is that the cell range include the cell formatting, and this would be overwritten. Adding text seems to realise this and work properly but the form field procedure doesn't, and you have to pull the range back buy one character.
    Dim vText As String

    Dim oRange As Range

    Dim oFormField As FormField

    Dim Count As Integer

    Dim vVar As Variant

    vText = InputBox(Prompt:="Please enter description of the materials and click ok.")

     

    Dim tbl As Table

    Dim rw As Row

    Set tbl = ActiveDocument.Tables(1)

    Set rw = tbl.Rows.Add

    rw.Cells(1).Range.Text = vText

    

    vText = InputBox(Prompt:="Please enter cost of the materials and click ok.")

    

    'now access cell

    Set oRange = rw.Cells(2).Range

    oRange.MoveEnd wdCharacter, -1 '<----------------------

    Set oFormField = ActiveDocument.FormFields.Add(Range:=oRange, Type:=wdFieldFormTextInput)

    

    'build the field's name

    Dim fieldName As String

    fieldName = "MATERIAL" + CStr(Count)

    oFormField.Name = fieldName

    oFormField.Result = vVar

Open in new window

0
 

Author Comment

by:flynny
ID: 22851356
great that works a treat. one last thing do you know how i can set the number format of the text form field please? i've been playing around for ages with it with no luck whatsoever.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 22851765
Use FormField.TextInput.EditType

Here is an example
    Dim ffld As FormField

    Dim rng As Range

    

    ActiveDocument.Unprotect 'Password

    Set rng = ActiveDocument.Bookmarks("\EndOfDoc").Range

    Set ffld = ActiveDocument.FormFields.Add(rng, wdFieldFormTextInput)

    ffld.TextInput.EditType wdNumberText, "#,##0.00", "0.00", True

    ActiveDocument.Protect wdAllowOnlyFormFields, True ',password

Open in new window

0
 

Author Comment

by:flynny
ID: 22865362
brilliant thanks so much for your help on this its been very much appreciated. sorry fo the late reply, but for some reason ee kept crashing when i tried to post a reply. proberbly due o my poor wireless connection.

thanks again!

Matt.
0
 

Author Closing Comment

by:flynny
ID: 31511982
superb quick repsonse and direct to the solution.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now