Link manager (Excel 2002)

Topic

New Reply

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 ?

Reply | Quote

Replies

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:

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

Reply | Quote

Thanks Jan, I will try both methods.

Reply | Quote

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.

Reply | Quote