link list

[miha]

Hi,

I have searched for macro to link list but I did not found it. There
are some to list files but they do not suit me.

I have list of names in column A and I would like to check if file
with that name exist in folder and if yes than I would like to add
hyperlink in column B to that file.

Any help appreciated.

Miha

 

[Guest]

Look at the Dir command to see if a file exists.

YOu can see how to add a hyperlink by turning on the macro recorder, then
adding one manually. Turn off the macro recorder and look at the recorded
code. Then work that into your macro.

 

[miha]

I have following code. ELSE in IF does not work correct because I get
listed all files not just missing on the list.

Sub LinkList()
Dim ListName As String ' For List Name
Dim FileName As String ' For File Name
Dim MyFileLocation As String
MyFileLocation = "c:\Documents and Settings\msantovec\My Documents
\_work\ExcelLinkFiles\*.*" 'change to your needs
FileName = Dir(MyFileLocation)
Do Until FileName = ""
For i = 1 To [B65536].End(xlUp).Row
ListName = Cells(i, 2).Value
If FileName = ListName Then
Cells(i, 1).Value = FileName
' ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i),
Address:=FileName, TextToDisplay:=FileName
Exit For
Else
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Value =
FileName
' ActiveSheet.Hyperlinks.Add Anchor:=Range("A" &
[A65536].End(xlUp).Row + 1), Address:=FileName,
TextToDisplay:=FileName
Exit For
End If
Next i
FileName = Dir
Loop
End Sub


Tom Ogilvy je napisal: