Need to generate a msgbox telling how large the file is

[robs3131]

Hi all,

I would like to like have a msgbox come up during Workbook_Open only when
the size of the file being opened exceeds a certain size (ie - 30 MB). I'm
wondering what the best way is to this - I'm at a loss on how to even begin
the coding to look for the file size.

Thanks for your help!

 

[Alex Simmons]

Hi all,

I would like to like have a msgbox come up during Workbook_Open only when
the size of the file being opened exceeds a certain size (ie - 30 MB).  I'm
wondering what the best way is to this - I'm at a loss on how to even begin
the coding to look for the file size.

Thanks for your help!

Robert,

See this post, which uses the filelen function:

http://en.allexperts.com/q/Excel-1059/Using-VBA-Excel-file.htm

So, providing the document has been saved (which it has by the sounds
of your post), then you could use something like this:

Private Sub Workbook_Open()
If FileLen(ThisWorkbook.FullName) >= 31457280 Then
MsgBox ("Your prompt here")
End If
End Sub

31457280 is 30Mb in Bytes; and you can then use MsgBox to display
whatever message you want.

Regards,

Alex

 

[DomThePom]

Cresate a reference (tools references) to the Micrsoft scripting runtime
library
and then place the following in the workbook open event procedure:

Dim fso As New FileSystemObject
Dim fil As File
Dim dblSize As Double

Set fil = fso.GetFolder(ThisWorkbook.Path).Files(ThisWorkbook.Name)
dblSize = fil.Size / 1000000
If dblSize > 30 Then
MsgBox "This file is " & fil.Size / 1000000 & "MB"
End If
Set fso = Nothing
Set fil = Nothing

 

[Mike H]

Rob,

Not tested because I didn't have a workbook large enough but this should do
it.

Private Sub Workbook_Open()
Path = ThisWorkbook.FullName
If FileLen(Path) / 1024 / 1024 > 30 Then
MsgBox FileLen(Path) / 1024 / 1024 & " Megabytes"
End If
End Sub

Mike

 

[robs3131]

Thanks Alex! Works perfect.

--
Robert

Robert,

See this post, which uses the filelen function:

http://en.allexperts.com/q/Excel-1059/Using-VBA-Excel-file.htm

So, providing the document has been saved (which it has by the sounds
of your post), then you could use something like this:

Private Sub Workbook_Open()
If FileLen(ThisWorkbook.FullName) >= 31457280 Then
MsgBox ("Your prompt here")
End If
End Sub

31457280 is 30Mb in Bytes; and you can then use MsgBox to display
whatever message you want.

Regards,

Alex

 

[robs3131]

This worked great! Thanks Mike! And thanks to all others that responded.