I've seen numerous exchanges with regards to using VBScript to install an Excel Addin. Most of them deal with using it to install a .XLA or .XLL.
I have been trying for numerous hours to install a DLL (written in VB6). I've done this successfully manually on numerous computers and now I am trying to automate it. I am going to skip the code where I copy it to a folder and register it via regsvr32.
My program fails on the following line
Set oAddin = xlApp.AddIns.Add(strAddInPath, True) where strAddInPath = "c:\example\mydll.dll"
I get the error message
RunTime error '1004'
Addins Method of Addins Class Failed.
I know the part of having to create a workbook before doing the Addins line.
Has anyone gotten this process to work with a DLL? Or does this only work with XLA/XLL?
My code is below
' subset of install program
' prior part installs mydll.dll into the proper directory and registers it with regsvr32
' define constants
strAddInPath = "c:\example\mydll.dll"
strFileName = "mydll.dll"
strHead = "Install of mydll.dll"
DebugMode = False
lngButtons = vbExclamation or vbokonly or vbDefaultButton1 or vbApplicationModal
Set oWS = CreateObject("WScript.Shell")
Set FSO = CreateObject("Scripting.FileSystemObject")
' if Excel is already loaded - grab it
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
' if Excel is not loaded - start an instance of it
if xlApp = Nothing then
Set xlApp = CreateObject("Excel.Application")
End if
' during debug - show Excel from the viewer
xlApp.Visible = True
strMsg = xlApp.Name
On Error Goto 0
' see if Excel was successfully loaded or started. if not -- it does not exist on the computer
if strMsg = vbNullString then
strMsg = "ERROR: Unable to run Microsoft Excel. Terminating install program."
msgBox strMsg, lngButtons, strHead
call KillScript("Cannot Find or Load Excel")
end if
' add a workbook
xlApp.Workbooks.Add
blnAddinReg = False
With xlApp.Application
For F = 1 to .AddIns.Count ' loop through all the add ins looking for mydll.dll
if LCase(.AddIns(F).Name) = strFileName then
blnAddinReg = True
Exit For
end if
Next
if blnAddinReg = False then ' if mydll.dll was not found/installed - then set the flag to install and activate it
i = 0
else ' if mydll.dll was found - make sure it is active/chkbox
if .AddIns(F).Installed = False then
.AddIns(F).Installed = True
i = 1
else
i = 2
end if
end if
End With
' need to install Add-in IF i=0
if i = 0 then
On Error Resume Next
Set oAddin = xlApp.AddIns.Add(strAddInPath, True) '< == fails on this line with
if err.Number <> 0 then
i = 3
else
oAddin.Installed = True
if err.Number <> 0 then
i = 4
end if
end if
end if
On Error Goto 0
' determine what happened with the process and let the User know the result
if i = 0 then
strMsg = "SUCCESS: mydll.dll was not installed so it was INSTALLED and ACTIVATED in Excel."
elseif i = 1 then
strMsg = "SUCCESS: mydll.dll was installed but not ACTIVE so it was ACTIVATED in Excel."
elseif i = 2 then
strMsg = "NO CHANGE: mydll.dll was already INSTALLED and ACTIVE in Excel."
else i = 3 then
strMsg = "ERROR: Issue with AddIns.Add, Error msg = " & err.description
else
strMsg = "ERROR: Issue with oAddin.Installed = True, Error msg = " & err.description
end if
MsgBox strMsg, lngButtons, strHead
xlApp.Quit
Set xlApp = Nothing
WScript.Quit
