Scripting

You don't need to create a new Excel object. Try this (untested):Dim blnReturn
Dim blnIsError
Dim objXL
Dim objWorksheet
Dim objAddin
Dim strAddInPath
Dim strAddInName
Dim strAddIn
Dim objWSHShell
Dim objFSO
Dim strMsg

Set objWSHShell = CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

strAddInPath = "C:\Program Files\Microsoft Office\Office10\Library\Analysis"
strAddInName = "ANALYS32.XLL"

blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"

WScript.Echo strMsg
WScript.Quit(False)
End If

strAddInName = "SOLVER.XLA"

blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"

WScript.Echo strMsg
WScript.Quit(False)
End If

Set objFSO = Nothing
Set objWSHShell = Nothing


Function ExcelAddin(ByVal strPath, ByVal strName, blnAdding)

Dim objFSO_XL
Dim intCounter
Dim blnInstalledAlready

ExcelAddin = False
blnInstalledAlready = False

If Right(strPath, 1) = "\" Then
'// Strip trailing backslash
strPath = Left(strPath, Len(strPath) - 1)
End If

strAddIn = strPath & "\" + strName

strMsgNotInstalled = "'" & strAddIn & "' was not installed."
strMsgNotRemoved = "'" & strAddIn & "' was not removed."

If blnAdding Then
'// We only care about this if we're installing
Set objFSO_XL = CreateObject("Scripting.FileSystemObject")

With objFSO_XL
strMsg = ""
On Error Resume Next
'// Check source path exists
If Not .FolderExists(strPath) Then
strMsg = "The add-in source folder " & strPath & " does not exist." & vbCRLF & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Exit Function
End If

'// Check source file exists
If Not .FileExists(strAddIn) Then
strMsg = "The source file " & strAddIn & " does not exist." & vbCRLF & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Exit Function
End If

On Error Goto 0
End With
End If

On Error Resume Next
Set objXL = CreateObject("Excel.Application")
If Err.Number <> 0 Then
blnIsError = True
strMsg = "Failed to create Excel object." & vbCRLF
If blnAdding Then
strMsg = strMsg & strMsgNotInstalled
Else
strMsg = strMsg & strMsgNotRemoved
End If
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Created Excel object." & vbCRLF
WScript.Echo strMsg
End If

If blnAdding Then
'// We only need this if we're installing
Set objWorksheet = objXL.Workbooks.Add()
If Err.Number <> 0 Then
blnIsError = True
strMsg = "Failed to create new workbook." & vbCRLF
If blnAdding Then
strMsg = strMsg & strMsgNotInstalled
Else
strMsg = strMsg & strMsgNotRemoved
End If
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Created worksheet object." & vbCRLF
WScript.Echo strMsg
End If
End If

With objXL
For intCounter = 1 to .Addins.Count
If .Addins.Item(intCounter).Installed Then
blnInstalledAlready = True
Exit For
End If
Next

If blnAdding Then
If Not blnInstalledAlready Then
Set objAddin = .AddIns.Add(strAddIn)
If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCRLF
strMsg = strMsg & "Failed to add add-in '" & strAddIn & "'." & vbCRLF & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Else
objAddin.Installed = True
If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCRLF
strMsg = strMsg & "Failed to set add-in installed status." & vbCRLF & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Add-in '" & strAddIn & "' installed successfully." & vbCRLF
WScript.Echo strMsg
blnInstalledAlready = True
End If
End If
Else
strMsg = "Add-in '" & strAddIn & "' is already installed." & vbCRLF & strMsgNotInstalled
blnIsError = False
WScript.Echo strMsg
End If
Else
If blnInstalledAlready Then
'// intCounter ought still to be at the correct position,
'// since we exited the For...Next loop when we located the add-in
.Addins.Item(intCounter).Installed = False

