using cell value to open file

[Doug]

It's been several years since I've been on this site, boy have things
changed. I posted a few days ago and lost it somewhere, so I'm going to try
again.

I have a sheet listing job numbers, addresses, names, etc. I have a budget
for each job saved as separate files with the job number in the file name. I
would like to be able to click on any job number in my list, and have it open
the corresponding budget file. Any ideas.

Thanks for the help.

 

[ward376]

You could make them hyperlinks...

Cliff Edwards

 

[Joel]

Try this code. Change folder as required

Sub OpenJobFile()

Folder = "C:\temp"

JobNumber = ActiveCell.Value

FName = Dir(Folder & "\*" & JobNumber & "*.xls")

If FName = "" Then
MsgBox ("Cannot find file : " & Folder & "\*" & JobNumber & "*.xls")
Else
Workbooks.Open Filename:=Folder & "\" & FName
End If

End Sub

 

[Doug]

That looks great, I'll try it. Here's another issue.

The job numbers are grouped by subdivsion, so if I have job numbers in
SubDivRange1 or SubDivRange2, etc., how do I find the range name so I can
include that name in the file path with the job number?

 

[Joel]

Not sure if this is the best method, but it works. Use intersect to
determine if cell is in the range of each name in the workbook.

RangeName = ""
For Each nm In ActiveWorkbook.Names
NameAddr = nm.RefersToRange.Address
If Not Intersect(Range(NameAddr), ActiveCell) Is Nothing Then
RangeName = nm.Name
Exit For
End If
Next nm
If RangeName <> "" Then
'enter your code here
End If