Excel function in Word. Is it possible?

[Doug G]

Using the trial version of word and excel 7 and would like to be able to
perform a function similar to the VLOOKUP (or HLOOKUP) like excel. I would
like to have one document that will automatically insert the proper text
based on what is chosen from a pull down list. Specifically, it is for a
master specification for new home construction of which i have 6 different
levels of specs. 90% of all of them is identical however there are maybe a
dozen or so entries that differ between them, e.g. if specification A is
selected, the field for that item would list a steel painted front door,
whereas specification B might be a fiberglas door, etc.
I've spent a good deal of time using Excel 2003 but don't have a clue how to
program in Word.
While i have your attention, i also can't get an excel worksheet to link
into word properly. i've tried the cut and paste special, embed, link, and
every other suggestion but my range is too big to fit and it doesn't bring in
the entire spreadsheet. it also doesn't automatically update when i edit the
excel file even though it is set to update automatically in the "edit links
to file" list

 

[Doug Robbins - Word MVP]

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Doug G]

Thanks for the links. I did look at your tutorial before posting thinking
that was the answer but i'm not sure it's right for this application. I may
not have been clear on my request. I have about 6 separate documents
currently and instead of opening the one that applies, i would like to have a
master file (template i suppose) that will swap out certain lines of
information depending on which level of specification is selected from the
pull down list. There would be no data entry required. You would simply open
the master file, save as, select the proper specification and the fields that
differ between specs would automatically update. Am I not understanding how
the userform really works? Thanks

 

[Graham Mayor]

Essentially the userform would supply the means whereby the selection
option(s) are made - your 'drop down list'. As a result of that selection,
you would apply the required information to docvariables in the six
documents and update the docvariable fields in those documents that display
the information. The userform is thus a vehicle to make the selection and
its associated macros apply the data.

Userform coding is diffcult to convey in a text forum, but to quote an
example I have used before -

Imagine a userform with one text box and a command button -
all with default names. (Use more meaningful names in your project). When
the userform is called the first macro inserts a default value in the text
box. That text box could be a drop down field containing selections, but
let's stick with the text box example for now.

When the command button is clicked, the value in the textbox is inserted
into a docvariable and the fields in the active document are updated. If one
or
more of those fields is a docvariable field - in this case {Docvariable
"varText1" } - the field will show what was entered into the user form
field.

Private Sub UserForm_Initialize()
'Set the default value for the text box field
TextBox1.Value = "Default value"
End Sub

Private Sub CmdClose_Click()
'Unload the form when the X is clicked
Unload Me
End Sub

Private Sub CommandButton1_Click()
'Click the command button
Dim oVars As Variables
Dim oStory As Range
Set oVars = ActiveDocument.Variables

'Assign the value of the TextBox1 field to a doc variable
oVars("varText1").Value = TextBox1.Value
'Update the fields in the document
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
'Clean up and close the form
Set oStory = Nothing
Unload Me
End Sub

This example is at a very simple level. It can be scaled up to suit your
project. I include it only to give you some idea of what Doug had in mind.
For example it could be used to lookup information from a table in another
document. The following macro can be used to populate a docvariable field
from a lookup table in a document. Here the document has a two column table.
The macro prompts for a word to look up from column 1 (in practice you would
use the userform to select the look-up item) and then assigns the content of
matching cell in column 2 to a docvariable in each of six documents
doc1.docx - doc6.docx, updates the field in those documents and saves the
result.

The macro is provided to give you ideas and is not meant to be a finished
solution

Sub LookUpFromTableList()
Dim ChangeDoc As Document
Dim RefDoc As Document
Dim cTable As Table
Dim rFind, rRepl, oStory As Range
Dim i As Long
Dim sFname As String
Dim sText As String
Dim oVars As Variables
Dim vVar As Variant
Dim bFound As Boolean
bFound = False
sText = InputBox("Find what?")
sFname = "D:\My Documents\Test\changes.doc"
Set ChangeDoc = Documents.Open(sFname)
Set cTable = ChangeDoc.Tables(1)
For i = 1 To cTable.Rows.Count
Set rFind = cTable.Cell(i, 1).Range
rFind.End = rFind.End - 1
If rFind = sText Then
Set rRepl = cTable.Cell(i, 2).Range
rRepl.End = rRepl.End - 1
bFound = True
Exit For
Else
bFound = False
End If
Next i
For j = 1 To 6
Set RefDoc = Documents.Open(FileName:= _
"d:\My Documents\Test\Temp\Doc" & j & ".docx")
Set oVars = RefDoc.Variables
If bFound = True Then
oVars("varText1").Value = rRepl.Text
Else
oVars("varText1").Value = ""
End If
For Each oStory In RefDoc.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
RefDoc.Close wdSaveChanges
Next j
ChangeDoc.Close wdDoNotSaveChanges
End Sub

