Hi all,

I got a large number of Excel files, all with a reference (in the VBA editor) to another file. I wonder if it's possible to programmatically change the reference in all files, to save the repetitive work of changing each manually?

Gustaf

I don't think its possible because the compiler would break.
Why not just rename the original referenced file -- as backup- and rename
the new file with the original file referenced name?

"Gustaf" wrote:

> Hi all,
>
> I got a large number of Excel files, all with a reference (in the VBA editor) to another file. I wonder if it's possible to programmatically change the reference in all files, to save the repetitive work of changing each manually?
>
> Gustaf
> .
>

References are always to "another file". Depending on the type reference, ie
file, it might be easy, unreliable, or impossible. Describe "another file".

Regards,
Peter T

"Gustaf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all,
>
> I got a large number of Excel files, all with a reference (in the VBA
> editor) to another file. I wonder if it's possible to programmatically
> change the reference in all files, to save the repetitive work of changing
> each manually?
>
> Gustaf

It depends. I recently wrote a macro to open all files in a folder and
delete a procedure in a worksheet_change event and insert a new one >save
and close the file.
Details?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Gustaf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all,
>
> I got a large number of Excel files, all with a reference (in the VBA
> editor) to another file. I wonder if it's possible to programmatically
> change the reference in all files, to save the repetitive work of changing
> each manually?
>
> Gustaf

Have a look at Chip Pearson's site (http://www.cpearson.com/excel/vbe.aspx).
He has got some good clues on programing at the vbe editor level. There is
definitely an .addreferences method in the VBE command set. You may be able
to set up a loop that programmatically opens each file then redefines the
reference.

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Patrick Molloy" wrote:

> I don't think its possible because the compiler would break.
> Why not just rename the original referenced file -- as backup- and rename
> the new file with the original file referenced name?
>
> "Gustaf" wrote:
>
> > Hi all,
> >
> > I got a large number of Excel files, all with a reference (in the VBA editor) to another file. I wonder if it's possible to programmatically change the reference in all files, to save the repetitive work of changing each manually?
> >
> > Gustaf
> > .
> >