Display the size of the active file 2

[vakubia0]

Hi,

New to all this forum stuff and VBA.

I have a file - 1 mb in size, I run a macro to paste values all the cells to get rid of formulas and then save it. I need to then display the new file size.

Any ideas?

 

[fumei]

Unfortunately Excel does not support the property

BuiltInDocumentProperties("Number of bytes").Value

In Word, this returns the file size in bytes. In Excel this returns an error, as while it has the property of Number of bytes, Excel does not bother to set a value.

I am sure the Excel folks here can tell you a better way, the only thing I can think of is to make a FileSystemObject of the file, and get the file size that way.

Gerry

 

[palusa]

Hi, try this.

Under Excel sheet open the VisualBasic Editor (Alt F11).
In the General Declaration panel paste this code:

Public Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _ 
(ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Public Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * 260
    cAlternate As String * 14
End Type


Private Sub ShowFileInfo()

    Dim hFile As Long
    Dim WFD As WIN32_FIND_DATA
    Dim FullName As String

'   FullName is the path and filename
'   Substitute any valid file and path

    FullName = "c:\autoexec.bat"

    hFile = FindFirstFile(FullName, WFD)
    
    If hFile > 0 Then
        MsgBox "File size: " & WFD.nFileSizeLow
    Else
        MsgBox "File not found.", vbCritical, FullName
    End If
End Sub

Replace in Sub ShowFileInfo FullName with the name of your file.
From the Excet sheet: run macro (Alt F8) select ShowFileInfo.

You should get the new filesize.

Hope this helps.

Luca

PS: rearranged from

http://j-walk.com/ss/excel/tips/tip57.htm

 

[xlbo]

You'll like this....

1: follow Insert>Name>Define
2: Enter myFileName under "Names In Workbook"
3: Copy and paste
=SUBSTITUTE(SUBSTITUTE(MID(CELL("FILENAME",Sheet1!G10), 1,FIND("]",CELL("FILENAME",Sheet1!G10))),"[",""),"]","")
into the "Refers To" box
4: Press Add

You now have a "Name" that references the filename of the workbook

Copy and paste the following into a standard module

Function FileSize()
    Dim fs, f
    Application.Volatile [COLOR=green]' get rid of this if calling from a sub[/color]
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.Getfile([[b]myFileName[/b]])
    FileSize = UCase(f.Name) & " uses " & f.Size & " bytes."
End Function

As you can see, the function uses the "Name" created earlier to reference the name and path of the file.

You can call this from a sub or enter it in a cell using
=Filesize()

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[vakubia0]

Thank you all for your reply. Geoff's function worked like a charm.

 

[IvanMoala]

Or just use native Excel commands

Sub FileSize()
Dim strFileSize As String
On Error GoTo NoGo
strFileSize = FileLen(ActiveWorkbook.FullName) / 1024
MsgBox ActiveWorkbook.Name & " is " & Format(strFileSize, "#,#00") & " KBytes" & vbLf & _
          "It has been saved as " & ActiveWorkbook.FullName & " "
Exit Sub
NoGo:
MsgBox "File not saved"
End Sub

Ivan F Moala

http://www.xcelfiles.com

 

[xlbo]

Ivan - sweet. never knew that FileLen function existed
Have a star on me !

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Seems strange that if Excel has a native function of FileLen, that it does not set the value in a BuiltinProperty that it DOES hold. "Number of bytes" is a named builtinproperty, but Excel, unlike Word, does not automatically set the value. In fact, it never sets the value.

Maybe because Excel files tend to change more? Odd that.

Gerry