If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCRLF
strMsg = strMsg & "Failed to remove add-in '" & strAddIn & "'." & vbCRLF & strMsgNotRemoved
blnIsError = True
WScript.Echo strMsg
Else
strMsg = "Add-in '" & strAddIn & "' removed successfully." & vbCRLF
blnIsError = False
WScript.Echo strMsg
blnInstalledAlready = False
objAddin.Installed = False
End If
Else
strMsg = "Add-in '" & strAddIn & "' is not installed, so no removal necessary." & vbCRLF & strMsgNotRemoved
blnIsError = False
WScript.Echo strMsg
End If
End If
End With

If blnAdding Then
If blnInstalledAlready Then
'// We test blnInstalledAlready because objAddIn.Installed may not be set if the installation failed
ExcelAddin = True
End If
Else
If Not blnInstalledAlready Then
'// We test blnInstalledAlready because objAddIn.Installed may not be set if the installation failed
ExcelAddin = True
End If
End If

objXL.Quit

On Error Goto 0

Set objFSO_XL = Nothing
Set objAddin = Nothing
Set objXL = Nothing
End Function

Hi VBScab!

I tested your sugestion but failed with a compile error: invalid outside procedure at line 12 {Set objWSHShell = CreateObject("Wscript.Shell")} with the command Set.

How can I solve this?

Additional to your tip, I insert an auto load at start-up command (tested ok):

Private Sub Workbook_Open()
Call ExcelAddin
End Sub

Should be a network diretive that do not allow to use objWSHShell

