VLOOKUP lookup_value file name

[Greymud]

I have the feeling I'm going about this wrong, but here's where I am:

I'm making about 100 workbooks in a folder - each with a filename of site code for each location we are working on (coded in numbers) - and another workbook each of the first workbooks needs to pull it's information from called Data Entry. The Data Entry workbook has a Column A with site codes. I need to insert data from that row into the other workbooks.

Easy, right? I'm using

=VLOOKUP("1556",[Data Entry]Sheet1!$A1:$BL$100,5,FALSE) And it works fine. I don't want to edit 100+ workbooks. Is there a way to replace "1556" with the name of the file (minus .xlsx)? Is there a better way i can do this?

It's basically a mail merge, but I need it to be dynamic.

Thanks in advance!

 

[offthelip]

Try this code , I suggest you try it on a test folder with some dummy files in it to check that it is doing what you want. It opens every file in the folder that the main workbook is sitting in and then writes the equation you asked for in cell A1 of the first sheet. You need to add a reference to microsoft scripting runtime to use the FSO object.

Sub test()
' add reference to Microsoft Scripting runtime by selecting tool/ references in VBA window
    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim pathn As String
    pathn = ActiveWorkbook.Path
    thisname = ActiveWorkbook.Name
    
    Set myFolder = FSO.GetFolder(pathn)
           For Each myFile In myFolder.Files
            fname = myFile.Name
            tt = Asc(Left(fname, 1))
            If fname <> thisname And tt <> 126 Then ' check it is not this file or a temporary file
                Workbooks.Open Filename:=pathn & "\" & fname
                x = InStr(fname, "xlsx")
                fn = Left(fname, x - 2)
                Range(Cells(1, 1), Cells(1, 1)).Formula = "=VLOOKUP(" & Chr(34) & fn & Chr(34) & ",[Data Entry]Sheet1!$A1:$BL$100,5,FALSE)"
                ActiveWorkbook.Close SaveChanges:=True
            End If
           Next
End Sub