Automatically trasnferring data into Word (2000)

Topic

New Reply

I have a table in Word and l would like to know if it is possible to import some data into it automatically, either from Access or Excel.
As l keep having to cut and paste all the time which is very time consuming.

If this is possible any help would be appreciated.

Reply | Quote

Replies

Hi jj,

If your data are in an Excel Worksheet in tabular form, you could simply copy it, then use ‘Edit|Paste Special’ in Word with the ‘paste link’ option to link your Word table to the Excel one. You even get a choice of paste formats. Then all you’ll need to do is select the table in Word and press F9 to update it – you can even have Word update the table automatically upon opening and/or at print time (Look under Tools|Options). If the Excel range is subject to change, give it a name before copying & pasting and update the named range in Excel to reflect any changes. Then the updating process in Word will cause the table to expand/contract to match the redefined range in Excel.

You can use the same process for individual Excel cells, pasting them into a Word table but, if there are many cells involved, it would probably be simpler to set your Excel workbook up with a worksheet formatted to match the Word table and use the above approach.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I cannot see any options in Tools – Options in Word where the table could be updated automatically.
Please can you tell me what option l need to select in Word?
I would be importing all cells in the left hand side of the Excel spreadsheet into Word,can you tell me how l can do this?

Reply | Quote

hu jj,

Maybe you misunderstood. I said:[indent]

---

automatically upon opening and/or at print time (Look under Tools|Options

---

[/indent] If you look under Tools|Options|General you’ll see an option to ‘update automatic links at open’ and if you look under Tools|Options|Print you’ll see an option to ‘update links’ at print time. These are not dynamic updates in the sense that you get them in Excel.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I have both these boxex checked, can you tell me what l have to do next?

Reply | Quote

Press OK and save your document…simple as that

Reply | Quote

I have done that and nothing happens?
How does word know which cells to extract from Excel?,sorry l am a little confused?

Reply | Quote

Well I have a rather large suspicion that you are using auto filters again in your excel file. Although it is not apparent in this screenshot, it has been other ones you have shown in previous Excel threads with helicopter stuff in it. If you are using the autofilters this method will not work…am I right to suggest this?

The method that has been suggested to you works as I have tested it here and I have followed Macropod’s instruction to the letter and he isn’t normally wrong.

Reply | Quote

Yes that is right l am using auto filters.

Reply | Quote

I’m not sure what gets created when you paste a link. Is it a field code? If you press Alt+F9 to toggle the display of fields, does your Excel sheet switch to a field code? If so, try posting its contents here for review.

You also could try Insert>File. I believe you can choose to insert a link and then to link to a particular range in your Excel sheet. If you don’t have a named range yet for the area you want displayed, create that first. (If you can’t create that for some Excel-related reason, you will have to pose that problem to the Excel gurus.)

Reply | Quote

Jeff

Using Macropod’s method you actually get a table created (at least in 2K3), when it is selected you can refresh the data if you make changes to the Excel spreadsheet and it shows in the Word document. I think the issue here is that Justin is using filters in his Excel spredsheet and the data is not being picked up because it is, well, being filtered……hence my query but he is not providing us with much information to assist us!

Reply | Quote

Attached is the named range l need to insert into Word, please can you tell me how l insert it into Word?
see attachment.

Reply | Quote

> Attached is the named range l need to insert into Word, please can you tell me how l insert it into Word?

Did you try Insert > File?

(I never do this, so you will need to try it and check the Help as you go.)

Reply | Quote

This is not quite what l require, l just require a few fields to be inserted.

Reply | Quote

The other thing you can try is Edit > Copy and then in Word you use Edit > Paste Special > Paste Link

StuartR

Reply | Quote

Hi jj,

All you need to do is to copy the Excel range, then use ‘Edit|Paste Special’ in Word with the ‘paste link’ option to link your Word table to the Excel one. Exactly as I originally said.

If the Excel range is subject to change, as it appears to be (you originally had A504:A518 highlighted, but you’ve now got A506:A510 highlighted), I’d recommend giving the range a name in Excel before copying & pasting into Word. If you then update the named range in Excel to reflect any changes, the change will be reflected in your Word document also, causing the table in Word to expand/contract to match the redefined range in Excel. You can automate the updating in Word by checking the ‘update automatic links at open’ option under Tools|Options|General yand the ‘update links’ option under Tools|Options|Print.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

In paste special what option shall l select, see attachment.

Reply | Quote

Hi jj,

Can you describe exactly what was the process you went through to get that dialogue box showing in Word? If you followed my instructions, it should look like the attached.

Your dialogue box looks very much like you closed Excel before pasting into Word. You should keep Excel open while doing this.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I firstly select a name range then select “EDIT” then “COPY”, is this ok?
I called the range: pasted_range

Reply | Quote

The Paste Link option will only be available if you have just copied the cells in Excel, and Excel still has the flickering dashed line around the copied cells. As soon as you double click in a different Excel cell, or do almost anything else in Excel, this becomes disabled again.

StuartR

Reply | Quote

Hi jj,

OK, step by step:
1. Open your Word document
2. Open your Excel Workbook
3. Select the source range in your Excel Workbook
4. Copy the selected range (eg Ctrl-C)
5. Switch to your Word document (eg by pressing Alt-Tab)
6. Scroll to wherever you want to paste the data into the document
7. Click on the insertion point
8. Select Edit|Paste Special from the Word menu
9. Click on the ‘paste link’ option
10. Select the ‘Microsoft Excel Worksheet Object’ option or, if there’s a possibility that the table will sometimes need to flow over to the next page, you might find the ‘Formatted text (RTF)’ option works better.
11. Click OK

Now you your source range in the Excel Workbook is linked to your Word document. If you change the source contents in Excel, the values in Word will change too. You can position the Excel object anywhere you like on the page. If you select it and look under Format|Object, you’ll see a range of formatting options. The one to control the object’s positioning on the page are grouped under the ‘Layout’ tab.

If you want to see the field coding that Word has used to create the link, selected the Excel object you’ve just pasted into Word and:
12. From the Word menu, select Format|Object|Layout
13. Click ‘In line with text’
14. Click ‘OK’
15. Press Alt-F9 to toggle the field code on/off.

Note that by formatting the object as ‘In line with text’, you can use the normal paragraph formatting options to control its positioning. If you want more freedom, however, formatting it’s layout as ‘square’ or ‘tight’ might prove more useful.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi,

I have followed all of your instructions but cannot workout how to get each cell in a different cell within Word,
see screenshot.
Any help would be appreciated.

Reply | Quote

Hi jj,

To paste the data into a set of horizontal cells in Word, they’d have to be organised horizonatlly in Excel also.

However, it now seems that you’re wanting to paste some data into table cells that already exist in Word. Inserting a linked ‘table’ from Excel into a Word table is possible, but it would propbably take far more work that setting up the whole table as you want it in your Excel workbook, and linking to that. For example, inserting an Excel range into a Word tanble means you’d probably have to use an single table-width cell in Word to paste the Excel data into (in ‘formatted text (RTF) format), then adjust column widths, row heights, etc (plus the unwanted paragraph marker that Word will insist on putting there for you), to match the rest of your table.

If you don’t want to go down that path, you can use the same linking process for individual Excel cells, pasting them into your Word table one at a time and pasting the links as ‘unformatted text’, but then you lose the flexibility of being able to accommodate ranges in Excel that grow/contract (unless you simply mean that some of the cells will sometimes be empty).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Many thanks for that, thants great.

Reply | Quote

Attached is the range l would like to go automatically into Word, is this possible?

Reply | Quote