End Sub



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


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

 

[Doug G]

Essentially the userform would supply the means whereby the selection
option(s) are made - your 'drop down list'. As a result of that selection,
you would apply the required information to docvariables in the six
documents and update the docvariable fields in those documents that display
the information. The userform is thus a vehicle to make the selection and
its associated macros apply the data.

Userform coding is diffcult to convey in a text forum, but to quote an
example I have used before -

Imagine a userform with one text box and a command button -
all with default names. (Use more meaningful names in your project). When
the userform is called the first macro inserts a default value in the text
box. That text box could be a drop down field containing selections, but
let's stick with the text box example for now.

When the command button is clicked, the value in the textbox is inserted
into a docvariable and the fields in the active document are updated. If one
or
more of those fields is a docvariable field - in this case {Docvariable
"varText1" } - the field will show what was entered into the user form
field.

Private Sub UserForm_Initialize()
'Set the default value for the text box field
TextBox1.Value = "Default value"
End Sub

Private Sub CmdClose_Click()
'Unload the form when the X is clicked
Unload Me
End Sub

Private Sub CommandButton1_Click()
'Click the command button
Dim oVars As Variables
Dim oStory As Range
Set oVars = ActiveDocument.Variables

'Assign the value of the TextBox1 field to a doc variable
oVars("varText1").Value = TextBox1.Value
'Update the fields in the document
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
'Clean up and close the form
Set oStory = Nothing
Unload Me
End Sub

This example is at a very simple level. It can be scaled up to suit your
project. I include it only to give you some idea of what Doug had in mind.
For example it could be used to lookup information from a table in another
document. The following macro can be used to populate a docvariable field
from a lookup table in a document. Here the document has a two column table.
The macro prompts for a word to look up from column 1 (in practice you would
use the userform to select the look-up item) and then assigns the content of
matching cell in column 2 to a docvariable in each of six documents
doc1.docx - doc6.docx, updates the field in those documents and saves the
result.

The macro is provided to give you ideas and is not meant to be a finished
solution

Sub LookUpFromTableList()
Dim ChangeDoc As Document
Dim RefDoc As Document
Dim cTable As Table
Dim rFind, rRepl, oStory As Range
Dim i As Long
Dim sFname As String
Dim sText As String
Dim oVars As Variables
Dim vVar As Variant
Dim bFound As Boolean
bFound = False
sText = InputBox("Find what?")
sFname = "D:\My Documents\Test\changes.doc"
Set ChangeDoc = Documents.Open(sFname)
Set cTable = ChangeDoc.Tables(1)
For i = 1 To cTable.Rows.Count
Set rFind = cTable.Cell(i, 1).Range
rFind.End = rFind.End - 1
If rFind = sText Then
Set rRepl = cTable.Cell(i, 2).Range
rRepl.End = rRepl.End - 1
bFound = True
Exit For
Else
bFound = False
End If
Next i
For j = 1 To 6
Set RefDoc = Documents.Open(FileName:= _
"d:\My Documents\Test\Temp\Doc" & j & ".docx")
Set oVars = RefDoc.Variables
If bFound = True Then
oVars("varText1").Value = rRepl.Text
Else
oVars("varText1").Value = ""
End If
For Each oStory In RefDoc.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
RefDoc.Close wdSaveChanges
Next j
ChangeDoc.Close wdDoNotSaveChanges
End Sub

End Sub



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


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




Wow, that's a lot to take in as a newbie but i will give it a go. Thanks for the detailed explaination and it sounds like Doug was pointing me in the right direction after all. Thanks
.