• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4273
  • Last Modified:

create and insert bookmarks into word document using VBA

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
flynny
Asked:
flynny
  • 6
  • 4
1 Solution
 
GrahamSkanRetiredCommented:
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
 
GrahamSkanRetiredCommented:
Sorry. Line 3 should read
Set tbl = ActiveDocument.Tables(1) ' for the first table in the document.
0
 
flynnyAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
flynnyAuthor Commented:
sorry, the error occurs at this line

oFormField.Name = fieldName
0
 
flynnyAuthor Commented:
also just to add the program does create the formfield in the relevant cell.
0
 
GrahamSkanRetiredCommented:
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
 
flynnyAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
flynnyAuthor Commented:
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
 
flynnyAuthor Commented:
superb quick repsonse and direct to the solution.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now