Consulting

Results 1 to 12 of 12

Thread: Solved: Create an .XLA file

  1. #1
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Solved: Create an .XLA file

    Does anyone know how to make a .xla file for your vba to use.
    Because it lists a few of em and I was just wondering how I would make my own. I want to create a macro that saves and then exits on all excel workbooks so I can use a shortcut key to do it.

  2. #2
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location

    .XLA File

    Get your code the way you want it as an XLS. Then when you're happy, on the File | Save As... command under Save As Type option, scroll all the way down to the bottom - XLA is the last option.

    You can then Install with Tools | Addins...

    FWIW - you can define a hotkey even if you just put the macro in your Personal macro WB.


    Regards,
    Greg

  3. #3
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    :)

    This one is Solved

    Thank You Greg

  4. #4
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi, ideally you want your addin to be as small as possible so it wont have any adverse affects for your users. Heres some advice extracted from John Walkenbach's Excel 2002 Power Programming with VBA...

    FILE SIZE
    Excel workbooks (including add-ins) have always suffered from a serious problem:

    bloat. You may have noticed that the size of your files tends to increase over time,

    even if you don?t add any new content. This is especially true if you delete a lot of

    code and then replace it with other code. Making lots of changes to worksheets also

    seems to add to file bloat.

    If you want to make your add-in?or any workbook, for that matter?as small as

    possible, you?ll need to re-create your workbook. Here?s how:

    1. Make a backup of your application, and keep it in a safe place.

    2. Activate the VBE, and export all the components for your project that contain

    VBA code (modules, code modules, UserForms, and possibly ThisWorkbook,

    worksheet, and chart modules). Make a note of the filenames and the location.

    3. Create a new workbook.

    4. Copy the contents of all the worksheets from your original application to

    worksheets in the new workbook. Be especially careful if you used named

    ranges in your workbook?they must be re-created.

    5. Import the components you exported in Step 2.

    6. Compile the code.

    7. If applicable, reattach any toolbars that were attached to your original workbook.

    8. Save the new workbook.

    9. Test the new workbook thoroughly to ensure that nothing was lost in the

    process.

    There?s an excellent chance that the newly created file will be much smaller than

    your original. The size reduction depends on many factors, but I?ve been able to

    reduce the size of my XLA files by as much as 55 percent with this process.

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location

    Putting your XLA's on a diet

    Parry,

    I read that chapter a while back and your post prompted me to check the CD. But in the folder for Chapter 21, there are only two items, neither of which appear to be a utility that would do all of this for you. Are you aware of Walkenbach (or anyone else) already having done up a utility for this? I've got a hum-dinger of an add-in that I suspect is as bloated as the federal budget and I'd like to put it on a diet, so I may end up writing a utility for this for myself (I don't think it'd be all that hard), but thought I'd ask if you'd seen something on the net already.

    Regards,
    Greg

  6. #6
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    Thank You

    Thank you all for all the good help. Especially you Parry for typing for a while, you didnt have to do that.

    Where would books like that be found or I should say, where did ya'll get yours?

  7. #7
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location
    http://j-walk.com/ss/books/index.htm

    There are, of course, others, but J-Walk's among the best I've come across.

    As to where to get it - since our corp. purchasing (finally) figured out how to use Amazon, I got it there, but I'd say buy whereever you can get your finance manager to sign off on using!

    Um - should add, Mr. Excel's offering some new titles. But I'm not familiar with their content.
    Greg

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    MrExcel Books: http://www.mrexcel.com/sunshop/index...on=&id=&subid=

    Bill Jelen & Tracy Syrstad's new book: VBA & Macros for Microsoft Excel

    Which is supposed to be an excellent book, and I'll probably get (next time I have $$).

  9. #9
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Quote Originally Posted by Greg T
    Parry,

    I read that chapter a while back and your post prompted me to check the CD. But in the folder for Chapter 21, there are only two items, neither of which appear to be a utility that would do all of this for you. Are you aware of Walkenbach (or anyone else) already having done up a utility for this? I've got a hum-dinger of an add-in that I suspect is as bloated as the federal budget and I'd like to put it on a diet, so I may end up writing a utility for this for myself (I don't think it'd be all that hard), but thought I'd ask if you'd seen something on the net already.

    Regards,
    Hi Greg not that Im aware of unless JW put this in his PUP utility. Personally I dont create that many add-ins so I havent really worried about it but yes you could create some code - an Add-In in itself :-) - that cleans it all up. I would probably do it myself though as I tend to do extra things like delete all sheets except one to get it down as much as poss.

  10. #10
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi guys,

    You may be interested in Rob Bovey's Code Cleaner utility - its designed to help clear-up exactly the sort of 'bloat' that JW refers to.

    http://www.appspro.com/Utilities/CodeCleaner.htm

  11. #11
    VBAX Regular
    Joined
    Jul 2004
    Posts
    15
    Location

    Thanks

    Richie,

    Thanks for that link. I downloaded the utility and am looking forward to giving it a try when I have a moment.

    Regards,
    Greg

  12. #12
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    134
    Location

    ;)

    Thanks also Richie!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •