Solved

Script to Copy xla and add them

Posted on 2010-09-23
2
496 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now