CODEWITHSUNDEEP
excelvba
Patrick
Patrick

Reputation: 53

Linking cells to previous workbooks

If i may also ask: I have cashup files that have certain links..December takes info from certain cells in November. Would it be possible that when i open December to auto collect links from November with out having to change "=+'C:\Stores\Fego Caffe Irene\Cash Ups\2011[Fego Caffe June 2011.xlsm]Daily Breakdown'!$E$42" everytime i save a new month. so if i create a January file it must auto link to December without me having to manually change the formulas the whole time??

Basically asking. I have just created a January 2013 cashup file which pulls info from December 2012. Now the formula i have in January cell is "=+'C:\Stores\Fego Caffe Irene\Cash Ups\2012[Fego Caffe November 2012.xlsm]Daily Breakdown'!$E$42" Now what is happening is that i now have to unprotect the sheet and manually change November to December so tha January can link to December 2012 file.

Is it possible to have a VB macro that when you open January file it auto links to December 2012 and so on for other months. Current month takes from previous month.

Sorry still very new at this

Thanks for all your help!!!!

Upvotes: 0

Views: 119

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

You can use Workbook.LinkSources to get a list of linked files, then change any of them you wish with Workbook.ChangeLink. Refresh data with Workbook.UpdateLink

Here's a macro you can call after creating a new Months workbook.

It will open a dialog box for you to select the file to link to

For simplicity it is written to include the Protection password in code, but you know how to prompt for it, so I'll leave that to you.

Sub UpdateLink()
    Dim strFile As String
    Dim aLinks As Variant
    Dim i As Long
    Dim strLink As String
    Dim strLinkNew As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Show

        If .SelectedItems.Count > 0 Then
            strLinkNew = .SelectedItems(1)

            aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
            If Not IsEmpty(aLinks) Then
                For i = 1 To UBound(aLinks)
                    strLink = aLinks(i)
                    If strLink Like "*\Fego Caffe Irene*.xlsm" Then

                        ' Change Linked File
                        ThisWorkbook.Worksheets("Links").Unprotect "YourPassword"
                        ThisWorkbook.ChangeLink Name:=strLink, NewName:=strLinkNew, Type:=xlExcelLinks
                        ThisWorkbook.Worksheets("Links").Protect "YourPassword"
                    End If
                Next
            End If
        End If
    End With
End Sub

One thing worth noting: you decalre some but not all your variables. If you put Option Explicit at the top of all your modules it will force you into the good practice of always declaring variables.

Upvotes: 2

Related Questions