Working with multiple files/workbooks cross linked

[MikeR-Oz]

Hi,
It seems that the dozen workbooks I have created and the cross sharing of
nformation with some single filesworkbook sharing several other files /
workbooks need to have the files all open otherwise the paths seems to get a
bit mixed up.

Is there a way then that will open all files related to a group when I want
to work on a particular set of files but as mentioned get ALL the oher files/
workbooks open to ensure the links all work corretly?

Thanks Mike

 

[Gary''s Student]

Here is an example with three workbooks:

first.xls
second.xls
third.xls

Let's say that if one of the files is opened, we want them all opened.

Put the following workbook event code in the workbook code area of each file:

Private Sub Workbook_Open()
Set r = Range("A1")
f1 = "C:\Temp\first.xls"
f2 = "C:\Temp\second.xls"
f3 = "C:\Temp\third.xls"

ActiveWorkbook.FollowHyperlink Address:=f1
ActiveWorkbook.FollowHyperlink Address:=f2
ActiveWorkbook.FollowHyperlink Address:=f3

Application.Goto r

End Sub

The hyperlinking will jump to each of the files. If a file is not open, the
hyperlinking will open it. The goto just returns to the original workbook.

 

[MikeR-Oz]

Gary -

I appreciate the reply but I have NO idea what you are talking about in :

"Put the following workbook event code in the workbook code area of each file:

Private Sub Workbook_Open()
Set r = Range("A1")
f1 = "C:\Temp\first.xls"
f2 = "C:\Temp\second.xls"
f3 = "C:\Temp\third.xls"

ActiveWorkbook.FollowHyperlink Address:=f1
ActiveWorkbook.FollowHyperlink Address:=f2
ActiveWorkbook.FollowHyperlink Address:=f3

Application.Goto r

End Sub"

Where excatly is the "workbook code area' ?

I am working on a roster system for work-

I have a series of workbooks i.e files each containing several files. These
workbooks have the same overall shape/ format but will have in the formulae
basic VLOOKUP ain the first worksheet of the book with a =C3 ( or perhps that
should be a =C!3 command in the subsequent sheets to copy across for each
week (Mon - Friday actual days) in the Roster coming off the first sheet (6
week projection) .

But I then need to use the information contained in the Projection file -
workbook to poulate details in a seperate file - workbook. These workbooks
also contain a series of worksheets amounting to the daily roster in a
different format to the 'other' workbook of projections. This second type of
workbook has a formulae

='O:\Linked PTO rosters for all areas\[PTO Projection Blacktown
06_09_20081TEST.xls]6 WEEK ROSTER'!D9


So I wish to be able to group the workbooks and subsequent 'linked'
workbooks together and make a GLOBAL change to a formulae.

Is ths possible??

Cheers
Mike

 

[Steven Terry]

use the option to save as workspace( i am using excel 2007) it is under the
view tab.

Gary -

I appreciate the reply but I have NO idea what you are talking about in :

"Put the following workbook event code in the workbook code area of each file:

Private Sub Workbook_Open()
Set r = Range("A1")
f1 = "C:\Temp\first.xls"
f2 = "C:\Temp\second.xls"
f3 = "C:\Temp\third.xls"

ActiveWorkbook.FollowHyperlink Address:=f1
ActiveWorkbook.FollowHyperlink Address:=f2
ActiveWorkbook.FollowHyperlink Address:=f3

Application.Goto r

End Sub"

Where excatly is the "workbook code area' ?

I am working on a roster system for work-

I have a series of workbooks i.e files each containing several files. These
workbooks have the same overall shape/ format but will have in the formulae
basic VLOOKUP ain the first worksheet of the book with a =C3 ( or perhps that
should be a =C!3 command in the subsequent sheets to copy across for each
week (Mon - Friday actual days) in the Roster coming off the first sheet (6
week projection) .

But I then need to use the information contained in the Projection file -
workbook to poulate details in a seperate file - workbook. These workbooks
also contain a series of worksheets amounting to the daily roster in a
different format to the 'other' workbook of projections. This second type of
workbook has a formulae

='O:\Linked PTO rosters for all areas\[PTO Projection Blacktown
06_09_20081TEST.xls]6 WEEK ROSTER'!D9


So I wish to be able to group the workbooks and subsequent 'linked'
workbooks together and make a GLOBAL change to a formulae.

Is ths possible??

Cheers
Mike

Here is an example with three workbooks:

first.xls
second.xls
third.xls

Let's say that if one of the files is opened, we want them all opened.

Put the following workbook event code in the workbook code area of each file:

Private Sub Workbook_Open()
Set r = Range("A1")
f1 = "C:\Temp\first.xls"
f2 = "C:\Temp\second.xls"
f3 = "C:\Temp\third.xls"

ActiveWorkbook.FollowHyperlink Address:=f1
ActiveWorkbook.FollowHyperlink Address:=f2
ActiveWorkbook.FollowHyperlink Address:=f3

Application.Goto r

End Sub

The hyperlinking will jump to each of the files. If a file is not open, the
hyperlinking will open it. The goto just returns to the original workbook.