Solved

create and insert bookmarks into word document using VBA

Posted on 2008-10-31
10
3,754 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
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…

726 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