Hyperlink help needed

[jreinhart]

I am having trouble creating multiple hyperlinks to their associated autocade files on my computer.
I can easily create a hyperlink to the files by entering a hyperlink for each individually but I need want my entered data ie a1 or b4 to be directly converted so it will access c:\a1.dwg or c:\b4.dwg.

I have tried to change the data type to hyperlink and it sort of does what i want but they link directly to

http://a1.dwg

or

http://b4.dwg.

Does anyone know how to change the http:// to c:\?

Thanks

 

[Zathras]

Not sure I understand you completely, but this may be what you are after:
[blue]

Sub CreateFileLinks()

[green]

' Select a range of cells.
' Each selected cell will be converted into
'    a file link with the cell address as
'    the file name.

[/color]

Const LINK_PATH = "c:\"
Const FILE_EXTENSION = ".dwg"
Dim c As Range
 For Each c In Selection
  ActiveSheet.Hyperlinks.Add Anchor:=c, _
  Address:=LINK_PATH & c.Address(0, 0) & FILE_EXTENSION
 Next c
End Sub

[/color]

 

[jreinhart]

Thanks for your help.
Although it is not what I needed it really helping me understand code (its been a while...) What i need is a lot simpler and here is some code that I have written that will hopefully help clarify what I am trying to do. I am getting an error at "run filedirectory".

Private Sub DwgNo_Click()
Dim FileName
Dim FilePath
Dim Fileext
Dim Filedirectory
FileName = DwgNo
Fileext = ".txt"
FilePath = "C:\"
Filedirectory = FilePath & FileName & Fileext
If Filedirectory = "" Then MsgBox "File Not Found"
If Filedirectory <> &quot;&quot; Then
Run Filedirectory

End If

End Sub

 

[Zathras]

Still not clear on what you are doing. Are you using a form? In any event, &quot;Run&quot; does not run an external program (check the help file)

Perhaps this is closer to what you need:
[blue]

Option Explicit

Private Sub DwgNo_Click()
  OpenDwgFile "1145"
End Sub

Sub OpenDwgFile(DwgNo As String)
Const FILE_PATH = "C:\"
Const FILE_EXT = ".txt"
Const APP_NAME = "Notepad.exe"
Dim sPathAndFile As String
  sPathAndFile = FILE_PATH & DwgNo & FILE_EXT
  With Application.FileSearch
    .NewSearch
    .LookIn = FILE_PATH
    .SearchSubFolders = False
    .FileName = sPathAndFile
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
    If .Execute() = 1 Then
      Shell APP_NAME & " " & sPathAndFile, vbNormalFocus
    Else
      MsgBox "File " & sPathAndFile & " Not Found"
    End If
  End With
End Sub

[/color]