Lookup a file based on user input

catrinawhite

New Member
Joined
Oct 2, 2006
Messages
6
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Oct 2, 2006
Messages
6
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.)
Code:
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

New Member
Joined
Oct 2, 2006
Messages
6

ADVERTISEMENT

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

catrinawhite

New Member
Joined
Oct 2, 2006
Messages
6
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.
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,542
Latest member
shablagoo

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