Lookup a file based on user input

[catrinawhite]

I have approximately 150 seperate excel files. What I am attempting to do is have the user enter a value in Worksheet A, then I need it to take that value and find the corresponding file and display it in worksheet A. Is this possible?

 

[HalfAce]

Hello catrinawhite, welcome to the board.
I need you to clarify a few things to fully understand what you want to do.

I need it to take that value and find the corresponding file . . .

Will all files being searched for share the same file path as the workbook the user is
making their entry in?

. . . and display it in worksheet A.

What exactly would you like to display in worksheet A?

Also (less important) how do you want to execute this macro, with a button or by just
making the entry? - In either case, what range will the entry be made in?

 

[catrinawhite]

Yes, all files will have the same file path. The file names correspond to what is being entered in B2 of worksheet A. Each file has different data that I need to display within worksheet A. It would would be awesome if it could display it just by making the entry in B2.

 

[HalfAce]

OK, try this in the sheet module for the sheet of choice. (In the workbook you want to
make the B2 entry in and have the copied data pasted to.)
Right click the sheet tab (of the sheet you'll make the B2 entry in) and choose View code.
Paste this into the sheet module. (White area on the right.)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim strDir$, fName$
strDir = ThisWorkbook.Path & "\"
fName = Target.Value & ".xls"
Application.ScreenUpdating = False
On Error Resume Next
Workbooks.Open strDir & fName
If Err = 0 Then
  With Workbooks(fName)
    .Sheets("Sheet1").UsedRange.Copy ThisWorkbook.Sheets("A").Range("A1")
    .Close False
  End With
Else
  MsgBox ("The file '" & fName & "' does not exist in the folder being searched."), , fName & " not found"
End If
Err.Clear
Application.ScreenUpdating = True
End Sub

Does that do what you want it to do?

 

[catrinawhite]

You are awesome!! It works perfectly. Thank you!

 

[HalfAce]

Thank you for the kind words. I'm glad it helped.

 

[catrinawhite]

HalfAce - I need your help again!

Hello,
The below code that you gave me works perfectly. However, now they would like me to also display a picture along with the data. I have the pictures in a .jpg in the same directory with the same name of the value that is entered in B2. The only catch is that I need to to resize the picture to 3" wide by 4" high. Can this be done with a simple modification to this code?

Thank you in advance to any help you can give.