Working with multiple files/workbooks cross linked

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
0
MikeROz (103)
9/10/2008 1:31:01 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
643 Views

Similar Articles

[PageSpeed] 50

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.
-- 
Gary''s Student - gsnu200803


"MikeR-Oz" wrote:

> 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
0
GarysStudent (1572)
9/10/2008 4:08:01 PM
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

tudent" wrote:

> 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.
> -- 
> Gary''s Student - gsnu200803
> 
> 
> "MikeR-Oz" wrote:
> 
> > 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
0
MikeROz (103)
9/12/2008 12:15:02 PM
use the option to save as workspace( i am using excel 2007) it is under the 
view tab.

"MikeR-Oz" wrote:

> 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
> 
> tudent" wrote:
> 
> > 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.
> > -- 
> > Gary''s Student - gsnu200803
> > 
> > 
> > "MikeR-Oz" wrote:
> > 
> > > 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
0
9/15/2008 11:56:01 PM
Reply:

Similar Artilces:

PNG files not displayed
Help! I have a problem where PNG files I insert into Publisher aren't correctly displayed. The image place-holder is visible, but no image. Other graphics file-types are fine, but not PNG's. Another user with identical hardware and software can view the same PNG's without problems. I've tried resintalling Office and service packs with no success. However, when printing the publication, the PNG graphics are produced with no problems. I'm running Office 2003 sp 1. Thanks in advance! Casey. Casey <Casey@discussions.microsoft.com> was very recently heard to utte...

Problem to open excel file
Hi to all, I have a problem with all excel file. The user can't open all excel file when she did a double click on it. She has this error message : "cannot find the file 'c:\documents and settings\desktop\......test.xls' (or one of its components). Make sure the path and filename are correct. And that all required libraries are available" Do you know what can I do to solve the issue?? regards Malcolm Your Excel file association may be lost. Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You may have to en...

Excel is creating temp files Help!!!
Hi i have to files in excel, i cant figure it out, whenever i open th files, they create temp files into the same location, when i shut dow the program the temp files are left there. Is their a way to make it so temp files are not saved. Or is their a way to make it so that the creation of temp files i turned off. Thanks jaso -- greenfalco ----------------------------------------------------------------------- greenfalcon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1362 View this thread: http://www.excelforum.com/showthread.php?threadid=26182 What do th...

Importing a Lotus file with a *.123 extension
My company uses Excel 97 and 2000 and we've received a Lotus file with a *.123 extension. Excel will not allow me to open it as it does not have the *.wk? format. I searched the web and while I found numerous people with the same problem I found no one who had the conversion answer. Any help would be greatly appreciated. Thanks Find someone with Lotus ver 9 or higher. Have them open the file, and save it as *.wk4 If you dont have Lotus available and want you can send it to me and I will do it for you Thanks "jim" <jimsto@gorbel.com> wrote in message news:088d01c35...

Navigation Bars
I put Navigation bars on my publisher website document. When I try to publish to a file or to the web, the Navigation Bars do not appear on the page(s). How do I resolve this?? Answer found in the web group. DavidF "Tom Snyder" <Tom Snyder@discussions.microsoft.com> wrote in message news:7A55B689-FE9D-4E1E-9B6C-3ED571156D46@microsoft.com... >I put Navigation bars on my publisher website document. When I try to >publish > to a file or to the web, the Navigation Bars do not appear on the page(s). > How do I resolve this?? Tony, did you get an answer to this...

