0

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.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Damien Minter
  • 309
  • 5
  • 11
  • `application.Addins` returns an `addins` collection. – Noodles Feb 20 '19 at 06:25
  • @Noodles Unless you specify an index: https://learn.microsoft.com/en-us/office/vba/api/excel.application.addins#example – Egan Wolf Feb 20 '19 at 06:29
  • 1
    Try this `Application.AddIns.Add "Filepath to Addin", False`. And then try `Application.AddIns("QzData Excel Addin").Installed = True` – Siddharth Rout Feb 20 '19 at 06:50
  • I think you need to prefix your operations on the excel object with `Excel.` also. – Nathan_Sav Feb 20 '19 at 08:46
  • @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 Feb 20 '19 at 12:32
  • @AnsgarWiechers I read the post as being in VBScript. And the createobject confirmed this. They do in VBA as well, as they'll be working on the active application and not the created one. :) – Nathan_Sav Feb 20 '19 at 12:34
  • @Nathan_Sav They tried both. Check the last paragraph. – Ansgar Wiechers Feb 20 '19 at 12:40
  • @AnsgarWiechers regardless, they will still be creating an excel object and never referencing it. As they are making the object visible, I assumed that they were trying to open a new instance of excel and install the addins on said instance, if this is not the case, creating the object would be redundant. I don't have time to argue the ins and outs of others bad/good coding practices. Just here to offer guidance when I can :) – Nathan_Sav Feb 20 '19 at 14:00

2 Answers2

1

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
MacroMarc
  • 3,214
  • 2
  • 11
  • 20
1

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

Damien Minter
  • 309
  • 5
  • 11