mbrwny20
12-29-2005, 11:55 AM
Hello, I have a closed workbook that I am treating as a database. I run sql queries for searches and return results to my searching workbook. I used the code found here: http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado
It works great! and is very fast. However the next step is to look at the results, then based on criteria, look in a folder to see if a file exists. If it does, then create a hyperlink to that file. This part works as well, but is quite slow. Is there a way for me to speed this part up. Any help would be greatly appreciated. My hyperlink code is below.
P.S. I search for files with any extension ".*", then I pull the extension in my function (MyExt),to place in the hyperlink. This allows me to link to .doc,.xls,.pdf etc. Being new at VBA in general, I thought that was a pretty good solution. But if it is slowing me down I'll change it.
Public MyExt As String
Sub Links()
Dim i As Integer
Dim MyFile As String
Dim MyFolder As String
Dim MyLink As String
Dim MyDir As String
MyDir = ("\\server\shr\ (file:///servershr)")
For i = 8 To 10000
If Cells(i, 1) <> "" Then
MyFile = Cells(i, 1).Value
MyFolder = "procedures\"
Else: Exit Sub
End If
If FileThere(MyDir & MyFolder & MyFile & ".*") Then
MyLink = MyDir & MyFolder & MyFile & MyExt
Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), _
Address:=MyLink, TextToDisplay:=MyFile
Application.EnableEvents = True
End If
If Cells(i, 5) <> "" Then
MyFile = Cells(i, 5).Value
MyFolder = "worksheets\"
If FileThere(MyDir & MyFolder & "\" & MyFile & ".*") Then
MyLink = MyDir & MyFolder & MyFile & MyExt
Cells(i, 5).Hyperlinks.Add Anchor:=Cells(i, 5), _
Address:=MyLink, TextToDisplay:=MyFile
Application.EnableEvents = True
End If
End If
Next i
End Sub
Public Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
MyExt = Right((Dir(FileName)), 4)
End Function
It works great! and is very fast. However the next step is to look at the results, then based on criteria, look in a folder to see if a file exists. If it does, then create a hyperlink to that file. This part works as well, but is quite slow. Is there a way for me to speed this part up. Any help would be greatly appreciated. My hyperlink code is below.
P.S. I search for files with any extension ".*", then I pull the extension in my function (MyExt),to place in the hyperlink. This allows me to link to .doc,.xls,.pdf etc. Being new at VBA in general, I thought that was a pretty good solution. But if it is slowing me down I'll change it.
Public MyExt As String
Sub Links()
Dim i As Integer
Dim MyFile As String
Dim MyFolder As String
Dim MyLink As String
Dim MyDir As String
MyDir = ("\\server\shr\ (file:///servershr)")
For i = 8 To 10000
If Cells(i, 1) <> "" Then
MyFile = Cells(i, 1).Value
MyFolder = "procedures\"
Else: Exit Sub
End If
If FileThere(MyDir & MyFolder & MyFile & ".*") Then
MyLink = MyDir & MyFolder & MyFile & MyExt
Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), _
Address:=MyLink, TextToDisplay:=MyFile
Application.EnableEvents = True
End If
If Cells(i, 5) <> "" Then
MyFile = Cells(i, 5).Value
MyFolder = "worksheets\"
If FileThere(MyDir & MyFolder & "\" & MyFile & ".*") Then
MyLink = MyDir & MyFolder & MyFile & MyExt
Cells(i, 5).Hyperlinks.Add Anchor:=Cells(i, 5), _
Address:=MyLink, TextToDisplay:=MyFile
Application.EnableEvents = True
End If
End If
Next i
End Sub
Public Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
MyExt = Right((Dir(FileName)), 4)
End Function