VLOOKUP lookup_value file name

Greymud

New Member
Joined
Feb 19, 2016
Messages
33
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,668
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top