Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Script to Copy xla and add them

Posted on 2010-09-23
2
Medium Priority
?
501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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