How can I create a Vlookup to certain columns in a closed workbook?

[HeatherA]

I currently have a master file with data and copy copy my master file over 17 other excel files to update them monthly. How can I create a Vlookup from each separate file to the columns of data that I need in the master file even when the master file is closed?

Thanks for the help.

[mdmackillop]

Hi Heather
Welcome to VBAX
Sorry, I don't understand the question.
Regards
MD

[Aussiebear]

Hi MD, I believe that HeatherA is trying to reference data in a closed Workbook (Master) when working in any one of the 17 workbooks that she is trying to update monthly.

[marshybid]

Hi HeatherA,

MD and xld helped me with the code below which enables me to gather VLOOKUP data from an unopened file


Private Sub AddVlookUp()
Const FILEPATH As String = _
"Place your filepath here for the Vlookup data"
Dim i As Long
Dim k As Long
Dim LastRow As Long

With Sheets("Place Sheet Name here")
For i = 1 To 3

For k = 1 To 125

.Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _
"[Place your Workbook name here.xls]Place Sheet Name here'!R" & k & "C" & i
Next
Next
End With
End Sub

i is your column references (1 to 3 as above = A, B & C)
k is your row reference (1 to 125 as above)

This should help you

Marshybid :hi:

[HeatherA]

Maybe I didn't explain it well the first time. I have 18 workbooks with basically the same data. 1 is a master file that I update monthly and the other 17 are for each of the 17 people I work with. Currently i have to update the master file and then copy the data into the other 17 files to update them with the new data. I would like to be able to just update the master file and have the other 17 automatically update.

The problem I ran into with my Vlookup is that it only works when the master file is open, however most of the time the file is closed. Can anyone suggest code that will make this work? I tried the code that Marshybid gave but it didn't work.

Thanks!
Heather

[rolly]

Will this work?
I'm running Excel 2003 and it can reference it my file without forcing it to be open.

VLOOKUP(C18,'\\Network-folder name\sub-folder-name\[Name-of-file-to-look-in.xls]sheet-name'!$C$45:$D$56
of course the cell addresses would need to be changed to suit your needs.

Rolly