Hi,
perhaps any of you experts can help me with this VBA-problem.
In an Excel-Sheet the following code reads a specific directory with all its files and creates corresponding Hyperlinks in a column.
The only problem is, I have to set all Hyperlinks to "Relative" manually, which can become a nuisance if you have more than 1000 Hyperlinks in one sheet. I would like it to be done automatically.
Please, take a look at the code:
Private Sub CommandButton1_Click()
Dim arrFiles As Variant
Dim intRow As Integer
Dim strPath As String
strPath = "d:\0002bas1.sd\reports"
arrFiles = FileArray(strPath, "*.*")
strPath = WorksheetFunction.Substitute(strPath, "\", "/")
For intRow = 1 To UBound(arrFiles)
With Worksheets(1)
.Cells(intRow, 1).Value = arrFiles(intRow)
.Hyperlinks.Add anchor:=.Cells(intRow, 1), Address:=strPath & .Cells(intRow, 1).Value
End With
Next intRow
End Sub
Private Function FileArray(strPath As String, strPattern As String)
Dim arrDateien()
Dim intCounter As Integer
Dim strDatei As String
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
strDatei = Dir(strPath & strPattern)
Do While strDatei <> ""
intCounter = intCounter + 1
ReDim Preserve arrDateien(1 To intCounter)
arrDateien(intCounter) = strDatei
strDatei = Dir()
Loop
FileArray = arrDateien
End Function
The code was provided by Hans W. Herber.
Any help will be greatly appreciated.
Thanks from Germany,
Catarina
