Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Populate MS Word table via VBA?

Posted on 2010-09-16
5
Medium Priority
?
648 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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 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