Solved

Populate MS Word table via VBA?

Posted on 2010-09-16
5
640 Views
Last Modified: 2012-05-10
I have some Word documents which have a small table in them.  I don't know much, if anything, able tables in Word.  The document itself serves as a mechanism to print labels, 4 labels to a page, i.e. it is a full page with 4 labels on it.

The table is structured so that 2 cells fall on each of the 4 labels.  These cells are currently manually updated with the Lot # and Expiration date that are on the labels.

When a user opens the document, it is mostly protected so they can't change anything, but they can enter data into the cells of the table.  So a user would open the document and for each of the four labels the user will update the cell that has the   Lot # and the cell that has the Exp. date.  Naturally, the completely manual system results no and then in errors, entering a lot # 4 times sometimes results in an incorrect lot # in one of the cells (and hence one of the labels).

What I would like to do is prompt the use to enter the lot # once and the expiration date once and then update all the cells via VBA.

I have this so far:
Private Sub Document_Open()
    Dim UseBy As String
    Dim Lot As String
    UseBy = InputBox("Please enter the Use By date in YY-MMM-DD format", "UseBy Date")
    Lot = InputBox("Please enter the Lot in DF-YY-### format", "Lot #")
   
End Sub

The question I have is "Now what?".

Thanks in advance.

Rich
0
Comment
Question by:RichNH
  • 3
  • 2
5 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 250 total points
Comment Utility
Assuming you are working with the first table in the document then:

thisdocument.tables(1) references the table
thisdocument.tables(1).cell(r,c) references the table cell in row r column c.

thisdocument.tables(1).cell(1,2).range.text = "fred" set the cell in column 2 of row 1 to the string "Fred"

Chris
 
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
As a for instance:

Chris
Sub fillTable()

Dim rw As Integer

Dim strLot As String

Dim strDesc As String

    

    strLot = InputBox("Enter the LOT", "Label Generator")

    strDesc = InputBox("Enter the Description", "Label Generator")

    With ThisDocument.Tables(1)

        For rw = 1 To .Rows.Count Step 2

            .Cell(rw, 1).Range.Text = strLot

            .Cell(rw, 2).Range.Text = strLot

            .Cell(rw + 1, 1).Range.Text = strDesc

            .Cell(rw + 1, 2).Range.Text = strDesc

        Next

    End With

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:RichNH
Comment Utility
Yes, Thank you.   I received the PW for this document a short time ago and from what I can determine the table is a table of at least THREE cells across and I'm confused about how many rows down.  Some of these cells contain a FORM which has text boxes which are filled in manually as you get ready to print the labels.  Sheesch, this gets more and more complicated.

I found this out because when I put the code in it wiped out the entire contents of the top 2 labels in the document and replaced them with the data I entered, then it said the collection I was trying for (1,4) didn't exist.  so (1,1) & (1,3) contain forms and (1,2) is the center column used as a spacer.

Obviously there is a lot more to this than I thought.   Hmmmmmmm.

OK,   Obviously your entries for the problem as originally stated worked, so I will give you the points and try to format a question that appropriately states the problem, once I figure out what that is.

Rich
0
 
LVL 1

Author Closing Comment

by:RichNH
Comment Utility
Thanks again for the prompt response.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
You could try uploading the document in the new question along with dummy data for how it should look - to make it easier to get it right.

Chris
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

743 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

13 Experts available now in Live!

Get 1:1 Help Now