?
Solved

create and insert bookmarks into word document using VBA

Posted on 2008-10-31
10
Medium Priority
?
3,934 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 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