Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA Code to Excel Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA Code to Excel Code
Lee,
Recorder a macro whilst inserting your hyperlink in Excel. That code plus the openfile should get you sorted. NickHK "Lee" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change text to bar code in word or excel? | Excel Discussion (Misc queries) | |||
Word equivalent of Excel code | Excel Programming | |||
How to open a Word or HTML document using VBA code in Excel | Excel Programming | |||
Excel code to Wait until Word finishes printing before closing | Excel Programming |