Lampshade54321
New Member
- Joined
- Nov 21, 2009
- Messages
- 2
This issue relates to automating with VBA code the archiving of upto 40 linked workbooks whilst maintaining the integrity of the workbook links.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>
<o></o>
I manage a large project and have six cell teams reporting progress schedules and financial information to me each month with excel workbooks. I take all of the cell teams workbooks and analyse the data they report.<o></o>
<o></o>
I have set-up a folder system on our network that has a similar structure for each Cell Team and the workbooks they use are similarly named but are identified by the Cell Team's prefix CT1, CT2 etc. <o></o>
<o></o>
The excel workbooks are called MASTER and are being updated with information on a daily basis but at the end of every month I have to create a month end snap-shot (a set of linked workbooks in a stable condition no external references outside of the archive folder). A copy of each Cell Team's workbooks is made in a single folder called R1 (for month 1) and given a Report number prefix R1 (month 1).<o></o>
<o></o>
Now the time consuming part of the exercise is to open up each workbook >edit links>change source and re-assign the link to the equivalent archived workbook.<o></o>
<o></o>
In other words:<o></o>
Master Files for CT1 are in the folders:<o></o>
X:\CT1\Progress\CT1-ProgressSchedule-MASTER.xls<o></o>
And links to<o></o>
X:\CT1\Milestones\CT1-Milestones-MASTER.xls<o></o>
and<o></o>
X:\CT1\ProgressClaims\CT1-PCs-MASTER.xls<o></o>
<o></o>
Similarly CT2's Master files will be in the folders:<o></o>
<o></o>
X:\CT2\Progress\CT2-ProgressSchedule-MASTER.xls<o></o>
links to<o></o>
X:\CT2\Milestones\CT2-Milestones-MASTER.xls<o></o>
etc.<o></o>
<o></o>
At the end of a month the files will be saved and links changed thus:<o></o>
<o></o>
X:\R1\R1-CT1-ProgressSchedule-MASTER.xls<o></o>
links to<o></o>
X:\R1\R1-CT1-Milestones-MASTER.xls<o></o>
and<o></o>
X:\R1\R1-CT1-PCs-MASTER.xls<o></o>
<o></o>
Similarly for CT2<o></o>
X:\R1\R1-CT2-ProgressSchedule-MASTER.xls<o></o>
links to<o></o>
X:\R1\R1-CT2-Milestones-MASTER.xls<o></o>
and<o></o>
X:\R1\R1-CT2-PCs-MASTER.xls<o></o>
<o></o>
Has anyone solved this sort of problem before? <o></o>
And, do you have any suggestions for VBA code?<o></o>
fficeffice" /><o></o><o
></o>I manage a large project and have six cell teams reporting progress schedules and financial information to me each month with excel workbooks. I take all of the cell teams workbooks and analyse the data they report.<o
></o><o
></o>I have set-up a folder system on our network that has a similar structure for each Cell Team and the workbooks they use are similarly named but are identified by the Cell Team's prefix CT1, CT2 etc. <o
></o><o
></o>The excel workbooks are called MASTER and are being updated with information on a daily basis but at the end of every month I have to create a month end snap-shot (a set of linked workbooks in a stable condition no external references outside of the archive folder). A copy of each Cell Team's workbooks is made in a single folder called R1 (for month 1) and given a Report number prefix R1 (month 1).<o
></o><o
></o>Now the time consuming part of the exercise is to open up each workbook >edit links>change source and re-assign the link to the equivalent archived workbook.<o
></o><o
></o>In other words:<o
></o>Master Files for CT1 are in the folders:<o
></o>X:\CT1\Progress\CT1-ProgressSchedule-MASTER.xls<o
></o>And links to<o
></o>X:\CT1\Milestones\CT1-Milestones-MASTER.xls<o
></o>and<o
></o>X:\CT1\ProgressClaims\CT1-PCs-MASTER.xls<o
></o><o
></o>Similarly CT2's Master files will be in the folders:<o
></o><o
></o>X:\CT2\Progress\CT2-ProgressSchedule-MASTER.xls<o
></o>links to<o
></o>X:\CT2\Milestones\CT2-Milestones-MASTER.xls<o
></o>etc.<o
></o><o
></o>At the end of a month the files will be saved and links changed thus:<o
></o><o
></o>X:\R1\R1-CT1-ProgressSchedule-MASTER.xls<o
></o>links to<o
></o>X:\R1\R1-CT1-Milestones-MASTER.xls<o
></o>and<o
></o>X:\R1\R1-CT1-PCs-MASTER.xls<o
></o><o
></o>Similarly for CT2<o
></o>X:\R1\R1-CT2-ProgressSchedule-MASTER.xls<o
></o>links to<o
></o>X:\R1\R1-CT2-Milestones-MASTER.xls<o
></o>and<o
></o>X:\R1\R1-CT2-PCs-MASTER.xls<o
></o><o
></o>Has anyone solved this sort of problem before? <o
></o>And, do you have any suggestions for VBA code?<o
></o>
