macro to search & open file

  • Thread starter Thread starter Mayte
  • Start date Start date
M

Mayte

If i type a file name in cell "A2", is there a way to pick up that file name
from cell A2, look in a specific location and open it? and if not found,
display a message that file was not found?

Cheers,
Mayte
 
Mayte,

Right-click on the sheet tab and choose View Code. In that code
window, paste the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FName As String
Dim Path As String
Dim WB As Workbook
If Target.Address <> "$A$2" Then
Exit Sub
End If
' Change Path to the desired folder name
Path = "C:\Test" '<<<< CHANGE
FName = Path & "\" & Target.Text
If Dir(FName) = vbNullString Then
' file does not exist
End If
' ensure that the workbook is not already open
For Each WB In Workbooks
If StrComp(FName, WB.FullName, vbTextCompare) = 0 Then
Exit Sub
End If
Next WB
Workbooks.Open Filename:=FName
End Sub


This will look at cell A2 and open the file if it exists and is not
already open. Change the Path value as necessary.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Thanks a lot !!!

one more favour ..anyway to search in a folder and all sub-folders?

cheers,
mayte
 
Back
Top