Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link manager (Excel 2002)

    Concerning my rather large file (50 sheets), I may need to divide it into several smaller files, which will need to be linked. This file is a template to be used for structural design of different building projects. So the files for each project will need to be linked, to the exclusion of the files for other projects.
    In other words, once a project is created from the template, the links need to be revised to refer to the other files for the same project. This is not a very stimulating task, and I would like to automate it.
    Is there a link manager of the kind available in Access ?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link manager (Excel 2002)

    If you open al files which are interlinked in one session of Excel and save-as those files one by one, all links will refer to the newly saved files.

    It wouldn't be hard to write some code that saves all open workbooks using e.g. their name followed by a project number:

    <pre><font color=blue>Sub</font color=blue> SaveAsAllFiles()
    <font color=blue>Dim</font color=blue> oBook <font color=blue>As</font color=blue> Workbook
    <font color=blue>Dim</font color=blue> sProject <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> sName <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    sProject = InputBox("Please enter project number or code")
    <font color=blue>If</font color=blue> sProject <> "" <font color=blue>Then</font color=blue>
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> oBook <font color=blue>In</font color=blue> Workbooks
    sName = oBook.Name
    <font color=blue>If</font color=blue> InStr(oBook.Name, ".xls") > 0 <font color=blue>Then</font color=blue>
    sName = Left(sName, InStr(sName, ".xls") - 1)
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    sName = sName & " " & sProject
    oBook.SaveAs sName
    <font color=blue>Next</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    End <font color=blue>Sub</font color=blue></pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link manager (Excel 2002)

    Thanks Jan, I will try both methods.

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link manager (Excel 2002)

    Jan, it was also saving Personal.xls with the project number, so I added a line to detect that with
    If sName <> "PERSONAL.XLS" Then
    ' Add the project number
    ' Save as ...
    End If

    Works fine.
    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •