Repost: Smart Hyperlink

[Kevin]

We currently operate in a peer-to-peer environment, but
are soon migrating to a file server. Our principal
currently maintains our project schedule in an Excel
worksheet residing on his PC's hard drive. We develop
several cost estimates and revisions thereof for each
project.

Frequently, we review an old estimate, using the schedule
to determine the project number and who prepared the
estimate, and then look in the Shared folder of that user
to retrieve the estimate. In other words, a lot of
productivity-killing navigation. We experimented with
hyperlinks, but since they're linked to a specific file,
they're quickly out-of-date.

So, we'd like to develop a "smart" hyperlink that takes us
to the last revision of the last estimate from the drive
of the estimator, the initials of which is maintained in a
column of the schedule. Our file naming convention is:

<projectnumber>E<estimatenumber>[R<revisionnumber>], e.g.,

04003E1
04003E2R1
04003E2R2

Can anyone assist us?

 

[quartz]

One way you could possibly approach this (though not in hyperlinks) is to have a program file that contains a list consisting all of your core directory names. A VBA program could then be used to retrieve a list of the file names in your various mapped folders into an array. You can do wild card searching to obtain a match based on project number, etc. so that only those files associated with the project are retrieved.

You already have a standard file naming convention which is good. It also looks like your naming convention will sort from newest file to oldest file at the operating system level. This is also good.

Once the program copies your file list into an array, sort the array if necessary, then structure your code to always open the last file in the array. This would always be the most recent file in that subdirectory.

HTH

 

[quartz]

Here is a function that might give you the idea, copy into a module, fix any line wrapping, change the path to your path and run the sub

Option Private Modul
Option Explici
Option Base

Private Sub Return_File_List(

Dim arrFileList() As Varian
Dim strPath As Strin
Dim strType As Strin
Dim varItem As Varian
Dim strMsg As Strin

strPath = "C:\Test\" '<Modify this pat
strType = "*.xls" '<Modify the file typ

arrFileList = FileList(strPath, strType, False

For Each varItem In arrFileLis
strMsg = strMsg & varItem & vbC
Nex
MsgBox strMs

End Su

Function FileList(argPath As String, argType As String, argSearchSubFolders As Boolean

Dim ofsSearch As FileSearc
Dim ofsFound As FoundFile
Dim lngX As Lon
Dim intSlash As Intege
Dim strFileName As Strin
Dim arrFileList() As Varian

Set ofsSearch = Application.FileSearc

With ofsSearc
.NewSearc
.SearchSubFolders = argSearchSubFolder
.Filename = argTyp
.LookIn = argPat
.Execute msoSortByFileNam
End Wit
Set ofsFound = ofsSearch.FoundFile

For lngX = 1 To ofsFound.Coun
intSlash = InStrRev(ofsFound(lngX), "\", -1
strFileName = Right(ofsFound(lngX), Len(ofsFound(lngX)) - intSlash
ReDim Preserve arrFileList(lngX
arrFileList(lngX) = strFileNam
Next lng
FileList = arrFileLis

End Function