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