Word VBA Code to Excel Code


L

Lee

I have some code that opens a FileDialog box and the user
can click on a document which will in turn, insert a
hyperlink into a document. This code runs in Word and I
have been asked to change this so it runs in Excel as
well.. Problem is, I've never written for Excel before and
I'm only a learner of VBA in Word. (We are using 2002).

The code is used to insert a hyperlink from a certain
folder. The Word VBA I have is:

Sub Hyperlink()

Dim fd As FileDialog, displaytext As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Use a With...End With block to reference the FileDialog
object.
With fd
'Set the initial path to the Agenda Attachments folder.
.InitialFileName = "file://
\\alchemy\data\processes\documents\agenda attachments\"
.Title = "Select the File to which you want to create
the link"
'Use the Show method to display the File Picker dialog box
and return the user's action.
'If the user presses the action button...
If .Show = -1 Then
displaytext = .SelectedItems(1)
While InStr(displaytext, "\") > 0
displaytext = Mid(displaytext, InStr
displaytext, "\") + 1)
Wend
displaytext = Left(displaytext, Len(displaytext) -
4)
ActiveDocument.Hyperlinks.Add
Anchor:=Selection.Range, Address:=.SelectedItems(1),
TextToDisplay:=displaytext
'If the user presses Cancel...
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing
End Sub

I tried to change the line "ActiveDocument.Hyperlinks.Add
Anchor:=Selection.Range, Address:=.SelectedItems(1),
TextToDisplay:=displaytext" for Excel but don't know how
or what I need to do. The rest of the code runs, e.g. the
FileDialog box opens in the right folder etc...

Help please !! Thanks
 
Ad

Advertisements

N

NickHK

Lee,
Recorder a macro whilst inserting your hyperlink in Excel. That code plus
the openfile should get you sorted.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top