Linking Combo box to pivot table?
Hi All, I have a pivot table which has teams on the left, months on the top and a count of the calls in the data area. What I would love is that if I could have a combo box on a worksheet which when I select a team from its list it only shows me that teams data in the pivot table. Is this possible please?? -- Adam ----------- Windows 98 + Office Pro 97 You could move Team to the page area of the pivot table. Select a team, and the pivot table will only show its data. Adam wrote: > Hi All, > > I have a pivot table which has teams on the left, months on the top and a > c...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Send/Receive at Launch does not work
I cannot get send/receive to work when I launch Outlook 2003. I went to the knowledge base and found "Microsoft Knowledge Base Article - 312336" and tried it but it didn't do anything. Any help on how to get this working? Thanks Anyone have an idea here? What automatic polling interval did you set? -- Russ Valentine [MVP-Outlook] "Doc" <doc@teamexs.com> wrote in message news:37ec01c3b071$2d7aee50$a601280a@phx.gbl... > I cannot get send/receive to work when I launch Outlook > 2003. I went to the knowledge base and found "Microsoft > Knowledge ...

How Do I Copy My Outlook Files To My New Computer?
I exported my Outlook files from my old computer and burned them to a CD. During the export process, it asked me three times for a password. When I tried to import these files to my new computer I got "File access is denied. You do not have the permission required to access the file." Can anyone help? Apart from the fact that Import/Export options often lead to corruption - you should use a simple pst copy - The pst needs to be on the hd and the read only flag off, before In OL attempting to open it. NB not import <aprice6@hotmail.com> wrote in message news:1145746658.84...

Outlook and Hotmail -- now works again! For some not all?
Received from MS: <<Recently, Hotmail� announced that ... Hotmail will no longer allow new e-mail accounts to be accessed via Microsoft� Office Outlook� and Outlook Express. [BUT....] <<We are pleased to inform you that because you are an existing and valued customer, at this time your current Hotmail and MSN account(s) are exempt from this restriction and you will be able to continue enjoying access to those accounts from Outlook or Outlook Express. However, any new Hotmail or MSN accounts you create will not be accessible via Outlook or Outlook Express.>> ....an "...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

Why does copy and paste between eMails not work?
Copy no longer copies to the clipboard. Is there a setting I have deleted somewhere? Using Windows 7 and Office 2007 proffessional. Does it work if you restart outlook? Do you have any addins installed that could be erasing the clipboard? I've seen this happen with addins and contacts, but not email. to test, open the new message form before copying so you just need to copy and switch to the other message. (http://www.slipstick.com/Contacts/clipboard.htm) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

viewing LN emails (.nsf files) saved to CD in outlook?
I have lotus notes messages saved on CD, these are saved as .nsf files. Is there any way I can view these in Outlook? have tried various LN websites - to no avail. ...

automatically close second workbook 12-16-09
i have put the following code in to workbook 'A' to close workbook 'B' but it does not close book 'B' there is no error message Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Workbooks("B.XLS").Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=True End Sub Several of us have tested your code..........both workbook_open which opens B.xls when A.xls opens and the workbook_beforeclose code. Works fine for me. All I can think of is that you are opening B.xls in a separate instance of Excel. If t...

Using XP Briefcase with money file
I put a copy of my money file into the laptop briefcase and I am able to keep them in sych for a while. For some reason I get locked out, the computer knows the file has been updated but it will not let the desk top file update the laptop file, I get the following message; "The source file can not be opened" "Check to see if the other program has the file opened, and close the file before updating the briefcase." Other files update just fine. When I get this message, I can't even drag a new copy of the Money file to my briefcase, I have to rename the file an...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Windows XP: Open file in read-only: Word, Excel, Visio
I'm often sharing files on a network drive with someone. Often, one of us knows that we don't need to open a file for modification, and we only need read-access. Is there a way to open a file in read-only mode so that when someone does need to open it for modification, the read-only user is not blocking that? If there is not a way to do this native to Windows XP, is there a way to do it from the three applications that we use most, namely Word, Excel, Visio? All are 2003 versions. Thx. I used to save files like this in "read only recommended" mode. File|SaveAs|Tools|Ge...

