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

On Apr 14, 12:18*pm, robs3131
wrote:
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

Robert,

See this post, which uses the filelen function:

http://en.allexperts.com/q/Excel-105...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

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

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

Set fil = fso.GetFolder(ThisWorkbook.Path).Files(ThisWorkboo k.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

"robs3131" wrote:

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

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" wrote:

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

Thanks Alex! Works perfect.

--
Robert


"Alex Simmons" wrote:

On Apr 14, 12:18 pm, robs3131
wrote:
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

Robert,

See this post, which uses the filelen function:

http://en.allexperts.com/q/Excel-105...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

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


"Mike H" wrote:

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" wrote:

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