code to check file size everytime an Excel file is opened

[Kaiser]

Hello,

Every time any Excel file is opened, I'd like Excel to automatically
check the size of the file being opened and if it is greater than say,
5mb, I'd like Manual calculate to be turned on. Is there a way to do
this?

The workbook_open event etc. wouldn't work since then I would have to
include this macro in every single Excel workbook I ever create or that
already exists. Having the macro in the xlstart folder etc. doesn't
work either since the macro there doesn't seem to be run before the
file is opened.

Is there a way to do this using VBA? or VB6? or at the command line?

Thanks,

[raypayette]

Sub FileSize()
Dim oFSO
Dim oFile
Dim oSourceFile
oSourceFile = "D:/Excel/test.xls"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.GetFile(oSourceFile)
If oFile.Size > 5000000 Then
Application.Calculation = xlManual
End If
Set oFile = Nothing
Set oFSO = Nothing
End Sub

[Tom Ogilvy]

It might be easier to set calculation to manual, then if the file is small
enough, turn it back on after the file is opened.

you would need to use application level events to turn it on.

http://www.cpearson.com/excel/appevent.htm

Do that in the personal.xls or an addin.

--
Regards,
Tom Ogilvy

"Kaiser" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Every time any Excel file is opened, I'd like Excel to automatically
> check the size of the file being opened and if it is greater than say,
> 5mb, I'd like Manual calculate to be turned on. Is there a way to do
> this?
>
> The workbook_open event etc. wouldn't work since then I would have to
> include this macro in every single Excel workbook I ever create or that
> already exists. Having the macro in the xlstart folder etc. doesn't
> work either since the macro there doesn't seem to be run before the
> file is opened.
>
> Is there a way to do this using VBA? or VB6? or at the command line?
>
> Thanks,
>