Programmatically creating Hyperlinks to File Names/locations from VBA & Excel

[deyken]

Hi All,

Thank you very much to p45scal for assisting me with a silly error earlier today! Now I can move on with my project, which now produces a new challenge:

I have populated 2 list boxes from 2 recordsets retrieved from a SQL Server Database and successfully displayed the selected field(s)' data in each respective list box. Listbox1 lists "Project Codes" and Listbox2 lists each Project's individual "Shipments". Clicking on Listbox1 repopulates Listbox2 with its detail data. Clicking on Listbox2 retrieves (from different DB Tables & Views) sub-detail data for each Shipment record and displays this data in Worksheet Cells (L17 through L24 as the case may be).

Now that I can diplay my Shipment record's Packing List reference number, would it be possible for me to display this result as a (pre-coded) hyperlink, which points to the file name as it sits on either the local hard drive or company intranet shared drive? If so, what would the syntax be? [PS: The file path for this exercise is assumed to simply be 'C:\My Documents\<PackingListNo>.xls']. For the actual Sub, it will be a string value also collected from a field where the user would have saved the full file path previously.

I pasted my entire Sub that displays the above data statically below for ease of reference:

[vba]Private Sub ListBox2_Click()
' Find selected Shipment's Document numbers and list them in range L17 through L24
Dim ShipRef As String
Dim ShipNo As Integer
Dim DB As New ADODB.Connection
Dim PL As New Recordset
Dim Ships As New ADODB.Recordset
Dim PLNo As String
Dim F178 As New ADODB.Recordset


ShipRef = ListBox2.Text
DB.CursorLocation = adUseClient
DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING004\SQLEXPRESS;Database=BrolazShipping"
Ships.Open "Select * from tblSHIPMENTS WHERE SHIPMENT_REF = '" & ShipRef & "' ORDER BY SHIPMENT_REF", DB, adOpenDynamic, adLockOptimistic
If Ships.RecordCount <> 0 Then
ShipNo = Ships.Fields("SHIPMENT_NO").Value
End If

PL.Open "Select PACKINGLIST_NO from tblPACKING_LISTS_m WHERE SHIPMENT_NO = '" & ShipNo & "'", DB, adOpenStatic, adLockReadOnly
PLNo = PL.Fields(0).Value
Sheet1.Cells(17, "L") = "Packing List Number: " & PLNo

F178.Open "Select F178_NO from tblF178 WHERE SHIPMENT_NO = '" & ShipNo & "'", DB, adOpenStatic, adLockReadOnly
If F178.RecordCount <> 0 Then
Sheet1.Cells(18, "L") = "Bank Form Reference: " & F178.Fields(0).Value
Else
Sheet1.Cells(18, "L") = "No Bank Form for this Shipment"
End If
End Sub[/vba]

I look forward to all assistance & thank you very much in advance!

[p45cal]

instead of:
[vba]Sheet1.Cells(17, "L") = "Packing List Number: " & PLNo [/vba] try:
[vba] Sheet1.Hyperlinks.Add Anchor:=Sheet1.Cells(17, "L"), Address:="C:\My Documents\" & PLNo & ".xls", TextToDisplay:="Packing List Number: " & PLNo
[/vba]