[:(]

Should be a network diretive that do not allow to use objWSHShell Er....nnnnnnot quite.....

That script was designed as a VBScript. If you're using it as a VBA module, you should add data types to the variables, e.g.Dim objXL As Excel.Application and so on.

Coming to the problem, you need to move the declarations to either the top of the entire project window or into the routine:Public blnReturn As Boolean
Public blnIsError As Boolean
Public objXL As Excel.Application
Public objWorksheet As Excel.Worksheet
Public objAddin As Object
Public strAddInPath As String
Public strAddInName As String
Public strAddIn As String
Public objWSHShell As IWshRuntimeLibrary.IWshShell
Public objFSO As Scripting.FileSystemObject
Public strMsg As String

Sub Workbook_Open()
Set objWSHShell = CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

strAddInPath = "C:\Program Files\Microsoft Office\Office10\Library\Analysis"
strAddInName = "ANALYS32.XLL"

blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"

WScript.Echo strMsg
WScript.Quit(False)
End If

strAddInName = "SOLVER.XLA"

blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"

WScript.Echo strMsg
WScript.Quit(False)
End If

Set objFSO = Nothing
Set objWSHShell = Nothing
End Sub

The code could be tidied up quite a bit by, for example, using an array for the add-in names (which would avoid the duplication) but at least it works. The Public declarations are somewhat moot, too...

Hi VBScab!

Well.... I tested your sugestion and did't work as a VBA module. But I tried to use your first sugestion of script as a VBS extension archive (script.vbs) and.... IT's WORK, but only for Analysis32.xll. For Solver not. I tried to insert strAddInPath = "C:\Program Files\Microsoft OFFICE\OFFICE10\LIBRARY\SOLVER" before strAddInName = "SOLVER.XLA", as you used for analysis but was unsuccessful.

the script now is:
Dim blnReturn
Dim blnIsError
Dim objXL
Dim objWorksheet
Dim objAddin
Dim strAddInPath
Dim strAddInName
Dim strAddIn
Dim objWSHShell
Dim objFSO
Dim strMsg
Set objWSHShell = CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strAddInPath = "C:\Program Files\Microsoft OFFICE\OFFICE10\LIBRARY\analysis"
strAddInName = "ANALYS32.XLL"
blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"
WScript.Echo strMsg
WScript.Quit (False)
End If
strAddInPath = "C:\Program Files\Microsoft OFFICE\OFFICE10\LIBRARY\SOLVER"
strAddInName = "SOLVER.XLA"
blnReturn = ExcelAddin(strAddInPath, strAddInName, True) '// Pass 'True' to install, 'False' to uninstall
If Not blnReturn Then
blnIsError = True
strMsg = "Unable to install Excel add-in '" & strAddInName & "'"
WScript.Echo strMsg
WScript.Quit (False)
End If
Set objFSO = Nothing
Set objWSHShell = Nothing

Function ExcelAddin(ByVal strPath, ByVal strName, blnAdding)
Dim objFSO_XL
Dim intCounter
Dim blnInstalledAlready

ExcelAddin = False
blnInstalledAlready = False

If Right(strPath, 1) = "\" Then
'// Strip trailing backslash
strPath = Left(strPath, Len(strPath) - 1)
End If

strAddIn = strPath & "\" + strName
strMsgNotInstalled = "'" & strAddIn & "' was not installed."
strMsgNotRemoved = "'" & strAddIn & "' was not removed."
If blnAdding Then
'// We only care about this if we're installing
Set objFSO_XL = CreateObject("Scripting.FileSystemObject")
With objFSO_XL
strMsg = ""
On Error Resume Next
'// Check source path exists
If Not .FolderExists(strPath) Then
strMsg = "The add-in source folder " & strPath & " does not exist." & vbCrLf & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Exit Function
End If
'// Check source file exists
If Not .FileExists(strAddIn) Then
strMsg = "The source file " & strAddIn & " does not exist." & vbCrLf & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Exit Function
End If
On Error GoTo 0
End With
End If

On Error Resume Next
Set objXL = CreateObject("Excel.Application")
If Err.Number <> 0 Then
blnIsError = True
strMsg = "Failed to create Excel object." & vbCrLf
If blnAdding Then
strMsg = strMsg & strMsgNotInstalled
Else
strMsg = strMsg & strMsgNotRemoved
End If
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Created Excel object." & vbCrLf
WScript.Echo strMsg
End If
If blnAdding Then
'// We only need this if we're installing
Set objWorksheet = objXL.Workbooks.Add()
If Err.Number <> 0 Then
blnIsError = True
strMsg = "Failed to create new workbook." & vbCrLf
If blnAdding Then
strMsg = strMsg & strMsgNotInstalled
Else
strMsg = strMsg & strMsgNotRemoved
End If
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Created worksheet object." & vbCrLf
WScript.Echo strMsg
End If
End If
With objXL
For intCounter = 1 To .AddIns.Count
If .AddIns.Item(intCounter).Installed Then
blnInstalledAlready = True
Exit For
End If
Next
If blnAdding Then
If Not blnInstalledAlready Then
Set objAddin = .AddIns.Add(strAddIn)
If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCrLf
strMsg = strMsg & "Failed to add add-in '" & strAddIn & "'." & vbCrLf & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Else
objAddin.Installed = True
If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCrLf
strMsg = strMsg & "Failed to set add-in installed status." & vbCrLf & strMsgNotInstalled
blnIsError = True
WScript.Echo strMsg
Else
blnIsError = False
strMsg = "Add-in '" & strAddIn & "' installed successfully." & vbCrLf
WScript.Echo strMsg
blnInstalledAlready = True
End If
End If
Else
strMsg = "Add-in '" & strAddIn & "' is already installed." & vbCrLf & strMsgNotInstalled
blnIsError = False
WScript.Echo strMsg
End If
Else
If blnInstalledAlready Then
'// intCounter ought still to be at the correct position,
'// since we exited the For...Next loop when we located the add-in
.AddIns.Item(intCounter).Installed = False
If Err.Number <> 0 Then
strMsg = ""
strMsg = strMsg & "Error: " & Err.Description & vbCrLf
strMsg = strMsg & "Failed to remove add-in '" & strAddIn & "'." & vbCrLf & strMsgNotRemoved
blnIsError = True
WScript.Echo strMsg
Else
strMsg = "Add-in '" & strAddIn & "' removed successfully." & vbCrLf
blnIsError = False
WScript.Echo strMsg
blnInstalledAlready = False
objAddin.Installed = False
End If
Else
strMsg = "Add-in '" & strAddIn & "' is not installed, so no removal necessary." & vbCrLf & strMsgNotRemoved
blnIsError = False
WScript.Echo strMsg
End If
End If
End With

If blnAdding Then
If blnInstalledAlready Then
'// We test blnInstalledAlready because objAddIn.Installed may not be set if the installation failed
ExcelAddin = True
End If
Else
If Not blnInstalledAlready Then
'// We test blnInstalledAlready because objAddIn.Installed may not be set if the installation failed
ExcelAddin = True
End If
End If

objXL.Quit
On Error GoTo 0

Set objFSO_XL = Nothing
Set objAddin = Nothing
Set objXL = Nothing
End Function


Despite solver tool, for this script we need have the files copied localy. For this propose, I created a .cmd file (Copy.cmd) with this code:
@echo off
C:
xcopy J:\GENASDV2\Core\OfficeXP\FILES\PFILES\MSOFFICE\OFFICE10\LIBRARY "C:\Program Files\Microsoft Office\Office10\Library" /T
xcopy J:\GENASDV2\Core\OfficeXP\FILES\PFILES\MSOFFICE\OFFICE10\LIBRARY "C:\Program Files\Microsoft Office\Office10\Library" /E /Y /H /D /Q
stop

How can I insert this code adapted to the VBS code listed before, without using the Call expression? With this solution the user will only need execute 1 file instead 2.

TKS!!!

Rodrigo, I posted the original script because I had it handy. I then went to the trouble of altering it so you could use it in a VBA module. I'm really sorry but I'm afraid I'm not going to continue to work for nothing. Script is simple: you can surely work things out for yourself, given what you have and by doing some basic research on the Internet. There must be thousands of examples on how to copy folders using VBS.

I meant to add to my *last* response that it's a really bad idea to use hard-coded paths in script, particularly in this case where, as soon as you upgrade to a new version of Office, the script will break (because there's no longer a folder called 'OFFICE10'). Somewhere on AppDeploy, I posted a script which uses the file association registry entry for '.XLS' files to get the path to EXCEL.EXE. Use what that function returns instead of the hard-coded path you have.

