Results 1 to 14 of 14
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xla xls addins macros (Excel/XP)

    Moderator: I'm posting here because this seems like a "deployment" issue rather than an end-user; the thread may be better housed in Excel?

    I think I've managed to boil it all down to two sentences, after all these years:

    (a) VBA code which I intend to be used as Function Procedures available to developers is savedAs an XLA and goes in the Addins folder (on my system "Cocuments and SettingsChris GreavesApplication DataMicrosoftAddIns").
    (b) VBA code which I intend to be used as Subroutine Procedures with no arguments ("macros") available to end-users is saved as XLS, is hidden, and goes in the Startup folder (on my system "Cocuments and SettingsChris GreavesApplication DataMicrosoftExcelXLSTART").

    Comments welcomed.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    I disagree.

    I see little benefit from having hidden workbooks.
    There is not much difference in deployment, apart from having to check an addin in the tools-addins dialog.
    But that can easily be automated.

    For other pointers on this matter: see my site on deploying macros in Excel:

    http://www.jkp-ads.com/Articles/DistributeMacro00.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    > see my site on deploying macros in Excel:

    Jan Karel , thank you very much for this guidance (in your response) and in what appears to me to be excellent material on your web site. I am this day starting to work through your steps, and will aim for XLA's.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    You're welcome.

    Should you find any problems with the info, let me know!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    Edited by HansV to remove spaces from URLs, they caused the links to fail. BTW, you can create a link to an MSKB article as <!t>[MSKB=161439]<!/t>

    > Should you find any problems with the info, let me know!

    No problems at all with the info (except that it has spurred me on into a mess of my own making!). I have migrated my Word/VBA "Setup.dot" to Excel/VBA "Setup.XLS", a workbook whose sole function is to collect XLA files from its own folder and install them as Addins.

    I'm running into the infamous '1004' error as described MSKB 161439 and MSKB 211956 and MSKB 280290. These MSKB jointly advise me to delete registry keys and to ensure that I have a workbook visible. This I have done, but still run into the error Run-time error '1004': Unable to get the Add property of the AddIns class'.
    My current code, pasted from a MSKB is <pre> Application.Workbooks.Add
    Dim myAddIn
    Dim strFileName As String
    strFileName = strTARGET & strList(lng)
    <font color=red>Set myAddIn = AddIns.Add(Filename:=strFileName)</font color=red>
    MsgBox myAddIn.Title & " has been added to the list"</pre>

    The code baulks at the AddIns.Add(Filename:=strFileName) at which time the value of strFileName is "Cocuments and SettingsChris GreavesApplication DataMicrosoftAddInsUtils.XLA, a file which is seen to exist.

    I have previously loaded Excel and checked that this particular addin is not loaded, so this ought to be a straightforward run. I'd appreciate any pointers to directions I might take.

    Addins.Count tells me "8", and the addins screen looks like this:

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    Jan Karel, please ignore, almost, my post of 30 minutes ago.

    I went back and tried manually to install my current Utils.XLA and learned through the UI that it was a poorly-formed XLA.
    The miscreant is me; I have essayed to implement a save-as-XLA macro, and I suspect that the means I have used (below) does NOT mirror what happens when I choose a Type from the SaveAs dialogue box.
    My suspicion arises because when I use the regular saveAs type-addin, the Setup.XLS runs flawlessly.

    <pre>Public Sub SaveAsXLA()
    Dim strFullName As String
    strFullName = ActiveWorkbook.FullName
    strFullName = Left$(strFullName, Len(strFullName) - 3) & "XLA"
    ActiveWorkbook.SaveAs Filename:=strFullName, FileFormat:=xlAddIn
    End Sub</pre>


  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    OK, great you could solve the problem.

    I have used code like this in the past:

    With Workbooks(sBookName)
    .IsAddin = True
    Application.DisplayAlerts = False
    .SaveAs Filename:=sAddinName, FileFormat:=xlAddIn
    Application.DisplayAlerts = True
    .IsAddin = False
    .Close False
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    > I have used code like this in the past:

    Thank you! You have now solved two more of my problems.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    The code is part of a sub that takes a list from a worksheet and saves the first file as an add-in, then picks up the remaining files of that list and zips it into a zip file, together with the just created add-in.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    Jan Karel , thanks again for an excellent primer on Deploying Excel.
    I have re-read your pages twice these past two days with especial attention to
    Page 2

    I now have a macro that will take a proposed add-in file name (e.g. "UX083.XLA"), and delete every form that matches the generic name ("UX???.XLA") in the appropriate registry keys of Offices 8.0, 9.0, 10.0, 11.0 and 12.0. So if I am releasing a better version of my Utility AddIn, the older versions such as UX082.XLA and UX081.XLA will vanish from the registry and I, and clients, should not get the aggravating "can't find add in" message when Excel loads.

    So much for the registry.

    Excel 2000, 2003 appears to save AddIns to c:documents and settingsuserapplication datamicrosoftaddins by default. Excel97 chooses the User's default File Location (Tools, Option, General)

    Where would you recommend installing the AddIn file itself (which will be part of the value of the registry key)?

    The Documents & Settings location is unique to a user. That is probably not an issue for the Business client, but does not render the AddIn universally available.

    Office 97, 2000 & 2003: With the AddIn in C:Program FilesOffice2003OFFICE11XLSTART, the code is available for use without the user traveling through the Tools, AddIn Dialogue. This would seem like an excellent strategy for a library of User-defined functions, for use by end=users to supplement regular Excel functions.

    If the XLA is a specialized application, it seems to make sense to store it away from the C:Program FilesOffice2003OFFICE11XLSTART folder, and activate it (Tools, AddIns) only when required.

    If the XLA is a common application, it seems to make sense to store it in the C:Program
    FilesOffice2003OFFICE11XLSTART folder, so that it is always available.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    Well, as you noted, the best spot for an addin to live depends on your goal.

    Personally, I'd just have it copied to the documents&settings path you indicated, as that is the default spot Excel looks for addins. I'm no fan of putting addins in XLSTART, as that makes it hard for people to load Excel without them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: xla xls addins macros (Excel/XP)

    Chris,
    If you put add-ins in XLSTART then:
    1. their .Installed property is set to False unless they are also flagged in the Add-ins dialog - this may be a problem if other add-ins depend on them.
    2. You cannot get rid of them except via code.

    This applies also if you open them via the File-Open dialog.

    HTH

    PS Any code you may have in the Workbook_AddInInstall event will not work.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    >the best spot for an addin to live depends on your goal.

    Jan Karel, thanks for this confirmation.
    I have utility workbook libraries. Generally a client will want these to be available at all times - end-users think of them as an extension to the set of built-in fucntions available through the function picker "f[smal]x</small>". These libraries should be installed in a manner which makes them always available to the end-user.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla xls addins macros (Excel/XP)

    Rory, thanks for these added tips. I've made a note of them, and will avoid XLStart as a spot for automatic installaiton of my AddIns.

Posting Permissions

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