VLOOKUP using multiple files in a networkl

Microsoft Excel 2007
August 15, 2009 at 08:21:04
Specs: Windows XP Service Pack 3, v.5755
I was working with VLOOKUP using multiple files in a network.
Basically I have 3 files to link. 1) the main file - where the user encodes the data 2) the monthly summary - with which data are from the main file and 3) the annual report - data are from the monthly summary.
VLOOKUP worked fine. The problem comes when changes are being made to the main file. The values on the annual report file won't update unless I keep open the monthly summary file.
Is there a way to correct this?

See More: VLOOKUP using multiple files in a networkl

Report •

September 9, 2009 at 05:10:20
You could add a routine to automatically open then close any linked Excel files in the Annual Report workbook.
The following routine will do this, its fairly basic with no error checking. If a linked file was already open it will probably open a dialog box asking if you want to reopen the file. If this approach works for you, you could add a check for an already open file.

Put the following code in a standard VBA module in the Annual Report workbook

Sub UpdateLinks()
Dim varLinks As Variant
Dim strChar As String
Dim strWBName As String
Dim i As Integer
Dim n As Integer

'get all Excel workbook links into an array
varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
'get each workbook name (and path) and open it
For i = 1 To UBound(varLinks)
    Workbooks.Open (varLinks(i))
    'have an empty string ready for the filename (without path)
    strWBName = ""
    'get the filename by working backwards until a "\" character
    For n = Len(varLinks(i)) To 1 Step -1
        strChar = Mid(varLinks(i), n, 1)
        If strChar <> "\" Then
            strWBName = strChar & strWBName
        End If
        If strChar = "\" Then Exit For
    Next n
    'close the workbook we just opened
    Workbooks(strWBName).Close SaveChanges:=True
Next i
End Sub

Put a button on a suitable toolbar and link to this routine, and click the button to run it whenever updating the Annual Report, or you could make this run whenever the Annual Report workbook opens.

To do that, put the code in the 'ThisWorkbook' module in the Workbook_Open() routine (omit the' Sub UpdateLinks() 'and' End Sub' statements from the suggested code as the Workbook_Open() routine provides the name of the routine and the 'End Sub').

Hope this works or gives you some further ideas for solving your problem.

Report •
Related Solutions

Ask Question