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.

You could make them hyperlinks...

Cliff Edwards

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


"ward376" wrote:

You could make them hyperlinks...

Cliff Edwards

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" wrote:

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


"ward376" wrote:

You could make them hyperlinks...

Cliff Edwards

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

"Doug" wrote:

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" wrote:

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


"ward376" wrote:

You could make them hyperlinks...

Cliff Edwards