Is it possible to get the properties of a folder/drive from within an Excel
macro and then populate seperate cells with the folder/drive size, number of
files & number of folders?

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size, number
of
files & number of folders?

Thank you, that appears to be the instruction I need but can anybody tell me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size, number
of
files & number of folders?

Here is what I could do.
As above mentioned, folder size is inaccurate.

HTH
--
AP

"Damon" a écrit dans le message de news:
...
Thank you, that appears to be the instruction I need but can anybody tell
me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de
news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size,
number
of
files & number of folders?

I forgot the attachment: http://cjoint.com/?fspSDS4wrK

--
AP

"Damon" a écrit dans le message de news:
...
Thank you, that appears to be the instruction I need but can anybody tell
me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de
news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size,
number
of
files & number of folders?

Hello Damon
Here is a try (please amend accordingly)
Cordially
Pascal

Sub Test()
GetInfo "C:\My documents\Excel\samples", 0
End Sub
Function GetInfo(Foldername$, Indx As Long)
Dim RootFolder As Object
Dim SubFldr As Object
Dim NewFldr As String
Dim fso As Object
Dim i As Long
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set RootFolder = fso.GetFolder(Foldername)
If Err < 0 Then
MsgBox "Folder " & Foldername & " was not found", vbInformation, "Not found"
Exit Function: End If
With ActiveWorkbook.Worksheets(1)
i = Indx
..Cells(i + 1, 1).Value = "Folder Name"
..Cells(i + 1, 2).Value = Foldername
..Cells(i + 2, 1).Value = "No of subFolders"
..Cells(i + 2, 2).Value = RootFolder.SubFolders.Count
..Cells(i + 3, 1).Value = "Folder size"
..Cells(i + 3, 2).Value = Format((RootFolder.Size) / 1024, "#,##0")
..Cells(i + 4, 1).Value = "No of Files"
..Cells(i + 4, 2).Value = RootFolder.Files.Count
End With
'if sub folders found then call recursively
If RootFolder.SubFolders.Count 0 Then
For Each SubFldr In RootFolder.SubFolders
i = i + 6
NewFldr = SubFldr.Path
GetInfo NewFldr, i
Next SubFldr
End If
End Function


"Damon" a écrit dans le message de news:
...
Thank you, that appears to be the instruction I need but can anybody tell
me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de
news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size,
number
of
files & number of folders?

Thank you.

I shall have a play when I get home from work and see if I can get it to do
what I need.

"Ardus Petus" wrote:

I forgot the attachment: http://cjoint.com/?fspSDS4wrK

--
AP

"Damon" a écrit dans le message de news:
...
Thank you, that appears to be the instruction I need but can anybody tell
me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de
news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size,
number
of
files & number of folders?

Thank you both for you help, both examples give me the results I require and
help me to understand programming excel/macros better.

"Damon" wrote:

Thank you, that appears to be the instruction I need but can anybody tell me
how I would use it get the properties for a folder and then copy the
infomation I need (Folder size, Number of files & Number of folders) into
seperate cells within excel?

Thank you in advance

"Ardus Petus" wrote:

Yes: use FileSystemObject
I'm not sure about folder size.

HTH
--
AP

"Damon" a écrit dans le message de news:
...
Is it possible to get the properties of a folder/drive from within an
Excel
macro and then populate seperate cells with the folder/drive size, number
of
files & number of folders?