CreateObject("Excel.Application") and install Addins [duplicate]

0
votes

After spending the last 2 days tying to get this to work, I am finally reaching out to the community. I have tried multiple variations of the code below including many of the examples that have been posted on to here and other sites. To keep it simple, this is what I have: 

Dim Excel

Set Excel = CreateObject("Excel.Application")

Excel.Visible = True

AddIns("QzData Excel Addin").Installed = True

Essentially I am trying to use a script to open an excel file and run a macro. This macro requires me having the "QzData Excel Addin" installed. For simplicity I am just trying to get this working with a new excel sheet as I already have the correct code to open my actual file.

I have tried to run the above as a script (.vbs) and in Excel VBA itself and am getting a "subscript out of range" error.

2
excelvbavbscriptadd-increateobject
application.Addins returns an addins collection. - Noodles
@Noodles Unless you specify an index: docs.microsoft.com/en-us/office/vba/api/… - Egan Wolf
Try this Application.AddIns.Add "Filepath to Addin", False. And then try Application.AddIns("QzData Excel Addin").Installed = True - Siddharth Rout
I think you need to prefix your operations on the excel object with Excel. also. - Nathan_Sav
@Nathan_Sav When running the code as a VBScript. When running the code as VBA they don't (but then they also don't need to create an Excel instance in the first place). - Ansgar Wiechers

2 Answers

1
votes

When using CreateObject, Excel addins are not available by default:

https://support.microsoft.com/en-us/help/213489/add-ins-do-not-load-when-using-the-createobject-command-in-excel

So try:

Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Open ("Path\To\QzData Excel Addin.xlam")
AddIns("QzData Excel Addin").Installed = True
1
votes

So I managed to get this to work with a few tries based on the information provided above with the final code below

Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Excel.Workbooks.Open"\\xxxx\xx\xxx\xxx\xx\xxxxxxxxxx\xxxx\xxx\xxxxx\xxxxx\Workbooks\OpalToolbelt_Plex_Combined_2019_Feb.xlsm"
Excel.AddIns("QzData Excel Addin").Installed = False
Excel.AddIns("QzData Excel Addin").Installed = True

I had to put the object name "Excel" in front of AddIns as it wouldn't work without. Having "Application" in front also didn't work.

I also needed to uninstall the addin before reinstalling it else it would only work half the time.

As per the link provided by @MacroMarc, there is a suggestion that you need to use the RegisterXLL function for .xll addins. I found that my code worked fine without, but worth a mention

Excel.RegisterXLL "QzDataAddin.xll"

Thanks all for your help - This seemed to take a while for something quite simple