Solved

Script to Copy xla and add them

Posted on 2010-09-23
2
497 Views
Last Modified: 2012-05-10
I'm trying to copy 7 xla files to the "%appdata%\Microsoft\Addins" folder then add them to excel.

When executing the script (script attached) I receive the following error:
Error: Object required: '[string: "\\SERVER\DATA"]'
Code: 800A01A8
Source@ Microsoft VBScript runtime error

As well as rectifying this error, I'd like to know how to modify this code so it runs through 7 addins.
Addin1.xla
function2.xla
exceladdin.xla
testaddin.xla
Addin5.xla
buttosn.xla
savings.xla

Regards
Set SourceLocation="\\SERVER\DATA\Addin1.xla"

AppData=CreateObject("WScript.Shell").ExpandEnvironmentStrings("%APPDATA%")
Set DestinationLocation=AppData & "\Microsoft\Addins\"
CreateObject("Scripting.FileSystemObject").CopyFile SourceLocation, DestinationLocation, True

On Error Resume Next
Dim oXL
Dim oAddin
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add(Source, True)
oAddin.Installed = True
oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing

Open in new window

0
Comment
Question by:cbsbutler
  • 2
2 Comments
 
LVL 1

Expert Comment

by:rd1966
ID: 33742921
Remove the SET from line 1 and 4 to get rid the error.

0
 
LVL 1

Accepted Solution

by:
rd1966 earned 500 total points
ID: 33742956
I haven't test this, but could you put it in a subroutine?
CopyFile "\\SERVER\DATA\Addin1.xla"
CopyFile "\\SERVER\DATA\function2.xla"
...

Sub CopyFile(sourceLocation)
	SourceLocation="\\SERVER\DATA\Addin1.xla"
	AppData=CreateObject("WScript.Shell").ExpandEnvironmentStrings("%APPDATA%")
	DestinationLocation=AppData & "\Microsoft\Addins\"
	CreateObject("Scripting.FileSystemObject").CopyFile SourceLocation, DestinationLocation, True
	
	On Error Resume Next
	Dim oXL
	Dim oAddin
	Set oXL = CreateObject("Excel.Application")
	oXL.Workbooks.Add
	Set oAddin = oXL.AddIns.Add(Source, True)
	oAddin.Installed = True
	oXL.Quit
	Set oAddin = Nothing
	Set oXL = Nothing

End Sub

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question