links word to excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 07 Nov 2007 04:49 GMT

Hello,

We have a Word document with approx 90 fields linked to an Excel
workbook. What we're trying to do is have an Excel VBA procedure step
through a table in the workbook, copy each record to the linked cells,
refresh the links and print the document, then on to the next record,
etc. The number of records is small (under 50), but the length is 86
fields (cells)

This idea would appear to work in theory, but it runs extremely slowly
and suffers from frequent crashes (usually Word). It seems to work Ok
when I drastically cut back the number of links, but the crash
frequency goes up as the number of links goes up.

Is the way to go, does anyone have any thoughts about this? To date I
haven't found anything on the Web or groups that quite addresses
this. Also, searching the Help didn't reveal any info re the maximum
number of links. I'm running Office 2007.

Thanks for your help,

Regards,

Dave

Reply to this Message

Bill Manville - 07 Nov 2007 07:22 GMT

Have you considered using Mailmerge?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Dave - 08 Nov 2007 04:45 GMT

Hi Bill,

Thanks for pointing me in that direction. As one who has always
worked with Excel, but rarely with Word, MailMerge just never occured
to me. All the same, I'm still not sure why those links didn't work
as expected.

Thanks again,

regards,

Dave

> Have you considered using Mailmerge?
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

macropod - 10 Nov 2007 10:21 GMT

Hi Dave,

If these are the same Excel cells you're referencing each time, simply copying them once into Word with Edit|Paste Special and
checking the 'Link' option will set the links up permanently. Then all you'll need to do is to refresh them periodically, which is
as simple as opening the Word document.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> Hi Bill,
>
[quoted text clipped - 14 lines]
>> MVP - Microsoft Excel, Oxford, England
>> No email replies please - respond to newsgroup

Reply to this Message

Dave - 12 Nov 2007 05:49 GMT

Hi macropod,

Thanks for your response.

Yes, that's how we had them set up, and they worked fine in a semi-
static evironment. What we were doing was stepping through a table in
Excel and updating the linked cells with each record (using VBA from
Excel), then printing the linked Word document.. In other words, we
were attempting to do a batch print job, using the Excel data and
printing it on a Word document (form).

It was this dynamic updating and refreshing that was bringing the
system to it's knees. As Bill ponted out, MailMerge was probably the
better way to go, but as it turned out, we converted the Word form to
Excel, so thus eliminated the problem.

thanks,

Dave

> Hi Dave,
>
[quoted text clipped - 28 lines]
>
> - Show quoted text -

Reply to this Message

macropod - 12 Nov 2007 07:04 GMT

Hi Dave,

For future reference, updating the links in Word by code could be as simple as 'ActiveDocument.Fields.Update'

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> Hi macropod,
>
[quoted text clipped - 49 lines]
>>
>> - Show quoted text -

Reply to this Message

Dave - 12 Nov 2007 15:24 GMT

Hi macropod,

That's what we were doing!

There was a lot of disk swapping going on while running our 'batch
print' application, it seemed like the process was maxing out the
system resources (most likely memory), even though the application
itself was quite small.

regards,

Dave

> Hi Dave,
>
[quoted text clipped - 61 lines]
>
> - Show quoted text -

Reply to this Message