Change color of multiple autoshapes
I need to change the color of several autoshape based on different cells I know how to change one autoshape using a worksheet_change event but i can't just copy and paste this and change the object name + cell name. is it possible to have multiple worksheet_change events in the same worksheet?? T-bone, You have only one worksheet_change event, but in it you can test to see which cell was changed with something like If not Intersect(Target, Range("A1") is nothing then ' do range A1 stuff here end if If not Intersect(Target, Range("A2") is nothing then &#...

outlook adreddbook file
I want to export the addressbook flie into database to be uses in a quiry. is it possible and how do i do it? Outlook has no separate "address book file." Contact data is stored in your Contacts Folder Use the Export Wizard to export it to the format that best suits your needs. -- Russ Valentine [MVP-Outlook] "Helpneeded" <derontayp@aol.com> wrote in message news:261e01c3af79$7d94e740$a601280a@phx.gbl... > I want to export the addressbook flie into database to be > uses in a quiry. is it possible and how do i do it? ...

Contact file not visible when importing or exporting contacts
I'm tidying up all my contacts files for email and also for my mobile ph using outlook. Have done this many times without any problems and am familiar with import export process. I exported all the contacts to an excel file to work on as it's much easier than through outlook. Had a hiccup at first as when I got to the select a contact file to export there was just a blank space. I exited outlook and went back in and it was all OK. Now I've finished the excel spreadhsheet and want to reimport it but it's the same problem in reverse. I can't "see" a co...

Force command to run for each computer in txt file list of compute
I have a few hundred computers that haven't been defragged in years. I was wondering if rather than doing it manually, if someone had a script where if i just put all the computer names in a txt file, it would iterate through the list and perform a "defrag c: -f" on them all from my workstation. The workstations all have the same local administrator password, so the process would be the same for each. I just dont know how to write scripts. I know if i were going to do this manually from my computer I would run the command below for each ComputerName psexec ...

How to remove the /dde from Windows Explorer File Open on Install
I have an MDI app that I would like to open a file on a double click in Windows Explorer. Unfortunately the Explorer File Type (Tools>Folder Options... File Types tab) is set so that the command invokes dde. Ie. The "open" option under the "Advanced" button is C:\Path...\App.exe /dde and the DDE message is [open("%1")]. This never seems to work as my app never sees the dde message and the initialization of m_pCmdInfo via: m_pCmdInfo = (CCommandLineInfo*)(UINT_PTR)m_nCmdShow; sets the pointer to one and the app fails on exit. This is referred as a kn...

How do I add multiple times together
Hi does anyone know how I can add multiple times togther and get the anser in hours and minutes. I have formatted the cell for time however when I atosum I keep getting an answer that is incorrect. Any help? Thanks D Maybe it was just a formatting problem. Try a custom format of: [hh]:mm Playhouse pm wrote: > > Hi does anyone know how I can add multiple times togther and get the anser in > hours and minutes. I have formatted the cell for time however when I atosum I > keep getting an answer that is incorrect. Any help? > Thanks > D -- Dave Peterson ...

Works in 2003 but not 2007
When I reference the below function in 2003 I get function displayed... now in 2007 I get #NAME? Any idea what I need to change/set in 2007 so it will run this function? Function formulaText(x As Range) 'returns the text equivalent of formula in upper left cell in range x 'example: =formulatext(a1) returns the formula found in cell a1 of active worksheet 'example: =formulatext(a1:b5) returns the formula found in cell a1 of active worksheet formulaText = x.Cells(1, 1).Formula End Function Thanks, Mark Mark, It works fine for me. Are you stored in a stan...

Publisher 2003 files try to open in Publisher 97 #2
Back again, but with a different Pub 2003/Pub97 issue Have Office 2003 with Publisher 2003 and Publisher 97 both on my XP Pro system Recently I find that files created in Pub 2003 are being saved as files with the Pub 97 Logo, and when you try to open them from the file listing, they open Pub 97, which correctly posts a box telling me that it cant open the files. If I use Publisher 2003 to open the same files from within the application the work fine. When I select a 2003 Pub file which contains the Pub 97 icon, the file list does not contain Publisher 2003, but when I browse and locat...