Excel addins causing problems with PHP/COM script

[Andrew-]

I have a PHP script that uses COM objects to access a spreadsheet. The problem is, the cell that has the value I want to retrieve cannot calculate the value; it gives an "invalid name" error. If I stop the script, remove all the addins, and then add them back in, it works like it should (but this would have to be done every time the script is run, so doing this manually isn't really an option). Everything was also working properly before we made a slight modification to the spreadsheet (but not the addins). Does anybody know where the problem lies? The thing that confuses me is that it works when all the addins are removed and then added in again, so it seems like the excel file and the script are fine, but the very same addins were working earlier.

If anyone can help me, I'd be very thankful.

[anilsolipuram]

I am not sure why you are getting that error.

I am not sure what is the solution for that, but there is way to disable and enable addins using macro.


AddIns("Access Links").Installed = False ' will disable Access Links addin

AddIns("Access Links").Installed = true ' will enable Access Links addin


so can actually use this code in workbook_open and workbook_close

like in workbook_open you can disable all addins and in workbook_close you can enable all disabled addins, so when your workbook is opened all the addins are disabled and before the workbook is closed all the disabled addins are enalbed.



this code has to be in "this workbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Access Links").Installed = true' will disable Access Links addin
'and more
End Sub

Private Sub Workbook_Open()
AddIns("Access Links").Installed = False ' will disable Access Links addin
'and more

End Sub

[Andrew-]

Thanks for your help. Actually, the only way it will work is if the addins are disabled, and the re-enabled at startup. So I made a macro called Workbook_Open:

Private Sub Workbook_Open()
addins("Analysis ToolPak").Installed = False
addins("Analysis ToolPak - VBA").Installed = False
addins("Conditional Sum Wizard").Installed = False
addins("Lookup Wizard").Installed = False
addins("Solver Add-in").Installed = False
addins("Analysis ToolPak").Installed = True
addins("Analysis ToolPak - VBA").Installed = True
addins("Conditional Sum Wizard").Installed = True
addins("Lookup Wizard").Installed = True
addins("Solver Add-in").Installed = True
End Sub

Unfortunately, it only works if I manually run the macro. I don't think it's running when the workbook is opened.

[anilsolipuram]

It should work.

You have the paste the code at correct location.
go to tools->macro->visual basic editor-> double click "this workbook", paste the below code.






Private Sub Workbook_Open()
addins("Analysis ToolPak").Installed = False
addins("Analysis ToolPak - VBA").Installed = False
addins("Conditional Sum Wizard").Installed = False
addins("Lookup Wizard").Installed = False
addins("Solver Add-in").Installed = False
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
addins("Analysis ToolPak").Installed = True
addins("Analysis ToolPak - VBA").Installed = True
addins("Conditional Sum Wizard").Installed = True
addins("Lookup Wizard").Installed = True
addins("Solver Add-in").Installed = True
End Sub

[Andrew-]

Where do I double click "this workbook"? When I go into the Visual Basic Editor, all I get is a window with a menu bar and a toolbar.

Thanks again for your help.

[anilsolipuram]

When you go to vba editor,

go to view->project explorer, this will make project explorer visible, it is visible by default.

Go to project explorer toolbar,now you will see vba project(your_file_name), double click it to expand, double click "microsoft excel objects" to expand, now you see "this workbook", double click the "this workbook" and paste the below code .

Private Sub Workbook_Open()
addins("Analysis ToolPak").Installed = False
addins("Analysis ToolPak - VBA").Installed = False
addins("Conditional Sum Wizard").Installed = False
addins("Lookup Wizard").Installed = False
addins("Solver Add-in").Installed = False
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
addins("Analysis ToolPak").Installed = True
addins("Analysis ToolPak - VBA").Installed = True
addins("Conditional Sum Wizard").Installed = True
addins("Lookup Wizard").Installed = True
addins("Solver Add-in").Installed = True
End Sub

[Andrew-]

For some reason the project explorer wasn't visible by default. Thank you for your help, that got it to work and startup and close. However, what I needed was for the addins to be deactivated and then reactivated as soon as the file opens (for some reason, this is the only way it works, and I have no idea why). I used this code instead:

Private Sub Workbook_Open()
addins("Analysis ToolPak").Installed = False
addins("Analysis ToolPak - VBA").Installed = False
addins("Conditional Sum Wizard").Installed = False
addins("Lookup Wizard").Installed = False
addins("Solver Add-in").Installed = False
addins("Analysis ToolPak").Installed = True
addins("Analysis ToolPak - VBA").Installed = True
addins("Conditional Sum Wizard").Installed = True
addins("Lookup Wizard").Installed = True
addins("Solver Add-in").Installed = True
End Sub

Now it works.

Thank you very much for your help!