hyperlink solution requested

[driller]

Dear All...

Just a few ago. I have 2 nice codes that works visually very well.

The good thing is, these Codes do not require me to go thru the
menuselection process likehere below...
Tools>Macro>Macros>Run
It runs on by itself when i click F2 on A1. Feels professionally very
relaxing & convenient...

But both code can't seem to locate one missing slash "\".
for a generic hyperlink address.
i.e
right click mouse > edit hyperlink > address >......"\"books.xls

Scenario is...
I have one workbook
Workbook has 30 sheets.
In cell A1 of each sheet contains a folder address (e.g. "d:\temp1";
"d:\temp2\tp1" ; "c:\jk1\fx1" ; etc)

For 30 sheets, in cells A2:C##, the codes were prepared and generated the
same visual results.

e.g. in Sheet1 : A2 ~ C2 : the result will look like below.
Booked1.xls 125065 9/17/2009 15:25

When I click now the Booked1.xls, the hyperlink prepared along Col A seems
missing something. for 30 sheets...

below here is Code A
---
Sub getdates()

Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(folder)

RowNumber = 1
'folder size in bytes
On Error GoTo 200
For Each fl In folder.Files
Sheets(1).Cells(RowNumber, "C") = fl.DateLastModified
Sheets(1).Cells(RowNumber, "B") = fl.Size
Sheets(1).Cells(RowNumber, "A") = folder.Name & "\" & fl.Name

RowNumber = RowNumber + 1
Next fl

200 On Error GoTo 0

End Sub
----
below here is Code B
----

Sub GetFileDetails()
Dim fso As Object, folder As Object
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Range("A2:C2").Resize(ws.Cells(Rows.Count, _
"A").End(xlUp).Row).ClearContents

If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = 2

For Each fl In folder.Files
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"
ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
lngRow = lngRow + 1
Next

End If
Next
End Sub
-----

I hope that either of the above Codes can be modified to work, visually and
desirably functional for my 1 workbook of 30 sheets.

Have a nice day.

 

[Joel]

code A doesn't have a folder definined

Set folder = _
fso.GetFolder(folder)


You need something like this

Myfolder = "c:\temp"
Set folder = _
fso.GetFolder(MyFolder)


The 2nd case you probably want the parent name included in the displayed link

ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Name & """)"

try this change

ws.Range("A" & lngRow).Formula = "=hyperlink(""" & _
folder.Path & "\" & fl.Name & """,""" & fl.Parentfolder.name & "\" & fl.Name
& """)"