Last note: please use the 'code' tage for posting script or text of any great length. You can access that tag by using the button marked '<%'.

Tried your script (with path modifications for different version of office and removed the code for solver.xla) and recieved the 'Failed to set add-in installed status' message. Would you be able to help and tell me common reasons for this?

You don't say whether you're running the VBS or VBA version but either way, you need to trap the error returned before the part where the .Install method is called.

For VBS, install and use the Microsoft Script Debugger or, better, the Microsoft Script Editor (don't be fooled by the title: it's better as a debugger than an editor!).

For VBA, you can step through the code inside Excel. Press Alt+F11 to get into the code editor.

Once the code reaches the .Install method, test the contents of Err.Description. If that's empty (which it sometimes can be), try Err.Number andlook the number up in MSDN. Again, because MS seems unable to decide what format its error numbers should be, you may have to convert it to hex and/or decimal before you get a 'hit'.

I am using the VBS version and (i think this is the problem) Office 2007.

Testing the script on a machine with 2003 it works fine. But even just
Addins(analysis toolpak").Installed = true in VBA (with all files in the right place) fails with Error Number: 1004 - Unable to set the Installed property of the Addin class.

Any thoughts would be appreciated

What I meant for you to do was trap any error immediately before the .Installed method is invoked. From memory, that would be from the .Add method.

Why not record a macro and compare the resulting code with what you currently have?

ORIGINAL: VBScab
What I meant for you to do was trap any error immediately before the .Installed method is invoked. From memory, that would be from the .Add method.

Why not record a macro and compare the resulting code with what you currently have?

Your script already traps errors at that point and it wouldn't get to the .Installed method if there was one. Looking at how office was setup it seems that analysis toolpak(ATP) is set to Installed on First Use and this seems to be causing the problem as it works just fine if ATP has been installed previously.