The following function (that calls Marcus Mansfield's IPSubnetCalc) will output a network range when given a subnet:
Public Function IPRange(IPandMaskInput$)
' PROGRAM:
' IP Range Calculator by Tomas Pospisek that uses Marcus Mansfield's IPSubnetCalc
'
' INPUTS:
' Input Expected xx.xx.xx.xx/yy
' Where xx = decimal IPv4 Octet
' yy = BitMask Value ( 0 - 32 ex.31 )
'
' OUTPUT:
' IP Range in the form: "aa.aa.aa.aa - bb.bb.bb.bb"
Range_From = IPSubnetCalc(4, IPandMaskInput)
Range_To = IPSubnetCalc(5, IPandMaskInput)
IPRange = Range_From & " - " & Range_To
End Function
Say it with me: Excel is not a database.
You're running up against the design limitations of the software: it's only got so much ability to store transactional data, so when multiple people are writing to it, it has to store a frickton of information in order to reconcile. You have so much data in it, that the transactional copies are HUGE.
Microsoft assumes (correctly) that if you have that much data, it's stored in a database, and you're just using Excel as a front end.
If you're going to work like that, you should at least knock together a little Access database. It will save you a world of headache, because it's meant to WORK like that and Excel just isn't.
@Josh: Yes, it absolutely IS leaking. When you share a document, it has to keep track of the modifications done by every user...I'll call this "transactional data" but you can just think of it as history. Since there is never an "official" version, it keeps keeping track of changes, and the document bloats up faster than Kirstie Alley in a doughnut shop.
It's by design. Someone who is an excel guru may be able to tell you how to make it stop, but the best solution is just not to use excel for data that's being constantly maintained. It's not really what it's designed for.
I am sympathetic to your problem, but it's a better solution to explain the problem to the higher-ups and work out a new procedure, than it is to try and prolong an unfortunate hack.
@Josh: If you just want to shrink it once, temporarily, copy all the data, and paste it into a new spreadsheet. THAT will kill all the metadata, guaranteed (make sure you don't select the whole sheet, but only the part with data in it) But this is a temporary solution at best.
Best Answer
Be sure to check both Disabled Items and COM Add-ins. I've seen it show up in disabled items but also seen the COM add-in get unchecked. you'll have to launch excel using run as admin in order to re-check that box. then close, relaunch normally and try again. Other thought - be sure to check the registry for excel add-ins and the load behavior is not set to 2.
from Autonomy support page:
"an error occurred in the com-add-in name 'iManO2k'. Error #429 ActiveX component can't create object Location:[modOffice2000Common][ShowLocalFileSaveAsDlg]
Cause: A shared resource, specifically, the scrrun.dll, has become unregistered or corrupted. Scrrun.dll is a system DLL that provides additional objects for reading and writing text files in a file system.
Resolution: Re-register the scrrun.dll. To register a dll, go to Start | Run and type regsvr32.exe [location of dll] scrrun.dll
More from Autonomy Support: just more of the same scrrun.dll and some long winded, best practices and other explanations of their dll's.
sorry can't be of more help.