Solved

Populate MS Word table via VBA?

Posted on 2010-09-16
5
641 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
ID: 33690983
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
ID: 33691076
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
ID: 33692030
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
ID: 33692050
Thanks again for the prompt response.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33693010
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

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 was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

910 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

18 Experts available now in Live!

Get 1:1 Help Now