Excel as data source

[User]

I have a table in MS Word and i want when users type in #80282 in one of the
cell in the table (in MS Word), it will look up my excel file for that code
and have the details about that code (like product ID, price etc) added
automatically into my Word's table.

Is that possible?

 

[Ed]

Almost anything is possible. Practical and do-able in real-world situations
is another story.

I assume you're going to give this Word file to other users? On CD? By
email as attachment? Download through a network? Or are they going to
reach through the network to open it in a common file? Where is your Excel
file going to reside? Will each user have a copy of it? Or will it be in a
network-accessible folder?

To have a macro in Word read the value put into a table cell, open an Excel
file, find that value and copy associated information back into Word is not
that hard. The difficult part is setting it up so the users don't have any
problems. One major hurdle is making the two files find and talk to each
other.

Ed

 

[User]

The file will be in a networked environment, G: mapped to a server drive. We
will not be sending that file out anyway.

ANy suggestions on where to start? I know nothing about VBA

 

[Helmut Weber]

Hi,

I have a table in MS Word and i want when users type in #80282 in one of the
cell in the table (in MS Word), it will look up my excel file for that code
and have the details about that code (like product ID, price etc) added
automatically into my Word's table.

Is that possible?

in a way, yes.

But there is more to it than meets the eye.

Can you control Excel from Word?
If not, see:
http://word.mvps.org/faqs/interdev/controlxlfromword.htm

You cannot, at least not with justifiable effort,
have Word react on each key pressed.

You can react on a tab pressed, if the insertion point is in a table.
For that you overwrite the built in command "nextcell".
With doing that, you got to have a replacement for NextCell:

Wordbasic.NextCell.

As pressing tab in the last cell of a table
results in a new row added, you'd have to check before,
whether the insertion point is in the last cell.

And there is a hell of more complications.
like you have to restrict that to a specific table.

Sub NextCell()
Dim C1 As Long
Dim C2 As Long
Dim rTbl As Range
Set rTbl = Selection.Range
C1 = Selection.Tables(1).Range.Cells.Count
rTbl.Start = Selection.Tables(1).Range.Start
rTbl.End = Selection.End + 1
C2 = rTbl.Cells.Count
If C2 < C1 Then
WordBasic.NextCell
' collect data from Excel <<<<
MsgBox "here comes the Excel part"
' but not for the last cell in the table
End If
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jean-Guy Marcil]

User was telling us:
User nous racontait que :

I have a table in MS Word and i want when users type in #80282 in one
of the cell in the table (in MS Word), it will look up my excel file
for that code and have the details about that code (like product ID,
price etc) added automatically into my Word's table.

Is that possible?

Yes. But it depends how well you can managed VBA code.

In short, here is what you could do.

I think the easiest would be to have a custom toolbar with a button that
would call a macro.

the macro would pop up an InputBox (or a userform) asking the user to type
in the product number.
Then, this number would be inserted in the currently active cell, the Excel
source would be opened, the info would be dropped in the cells adjacent to
the currently active cell. Finally the Excel source would be closed and
control returned to the user.

For info on controlling Excel from Word, see:
http://word.mvps.org/faqs/interdev/controlxlfromword.htm

For the Input box, in the Word VB editor, type InputBox select it and hit F1
to get the help file and some coding example on that.

For the transfer of data from Excel to Word, the easiest is to match the
info in the Excel cells to the same amount of cells in your Word table.

You may need to post in an Excel group for help with the Excel manipulation
(Looking for a specific cell in a column based on a text value).

But, maybe someone has already written code for this and will be along to
share it with you.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Ed]

I'm getting an idea of a setup like an order form, and when the user inputs
the item number you want the rest of the info to come up? Why use both Word
and Excel? Why not drive the whole thing in one Excel file? Excel is a
terrible Word processor, but great for information manipulation.

If this were my task, I would first try to create the input form on one
sheet of an Excel workbook, with all the other information on other
worksheets (hidden, if you don't want the user to have access to them).
When done, a single button saves the whole thing as a brand new file,
leaving the blank form for the next user. If it truly had to be both Word
and Excel, I would either go with Jean-Guy's thoughts of the UserForm, or
create a VB program with a unique toolbar for the whole shebang.

HTH
Ed

 

[User]

Thanks for all your replies.

Since my user has an additional requirement, I will be creating a UserForm
and when the user types the style, it will search the excel file, and show
the Size in the size Combo Box, and will do the same for Type.

However, now i need help on how to have the combo box and style's text box
link to Excel.

And what is ControlSource? I don't quite understand the help file on this...

Thanks agian for all your help!