Inserting Hyperlink in Excel 97 Macro

[Chris Hankin]

Hi, I'm hoping please that someone could advise on how I can do the
following:

When a use clicks in Column I of my workbook named Queries, I want Excel to
automatically do the following:

This is what I now do manually:

Click on Insert
Click on Hyperlink
Click on Browse (the Browse button adjacent to the drop-down box named Link
To File or URL
Select the following drive and directory: P:\DNSDC\LSA\QUERIES\EMAILS SENT\
The tick box Use Relative Path For Hyperlink is checked

So basically when a user clicks in Column I, I want Excel to automatically
show the Insert Hyperlink Window
with the above-mention drive and directory pre-selected so that the user
only needs to select the .EML file and click on the OK button.

Any help would be greatly appreciated.

Kind regards,

Chris.

 

[Mike Fogleman]

This should get you real close to what you want. Put this code in the sheet
module of the sheet that you want the hyperlinks on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fileToOpen As String
Dim MyPath As String
Dim SaveDriveDir As String
If Not Application.Intersect(Range("I1:I65000"), Target) Is Nothing Then
SaveDriveDir = CurDir
MyPath = "P:\DNSDC\LSA\QUERIES\EMAILS SENT\"
ChDrive MyPath
ChDir MyPath

fileToOpen = Application _
.GetOpenFilename("Email Files (*.eml), *.eml")
If fileToOpen = "False" Then Exit Sub
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fileToOpen

ChDrive SaveDriveDir
ChDir SaveDriveDir
Else: Exit Sub
End If
End Sub

Mike F

 

[Chris Hankin]

Hello Mike,

Thankyou very much for all you excellent help - the macro worked very
well - except that it opens an Open Window instead of a Link to File
Window.

Is there any chance please that you might be able to modify you macro so
that the Link to File Window opens up?

Again thanks for your help - much appreciated.

Kind regards,

Chris.

Live Long and Prosper

 

[Mike]

The browser window is the same, only the Title at the top is different. Here
is the code to change the Title from "Open" to "Link to File".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fileToOpen As String
Dim MyPath As String
Dim SaveDriveDir As String
If Not Application.Intersect(Range("I1:I65000"), Target) Is Nothing
Then
SaveDriveDir = CurDir
MyPath = "P:\DNSDC\LSA\QUERIES\EMAILS SENT\"
ChDrive MyPath
ChDir MyPath

fileToOpen = Application _
.GetOpenFilename("Email Files (*.eml), *.eml", , "Link to File")
If fileToOpen = "False" Then Exit Sub
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fileToOpen

ChDrive SaveDriveDir
ChDir SaveDriveDir
Else: Exit Sub
End If
End Sub


Mike F

 

[Chris Hankin]

Thanks Mike - very much appreciated - cheers Chris.

Live Long and Prosper