Claus Busch wrote:

Hi Terry,

Am Thu, 14 Dec 2017 14:02:42 +0100 schrieb Claus Busch:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

sorry, the folder and subfolder names are missing into the code.
Try:

Sub Test()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
On Error Resume Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objSubFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
Next
End Sub


Regards
Claus B.

Hi Claus,

Thanks for the code which I am trying to implement, even though I do now
have Garry's function fully working.

Don't forget I'm still in 'copy/paste and follow instructions mode'! So
I'm probably not applying your code properly.

By "try the following code in a new workbook" do you mean a copy? Or an
empty book? Or perhaps a new worksheet of my existing workbook?

I placed it in a fresh workbook containing just an extract of col F, as
shown he
https://www.dropbox.com/s/6ptgw0qjeq...cro-1.jpg?dl=0

When I ran your macro it gave the error

Run-time error '5':
Invalid procedure call or argument

The line highlighted by Debug was this:

Set objFolder = FSO.getfolder(MyPath)

I also tried it in a completely empty workbook, although I couldn't
imagine how that coud work with nothing to operate on?

Or should I have edited your code in some way before I used it?

Here's the code I pasted (renaming it was my only change).

Sub ClausGetFileSize()
Dim FSO As Object
Dim objFolder As Object, objSubFolder As Object, objFile As Object
Dim MyPath As String
Dim i As Long

Set FSO = CreateObject("scripting.fileSystemobject")
MyPath = Range("A1").Value
Set objFolder = FSO.getfolder(MyPath)

i = 2
For Each objFile In objFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
On Error Resume Next
For Each objSubFolder In objFolder.subfolders
For Each objFile In objSubFolder.Files
ActiveSheet.Hyperlinks.Add _
anchor:=Cells(i, "F"), _
Address:="file:///" & MyPath & objSubFolder.Name & _
"\" & objFile.Name, _
TextToDisplay:=objFile.Name
Cells(i, "G") = Format(objFile.Size / 1024 ^ 3, "0.000") & " GB"
i = i + 1
Next
Next
End Sub

Terry, East Grinstead, UK