Stumped (Need access to data in a table passed to another document)

[Greg Maxey]

I have a letterhead template that can have one to four addressees arranged
in a table.

I have another template for creating mailing labels (six per sheet). When
this template is used to create a new document it presents a Userform with
controls for filling in data in each of the six possible slots. This allows
the user to print 1 to 6 different labels to any slot on the label sheet.

I now face the challenge of bringing these two templates together. I am
trying to figure out how I can open and create an new letterhead (with one
to four addressees) then run a macro from that letterhead template that
creates a new label document. I have gotten as far as that. I have a macro
in the letterhead document that creates a new sheet of labels based on the
label template. When the new label document is created the UserForm is
display.

The code looks like this:

Option Explicit
Public oTblPublic As Word.Table
Sub CreateLabels()
Const EnvTempPath As String = "F:\My Documents\Word\Paid Work\Stein
Consulting\Fairfield\Practice Labels.dot"
Dim oPar As Paragraph
Dim oLetter As Word.Document

Dim oEnvDoc As Word.Document
Set oLetter = ActiveDocument
Set oTblPublic = oLetter.Tables(1)
Set oEnvDoc = Documents.Add(Template:=EnvTempPath)
End Sub

With the new label document now in front of me with the Userform displayed,
I need access to the information stored in the letterhead addreesee table
(oTblPublic). I don't know that must about public variables, but I thought
if a variable was declared as Public then it would be available to all
modules in all open projects. I must be wrong as I get an error when I try
something like this in the label document project:

Sub Test
Msgbox oTblPulic.Cell(1,4).Range.Text
End SUb

Saying that the variable is not declared.

Thanks for any explaination or help.

 

[Graham Mayor]

Assuming oTblPulic is a typo then the Cell(1,4) doesn't exist in your table
1
Are you sure it isn't Cell(4,1)

Option Explicit
Private oTblPublic As Word.Table
Private oRng As Range
Const EnvTempPath As String = "F:\My Documents\Word\Paid Work\Stein
Consulting\Fairfield\Practice Labels.dot"

Sub CreateLabels()
Dim oPar As Paragraph
Dim oLetter As Word.Document
Dim oEnvDoc As Word.Document
Set oLetter = ActiveDocument
Set oTblPublic = oLetter.Tables(1)
Set oRng = oTblPublic.Cell(4, 1).Range
oRng.End = oRng.End - 1
Set oEnvDoc = Documents.Add(Template:=EnvTempPath)
End Sub

Sub Test()
MsgBox oRng.Text
End Sub

works for me with a two column four row table to read row 4 column 1

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Greg Maxey]

Graham,

Yes the cell ref was a typo but not the problem. Your:

Sub Test()
MsgBox oRng.Text
End Sub

works if it is in the active document (e.g., the letterhead). I need to
access to that data while in a procedure contained in the lable document
that is created with:

Set oEnvDoc = Documents.Add(Template:=EnvTempPath)

 

[Greg Maxey]

Here is what I have ended up doing but feel that it is a Rube Goldberg
process.

I have simple document named "Test.doc" based on a template named
"Test.dot"that contains a single 2X2 table. This document contains a macro
that opens a new document based on a different template "Test Label.dot".
When the new document opens I want to use data in the "Test" document table
(still open and unsaved) to fill textboxes in a UserForm that is created
when the new document is opened.

Code in my "Test" document:

Sub CreateNewDoc()
Documents.Add Template:="C:\Documents and Settings\Maxey\Desktop\Test Label
Template.dot"
End Sub

Code in the "ThisDocument" module of the Test Label Template

Sub Document_New()
UserForm1.Show
End Sub


Code in the UserForm:

Option Explicit
Private oTbl As Word.Table
Private Sub UserForm_Initialize()
GetDocData
With Me
.TextBox1.Text = oTbl.Cell(1, 1).Range.Text
.TextBox2.Text = oTbl.Cell(1, 2).Range.Text
.TextBox3.Text = oTbl.Cell(2, 1).Range.Text
.TextBox4.Text = oTbl.Cell(2, 2).Range.Text
End With
End Sub
Sub GetDocData()
Dim oDoc As Word.Document
For Each oDoc In Documents
MsgBox oDoc.FullName
If oDoc.Name = "Test.doc" Then
Set oTbl = oDoc.Tables(1)
Exit For
End If
Next
End Sub


Is seems like I should be able to some how declare a table object in either
the Document_New procedure or the UserForm Initializaion procedure that can
just refer to the table in the Test document. If I can I don't know how to
do it or do I know a better/safer way to do what I have done.

Thanks.