Workbook size?

C

Charlotte E.

When you right-click a workbook in windows, and select 'Properties' you can
see the size of the workbook in bytes.

Is it possible to get a workbooks size from within the workbook by VBA code?


TIA,
 
D

Dave Peterson

Look for FileLen in VBA's help to get the size of the file already saved to
disk.

MsgBox FileLen(ActiveWorkbook.FullName)
 
M

Mike H

Charlotte,

Try

MyFileSize = FileLen(ThisWorkbook.FullName)

which returns the size in bytes

Mike
 
C

Charlotte E.

Thanks guys :)

Problem with the method is that it requires the wookbook to be saved!

What I was looking for was a way to determine the size of the workbook AFTER
changes has been made to it, but BEFORE saving it???

Anyway to do that?
 
C

Charlotte E.

Not that I know--but maybe someone else has an idea.


Got an idea of my own...

Make the VBA code do this:

1) Read the Windows envioment variabel for the Windows Temp-directory
(Normal: "C:\woindows\Temp\")

2) Save a temporary copy of the Workbook in that directory

3) Read the size of the file with the FilLen() as suggested

4) Return the Workbook to its previous location and name.

5) Delete the temporary copy.


Now question is: How are that code gonna be???

Especially number 1 and 4 is giving me a hard time!

Can anybody help???


TIA,
 
P

Peter T

Maybe get the size of a saved copy of your file, your original unsaved wb
remains unchanged (will exclude any vba code modules except sheet modules).

Sub Test()
Dim sFile As String, nSize as long
sFile = Application.DefaultFilePath & "\TmpExcelFile.xls"
On Error Resume Next
' just in case
Kill Application.DefaultFilePath & "\TmpExcelFile.xls"
On Error GoTo 0

ActiveWorkbook.Sheets.Copy

' the new copied wb is now active
ActiveWorkbook.SaveAs sFile
ActiveWorkbook.Close

nSize = FileLen(sFile)
Kill sFile

MsgBox "indicative filesize " & Format(nSize / 1024, "0,000 Kb")

End Sub

Regards,
Peter T

Charlotte E. said:
Not that I know--but maybe someone else has an idea.
Click to expand...


Got an idea of my own...

Make the VBA code do this:

1) Read the Windows envioment variabel for the Windows Temp-directory
(Normal: "C:\woindows\Temp\")

2) Save a temporary copy of the Workbook in that directory

3) Read the size of the file with the FilLen() as suggested

4) Return the Workbook to its previous location and name.

5) Delete the temporary copy.


Now question is: How are that code gonna be???

Especially number 1 and 4 is giving me a hard time!

Can anybody help???


TIA,
Click to expand...
 
D

Dave Peterson

Excel's VBA has a workbook.savecopyas command that won't affect the current
workbook.

Option Explicit
Sub testme()
Dim myStr As String
myStr = Environ("Temp") & "\" & Format(Now, "yyyymmdd-hhmmss") & ".xls"

ActiveWorkbook.SaveCopyAs Filename:=myStr
MsgBox FileLen(myStr)
Kill myStr
End Sub

=======
But this is cheating <vbg>. You're still doing a save!
 
C

Charlotte E.

Thanks, guys - got it to work just as I wanted - yes, it is cheating, but
sometimes that's just what is needed ;-)
 
Top