Link Word doc to Excel database

[pdxappraiser]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a Word template that I want to link to an Excel database. Each time I use the template, I want to import the fields in the Excel database to the template. I know I can use the Link or Embed features, but how to I make the template reference the fields in successive records in the database each time I want to start a new Word document using the template. I should also note that the "template" I am using is just a formatted document with a ".docx" file name.

 

[Peter Jamieson]

If you want to reference the data in the last row in the Excel sheet, you
can try the following. However, I have only tried it experimentally and have
no idea whether this approach will survive "in the wild". For one thing, it
currently relies on the fact that you have no empty cells)

If you want something else, please spell it out. Let's suppose your data is
in a table in the top left corner of Sheet1 and no cells below the table
contain data

For each column of data, you will need to create an Excel name
(Insert->Name->Define). e.g., if you have 3 columns, create
a. a name called columna set to
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,1,1)
b. a name called columnb set to
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-1,0,1,1)
c. a name called columnc set to
=OFFSET(Sheet1!$C$1,COUNTA(Sheet1!$C:$C)-1,0,1,1)

(NB, COUNTA(Sheet1!$A:$A) etc are probably not very good isdeas as Excel
potentially has to count a heck of a lot of rows). Perhaps an Excel expert
knows of a better approach).

Then, select cell A1, copy, paste special->paste link in Word, selecting the
format you want.

Display the field codes. You should see something like:

{ LINK Excel.Sheet.8 "Macintosh HD:Users:usernameocuments:myworkbook.xlsx"
Sheet1!R1C1:R1C1 \a \t }

Modify that to be

{ LINK Excel.Sheet.8 "Macintosh HD:Users:usernameocuments:myworkbook.xlsx"
Sheet1!columna \a \t }

and update the field. You should see the last value in column a. Try adding
a value in the next row in column A, then update the field.

If you need to allow for gaps in the data you could do something like:
a. have column A contain a row number (or anything, as long as there is
always something in every row).
b. the columnb name might then be set to
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A:$A)-1,0,1,1)

etc.

 

[Jim Gordon MVP]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a Word template that I want to link to an Excel database. Each time I use the template, I want to import the fields in the Excel database to the template. I know I can use the Link or Embed features, but how to I make the template reference the fields in successive records in the database each time I want to start a new Word document using the template. I should also note that the "template" I am using is just a formatted document with a ".docx" file name.

One way to connect a table to Excel is to use Word Fields.

First, connect a data table to excel.

In Word use View > Toolbars > Database
Click Insert Database on the Database Toolbar
Select the table you want to use
Set any Query options you want to filter by

Once your table is in Word it is now linked to Excel.
Save the Word document as a Template.
Don't move or rename or change the file path to the Excel document or
you will break the link.

If you were using Word 2004 at this point I'd probably recommend a VBA
Macro, but you can't do that in 2008. For 2008 you'll probably need an
AppleScript to handle keeping track of the last record that was
displayed and incrementing that for the next time you open the template.

You can check out the ASK Word field to see if that's good enough for
your needs.

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.


Visit my blog
http://blog.360.yahoo.com/blog-i7JMeio7cqvhotIUwCzaJWq9

 

[CyberTaz]

I can't honestly say I'm clear on the specific objective, but it sounds like
you may be able to accomplish what you want by using the standard features
of the Mail Merge Manager - set the Document Type to Catalog.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[pdxappraiser]

The reason I thought mail merge wouldn't work, is I need to reference the fields in a different record in the database each time I use the word template. I want to update the word doc with new client name, address, and other information. Ideally, it would be best if I could query the database for a specific job number, and then reference or import the remaining fields in the record that match that job number. Any thoughts?

 

[Jim Gordon MVP]

The reason I thought mail merge wouldn't work, is I need to reference the fields in a different record in the database each time I use the word template. I want to update the word doc with new client name, address, and other information. Ideally, it would be best if I could query the database for a specific job number, and then reference or import the remaining fields in the record that match that job number. Any thoughts?

Hi Cybertaz and I offered some suggestions to try.

Whether you use the Mail Merge or Database field codes you should be
able to accomplish your task.

If you try Taz's Mail Merge method be sure to check out the Word Fields
on the Word Field section of the Data Merge manager. The ASK word field
is there, along with NEXT RECORD IF, NEXT RECORD, SKIP NEXT RECORD IF.

Word help has good documentation for each of these commands, so there's
no point in us rehashing that here.

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.


Visit my blog
http://blog.360.yahoo.com/blog-i7JMeio7cqvhotIUwCzaJWq9

 

[Peter Jamieson]

If you take the Mail Merge route, you can select the record number (or if
you need the last one, click the "Last Record" button) using the Preview
Results section of the Data Merge manager, or use the Filter Recipients
facility.

However you do it, something to bear in mind is that when you save your
document, it is still linked to the data source - reopen your document and
you may not see what you expect. If you need to save a copy of the document
with the data "fixed" at the time of creation, you can select the document
and "unlink" all the field codes, which just leaves their results, or you
can for example print your document to .pdf format and save that.

 

[CyberTaz]

It wasn't clear originally that you were intending to do one recipient at a
time That being the case the Catalog idea is out the window. I've not
played with this to perfection, but initially what seems to work is:

You'll need to use Continuous Section Breaks in the template. [BTW - It
doesn't actually have to be saved as a template in this case because you'll
be generating new merge docs from it each time.] In place of the FORMTEXT
fields put tables (1 or more cells) into the appropriate sections & set the
Properties of the tables as necessary if you want to control their size,
border color, etc. The other fields (checkboxes, dropdowns & data merge)
need to be in separate sections from the editable tables.

Use tools> Protect Document - Forms - Sections & clear the checks for the
sections containing editable tables. Then turn the Protection *off* in the
main form doc - otherwise you won't be able to use the merge features.

As suggested by Peter, when you use the "template" take advantage of the
Filter Recipients feature of the MMM to select one or more recipients based
on your criteria (such as the Job number you mentioned). Then use the
Complete Merge - Merge to New Document button to generate a new document
personalized for the intended recipient. Once that document is generated use
Tools> Protect Document to turn Protection on in it - The correct Section
Protection settings should be inherited from the "template".

The resulting doc should allow use of the checkboxes & dropdowns as well as
the ability to enter text into the tables in the unprotected sections. The
doc should not be modifiable otherwise. Also, if you have designated your
data source in the original template you won't have to specify it again each
time you generate a letter.

I don't know that this will give you *exactly* what you're looking for but
perhaps it will at least serve as a launch pad to help get you there

HTH |:>)
Bob Jones
[MVP] Office:Mac