Difficulty in SaveAs an Addin

[Huyeote]

Hi all,

I have a workbook contains VBA code for work purpose and distribute it
to my collegues. I want to add code to either enable user to click a
button or even triggered by Workbook_Open event to save it as an Excel
addin and install it thereafter.

But I keep getting error message when I used different alternatives.

METHOD 1: AS FOLLOWING SIMPLE METHOD

Code:
--------------------

AddIns.Add(ThisWorkbook.FullName).Installed = True
--------------------


Excel returns Error 1004: Unable to copy add-in to liabrary. I guess
this is because our personal Addins folders are *hidden * and the Add
method can't access it.

METHOD 2: USING SAVEAS METHOD


Code:
--------------------
ThisWorkbook.SaveAs FileName:=fldr.Path & "\myAddin.xla" _
, FileFormat:=xlAddIn
AddIns("myAddin").Installed = True
--------------------

Note: fldr is a variable containing full path of the Addins folder.

This method yields Error 9: subscript out of range. When I manually
selete the addin file the code copied to liabrary folder
(Add-Ins...\Browse...), Excel said the file is not a valid add-in.

Any help on this annoying issue will be appreciated.

Thanks

Huyeote

 

[Mel Arquiza]

Hi Huyeote,

Hope this helps.

1. Start the Visual Basic Editor by pressing Alt+F11.
2. Select Debug, Compile VBA projectname. In previous versions of Excel the
VBA-code was automatically compiled when you saved the addin. This is no
longer true, you have to do it yourself before you save the addin.
3. If you want to lock the project from viewing you can select Tools,
Properties for VBA projectname.
4. In this dialog you activate Protection and check the option Lock project
for viewing.
5. Fill in a password and click the OK-button.
6. Activate Excel by pressing Alt+F11.
7. Select File, Properties..., Summary and fill inn information for the
fields Title and Comments.
8. The title will be the name that appears in the Add-Ins dialog (the dialog
displaying available add-ins),
the comment will be the description that appears when you select the addin
in the 9. Add-Ins dialog.
10. Click the OK-button to close the Properties dialog.
11. Select File, Save as….
12. Change the option Filetype: to Microsoft Excel addin (*.xla) (the last
choice in the dropdown).
13. The add-in must contain at least one worksheet if this option is to be
displayed.
Click the Save-button to save the workbook as an addin.

 

[Huyeote]

Thanks Mel, but my problem is with using a VBA code to save it as a
Excel add-in. Currently I use the same prcess as yours to save it as
add-in and documented it to allow my users to do the same thing. But
when I write code in the project to save Thisworkbook as an add-in,
Excel either tells me the file can't be copied to liabrary or doesn't
recoginse it as a valid add-in after copy. Can you help on the code and
tell me what's wrong with the code?

BTW, my Excel's version is XP (2002)
Operating system is Windows 2000 Terminal

Thanks,

Huyeote

 

[Mel Arquiza]

Hi Huyeote,

If you follow all this steps below in this weblink:

http://www.exceltip.com/st/How_to_Use_Your_Excel_Add-In_Functions_in_VBA/630.html

The problem next could be your Office XP Service Packs (SP3) not updated no
also sure.

Where is the code?

 

[Huyeote]

Thanks for your link, I've fixed the problem. Just need to change the
value of Thisworkbook.IsAddin property to False before copying the
workbook over to add-in liabrary.

For your information, following is the final code I use.

Code:
--------------------

ThisWorkbook.IsAddin = True 'set IsAddin property to true

ThisWorkbook.SaveAs FileName:=fldr.Path & "\myAddin.xla" _
, FileFormat:=xlAddIn
--------------------


Strangely, without tweaking *IsAddIn * property, the -FileFormat -
parameter in *SaveAs * Method doesn't help at all even when you specify
the format as -xlAddIn-. I could copy over the file to personal Addin
folder but Excel couldn't recognise it as a valid addin. Problem was
fixed after I change the IsAddin Property of the workbook. Do you know
why?

Thanks,

Huyeote