Solved

Script to Copy xla and add them

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

23 Experts available now in Live!

Get 1:1 Help Now