variant on copying excel data into word table

[rockhammer]

I am using excel 2003 in xp.

I want to create a macro in word that will, or do the equivalent of, open an
excel file, copy data from certain cells and paste that data by values into
the word file. My environment is somewhat different than those assumed in
answers I've seen posted here before.

Consider this:
1. I have 2 PCs, PCa & PCb, which share a common file system.
2. On PCa I have an excel file linking to an external data source -
depending on the input I enter into this file, different values get pulled
into the cells. Once the values are pulled in, I save this file.
3. On PCb, which does not have access to the above external data source (and
so I cannot open the excel file from the word macro since the links in the
excel file will show garbage), I have my word file where I want to create a
macro to insert values into cells of a table using the data that is saved
into that excel file.
4. I want a macro because this will be done many times.

My question is what is the best way to accomplish that?

I can imagine I need an intermediate step such as creating a word/excel
template that links to that excel file on PCa, and then copy from that
template to paste values into my word file. And then I would need to find a
way to have this intermediate template to refresh to pull in new data as they
get changed. But how?

Your help would be most appreciated.

 

[Peter Jamieson]

When you say "which share a common file system" do you mean
a. you have two PCs that are completely independent from each other,
but have the same folder structure (e.g. PCa has a folder called
c:\myexcelwbs where you keep Excel workbooks, and PCb also has a folder
called c:\myexcelwbs where you keep Excel workbooks or
b. you have two PCs that are networked and which share a folder where
you keep, or could copy, your Excel workbooks?

Or what?

Clearly, if PCb cannot get at your Excel file, there is little you can
do. If it can, but on PCa your file is called c:\myexcelwbs\mywb.xls and
on PCb you have to reference it as \\a_sharename_on_PCa\\mywb.xls or
z:\mywb.xls. I think you have to know your own folder naming convention
in order to make progress, or to create a convention that makes it
relatively easy to adjust references as you move code and documents from
one computer to another.

Or are you talking about something else altogether?



Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv