Posts by AJW

    Hi Richie,


    The created date after edit date is because it was created from a template that was lasted edited in 97.


    The benefit is that I can show my boss exactly how much of my own time i've spent creating a free package for them to use. It helps with time justification and resource allocation. Also handy to show a client when you bill them !


    Hi Roy,


    Yes I've been there and seen it as well, darn frustrating that they only did half the job.


    Thanks


    AJW



    Regards


    AJW

    Insomniac,


    Thanks heaps for the code and tips. It may take a little time for me to get at it due to current workload but I'm keen to give it a run. Hope you don't mind if I pick your brains on this now and again.


    Thanks again.


    AJW :yes:

    "Formulae can increase file size, but they can also be converted to values only. What I often do is have a Workbook Open Event that pulls in all external values via formulae, then converts them all to values only. :cool: "


    Thanks for the above suggestions Dave, unfortunatly I need the data and formula to remain live and ready to update should changes need to be made. We are constantly doing revision after revision of quote using the last revision. I trie converting or copying everything and pasting text only once before just as an exercise but didn't gain much from it. (That's when I realised that I needed to maintain the links.)


    I really like the idea of somehow recording the data entry & events then saving them only. I've been reading up on Workbook merge, Change History etc. It appears that this only applies to shared
    workbooks, I need to figure a way to firstly turn on the change history and secondly activate the merge_workbooks for an unshared one ?


    All too hard at he momment with a million other things to do. I'll need to give my approach some serious thought.


    AJW

    Hi Dave,


    Thanks for the link, I've done all that many times over.


    I'm pretty sure that even your File Size Reducer Addin would have a problem finding me some savings ;)


    Thanks anyway.


    :o Insomniac..............you are getting sleepy........very very sleepy.........listen to the dull tones of your computer fans buzzing.................listen to the rythmic clicking of your hard drive...............your eyelids are now like lead........sleeeeeep........sleeeeeeeep..............zzzzzzzzzzzzzzzzzzzz.


    Now you will send me all the code you used to do the text capture "Pleasssssse"



    OK WAKE UP!!!!!!!!


    Sorry just messin around
    :D


    Tell me how did you capture the text entries and save them to a text file?


    Do you have an example of the code you used to do this? It would save me a swag of time if you were a very very very nice person and would share
    :)


    Grovel grovel smile :D


    AJW

    Well not really ;)


    I should have said "ONE THING I MISS ABOUT Lotus 123".


    In Lotus 123 in Workbook Properties under the Statistics TAB they showed you what the Total Edit Time was and also the Total Revisions.


    Does anyone know if this can be extracted for an Excel Workbook ??


    Thanks


    AJW

    Egad,


    Thanks for tips and ideas. I've done most of this already, my application is a little complex in that each column of data is treated as a seperate set of data.


    It's a quote package and each column represent a piece of equipment. One Sheet is for calculations and selections, one for costing, one for sell pricing, one for factory orders etc. and each column represents a piece of equipment.


    It can be populated upto the last column or 251 pieces of equipment so memory use is mainly a function of the amount of data being physicaly entered.


    Having thought about this a bit I am toying with the idea of having the input recorded via VBE into a new module and then having the module saved with the filename into a specific location as a *.bas file through the VBE.


    Next time they want to view the file they can import the *.bas which will auto execute (by code) through an interface I will build. This way I am only saving the actual keystrokes and commands they have performed and not the whole template. When they open a new template they will have the option of populating it with an existing dataset.


    Of course this is a very rough idea at this time and will need a fair amount of work to perfect. That's why i was hoping someone else may have already done similar??


    Thanks again


    AJW

    Hi All,


    I have a problem.


    This ones fairly complex, for me anyway ;).


    I have a template that is opened (as a copy) and then worked on. A lot of information is entered and calculated as well as imported by way of FORM addins etc. The end result is that the file is SavedAs xyz.xls or whatever name. The file ends up being in the order of 3 to 4 MB in size by the time the project is finished.


    The problem is that many people are using the template and saving finished copies which equates to mega memory use.


    ie 30 users x 3 projects a day x 3MB = 270MB memory a day x 365 days = 98550MB a year. OUCH !!


    Everyone loves the project because it streamlines their work and reduces what used to take 5 days to do down to 2. But the all powerfull IT department have slammed it because of memory use.

    But back to the problem, how can I reduce the file size?? I've stripped my code back, I've deleted all unecessary formating, removed unwanted links........etc. and still have a fairly big file.


    I was wondering if anyone has ever thought about a way to save just the information that was entered when the workbook template was opened to the point of closing. And then to have it merge or combine with a template when you want to edit etc and again save the information. Essentially so that the template acts like an operating system and you only save the entred data???


    This way it would reduce the size of the files dramatically because I'm not saving the whole template again.


    Any thoughts, suggestions or help ??

    Thanks in advance.


    AJW

    Egad,


    Yes a VBA module can be hacked fairly quickly and I am pretty good at it.


    (I could acctualy sell you an addin that does it if I were that way inclined, but I never told you that OK).


    The best way to deter or I guess confuse a hacker is to give them a password that then needs to be deciphered.


    What does that mean??


    Well for example lets say you have a password: "EgadsSpecialPassword"


    If I were to hack the file with this password on the VBA code I would be looking to get a text feedback of the actual password (most hackers do).


    So instead of giving them an easy to read text string as above try something different like putting in odd characters and my favorite SPACES, yes good old fashioned blank spaces can drive a hacker nuts.


    Your special password may become:


    "Egads Special_________________________________________ Pass word"


    Give it a go but do remember to maintain the exact password in a seperate file or hardcopy somewhere. Forget how many spaces, underscores etc and your stuffed.


    AJW

    Epidemic,


    You sound a bit like me, difference being that I have done exactly that where I work.


    The 21st century has smacked them right between the eyes. Problem being that I'm not getting paid to do it (yet).


    Everything our guys did was manually and by their own devices (different). I've digitised the whole thing, and majorly reduced the development time by wrapping it up in the Excel environment and driving it with VBA.


    Like you I was fairly new to VBA so I've learnt on the fly and snatched and modified code to suit my requirements. Personally if you have a job to do this is the fastest approach, I'm now in the process of trying to get approaval for additional training based on the software developed.


    I think there is hugh potential to streamline systems and procedures using Excel and VBA. Opportunities abound !


    AJW

    As a suggestion you should be trying your local area as a starting point to build up a client base. Target your local computer outlets, last night I was in a shop and during conversation mentioned to the owner about my Excel VBA knowledge. As it turns out she has a book full of contacts that need Excel help. Hey presto a new client base! Next I would target local industry and especially an area of your expertise such as accounting, engineering etc. Many companies have some need of Excel development in either staff training or program design etc.


    AJW

    Do you mean completly stand alone running external to the Excel environment? In this case refer to XL-Dennis comments.


    Or do you want to have it so that it has an *.exe extension but still runs in the Excel environmet? See attached....


    AJW

    Anuja ??


    WillR's answer is correct except that he has it working through the Workbook Open event.


    From your Question I understand that you want it to work with a specific sheet selection ?? within a workbook.


    If this is the case then use the following code as per attached example file.



    Private Sub Worksheet_Activate()
    On Error GoTo Error
    Set Solv = AddIns("Solver Add-In")
    If Solv.Installed = False Then
    AddIns("Solver Add-in").Installed = True
    End If
    Exit Sub
    Error:
    Exit Sub
    End Sub


    Private Sub Worksheet_Deactivate()
    On Error GoTo Error
    Set Solv = AddIns("Solver Add-In")
    If Solv.Installed = True Then
    AddIns("Solver Add-in").Installed = False
    'This shuts solver to conserve memory
    Workbooks("Solver.XLA").Close SaveChanges:=False
    End If
    Exit Sub
    Error:
    Exit Sub
    End Sub


    Regards


    AJW