Anthony has attended:
Excel VBA Intermediate course
How to use VBA to check how many excel workbooks are currently o
I want to find out what VBA codes to program in order to avoid an excel application from crashing due to many excel workbooks being opened by the user.
RE: How to use VBA to check how many excel workbooks are current
Hi Anthony,
Thank you for the forum question and the nice feedback.
I was wrong it is a very complicated task. I remembered it wrong. The FileSystemObject can only return disk space and not memory space.
I found this function which return how much memory Excel use.
Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Function GetMemUsage()
' Returns the current Excel.Application
' memory usage in MB
Set objSWbemServices = GetObject("winmgmts:")
GetMemUsage = objSWbemServices.Get( _
"Win32_Process.Handle='" & _
GetCurrentProcessId & "'").WorkingSetSize / 1024
Set objSWbemServices = Nothing
End Function
The next task is to get Excel to check it every time a new workbook get opened. Unfortunately a Open Workbook event needs to be added to all workbooks to return the space in memory.
I am sorry but this is probably not a good solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
