Determining File Size

Todd

unread,

Aug 31, 2002, 5:33:26 AM8/31/02

to

Can anyone tell me how to determine the file size for the
active workbook (last saved version)?

Bob Kilmer

unread,

Aug 31, 2002, 9:14:55 AM8/31/02

to

As a first-pass solution, I'd use FileSystemObject to read the drive.

"Todd" <jenki...@yahoo.com> wrote in message
news:ac1101c250d1$7543cdc0$a5e62ecf@tkmsftngxa07...

Dave Peterson

unread,

Aug 31, 2002, 9:27:29 AM8/31/02

to

Something like this:

MsgBox FileLen(Activeworkbook.FullName)

might do it for you.

Todd wrote:
>
> Can anyone tell me how to determine the file size for the
> active workbook (last saved version)?

--

Dave Peterson
ec3...@msn.com

Ivan F Moala

unread,

Aug 31, 2002, 11:21:06 AM8/31/02

to

"Todd" <jenki...@yahoo.com> wrote in message news:<ac1101c250d1$7543cdc0$a5e62ecf@tkmsftngxa07>...

> Can anyone tell me how to determine the file size for the
> active workbook (last saved version)?

Hi Todd
There are a number of ways to get this here is but one method

Function FileSzA(ByVal strFullFileName As String)
Dim handle As Integer

'// ensure that the file exists
If Len(Dir(FullFileName)) = 0 Then GoTo NoGo

'// open in binary mode
handle = FreeFile
Open FullFileName For Binary As #handle
'// read the string and close the file
FileSzA = LOF(handle)
Close #handle

Exit Function
NoGo:
Err.Raise 53
End Function

Sub TesterIII()
'// Just a few tests
MsgBox FileSzA("c:\autoexec.bat") \ 1024 & "Kb"
MsgBox FileSzA(ActiveWorkbook.FullName ) \ 1024 & "Kb"
End Sub

HTH

Ivan

Jim May

unread,

Aug 31, 2002, 2:58:47 PM8/31/02

to

Dave: Where does one put your answer; I'm not only Blind, but also stupid
and crazy.
Ignorant of such matters,
help!!

"Dave Peterson" <ec3...@msn.com> wrote in message
news:3D70C441...@msn.com...

Dave Peterson

unread,

Aug 31, 2002, 10:22:19 PM8/31/02

to

You posted in .programming, so you got a lot of programming solutions.

This would be one line in a macro that you could run by hitting
Tools|Macros|Macros... (after it was created).

To learn more about creating macros, you might want to visit David McRitchie's
site:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

There's lots of notes that to help get started (hence the name!).

It kind of sounds like you want to use put this kind of thing in the worksheet
(as a formula):

It this is true, then you might be able to use this User Defined Function (after
you learn about macros from David McRitchie's site!):

Option Explicit
Function FileSize(Optional FileName As Variant) As Variant

Application.Volatile

If IsMissing(FileName) Then
FileName = Application.Caller.Parent.Parent.FullName
End If

If Dir(FileName) = "" Then
FileSize = CVErr(xlErrRef)
Else
FileSize = FileLen(FileName)
End If

End Function

Then you can do things like:
=filesize("C:\autoexec.bat")
or even
=filesize()
(if the current workbook has been saved.)

--

Dave Peterson
ec3...@msn.com

Jim May

unread,

Sep 1, 2002, 5:29:09 PM9/1/02

to

Thanks very much; got it..
Jim May

"Dave Peterson" <ec3...@msn.com> wrote in message

news:3D7179DB...@msn.com...