hyperlink solution requested
 

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...
ToolsMacroMacrosRun
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.

--
regards

hyperlink solution requested
 

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
& """)"


"driller" wrote:

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...
ToolsMacroMacrosRun
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.

--
regards