renaming linked spreadsheets and reports

Workpaper1 is linked to report 1. When both are then saved as version 2.....report 2 continues to be linked to workpaper 1 instead of 2.

Is there a way to change the link automatically when linked documents are both saved with different names?

Thanks

Howdy, and welcome to the board. I think there is via vba, but I need more info. Real file names, file path of the linked file, might as well make it work for you versus an example, not sure how comfortable you'd be with changing it...

No, but Edit-Links should take you a few seconds.

Thanks for the quick reply Nato. I will give that a try. I haven't done any vb work or any programming actually. I'll try this out and post any questions.

Thanks again.

Howdy, well, just too eager to post and no response, so here's an example. In the workbook WorkpaperX, I right click on the Excel icon next to the File menu heading, I then left-click on view code to access the workbook class module, which is required for the following workbook event procedure.

I then paste the following code:



Private y As Boolean



Private Sub Workbook_BeforeClose(Cancel As Boolean)

y = True

End Sub



Private Sub Workbook_BeforeSave(ByVal myVar As Boolean, Cancel As Boolean)

If Not (y) Then Application.OnTime Now, "ThisWorkbook.AfterSave"

End Sub



Private Sub AfterSave()

Dim MyDir As String, fso As Object, Nme As String, y As Long, oldLinks

Set fso = CreateObject("Scripting.FileSystemObject")

MyDir = "c:temp" 'Change Directory

Nme = "Report " & Replace(ThisWorkbook.Name, "Workpaper", "") '"Report #"

If fso.FileExists(MyDir & Nme) Then

    oldLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

    For y = LBound(oldLinks) To UBound(oldLinks)

            ThisWorkbook.ChangeLink oldLinks(y), MyDir & Nme

    Next

End If

End Sub

The code does a few things. First, it waits for the save to grab the new name, not the old one. Second, it looks for Report X on the c:temp drive, if it finds it, it proceeds to update the links, otherwise it aborts.

Questions or concerns, please let us know. Hope this helps.

Edit: Added code so that if you save on close, Excel won't fire the application level ontime procedure, which will attempt to reopen the workbook.

#If you have any other info about this subject , Please add it free.#

Search

Categories

Recent Posts

Archives

Blogroll