Capturing File Attributes

[Scott]

Is it possible to capture file attributes with formlas?
Specifically, could I get file sizes of files in a
directory?

 

[Frank Kabel]

Hi
this would require VBA (e.g. using the FileSystemObject). Is VBA
feasible for you?

 

[Andy Wiggins]

This might help

''
***************************************************************************
'' Purpose : Size of a file in kilobytes
'' Written : (This version) 30/09/97 by Andy Wiggins - Byg Software Ltd
''
Function fFileSize(vtPath$, vtName$)
On Error GoTo sOops
Application.Volatile
If Right(vtPath, 1) <> "\" Then vtPath = vtPath & "\"
fFileSize = Int(FileLen(vtPath & vtName) / 1000)
Exit Function
sOops:
fFileSize = 0
End Function

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Scott]

This worked perfectly. Thanks.

Now I want to capture folder sizes. I thought I might be
able to see this formula and tweak it for folder sizes but
I'm afraid this is a little beyond me.

Our IT guy put a dll on our system that reports folder
sizes in Windows Explorer. I'm not sure how it is
referenced. Can you do that?

 

[Andy Wiggins]

Loop through all the files in a directory and use the function I posted to
capture the size of each.

To loop through a directory see VBA's Dir function. There are examples in
Excel's Help file on how to do this.

IT guys and DLLs - why? It's like having a nail to knock in wood and not
looking through your toolkit for a hammer. Reason = they don't know the
ahmmer's there, and even if they did they wouldn't recognise what it's for.
So what do they do? Give you a great big rock (for rock read DLL) and say
"use this". Yes, it might work, but ...

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Scott]

I appreciate your help.

I think I can figure out how to loop through the
directory, but, unless I get the size of each file in each
subdirectory, I won't get the size of the top level
directory. I am not necessarily interested in knowing the
size of all the files (though I want the size of files in
the top level directories so your formula and the process
for getting them is useful to me) - I will want the size
of the directory and some subdirectories. We have tons of
files that go deep into subdirectories. Any thoughts?

 

[hgrove]

Scott wrote...
...

. . . but, unless I get the size of each file in each subdirectory I
won't get the size of the top level directory. I am not necessarily
interested in knowing the size of all the files (though I want the
size of files in the top level directories . . . I will want the siz of
the directory and some subdirectories. We have tons of files
that go deep into subdirectories. . . .

...

How wxactly do you suppose Windows figures out the size of all file
stored in a directory or drive? That specific information *ISN'T
stored anywhere on the drive itself or in any directory. If you wan
the total disk space used by all files and subdirectories within
given directory, you have to sum up the disk storage used by *EACH
file. If you have lots of files in many branches of the subdirector
tree, it's just plain going to take a while to figure out the tota
storage used in each.

So why are you using Excel to do something like this? Metaphorically
you could use a golf cart to haul several tons of coal if you mad
enough back & forth trips, but most people would understand that thi
wouldn't be a good idea. Likewise, Excel *can* be used to perfor
system administration tasks, but it's ill-suited to those tasks