• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Script to Copy xla and add them

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
cbsbutler
Asked:
cbsbutler
  • 2
1 Solution
 
rd1966Commented:
Remove the SET from line 1 and 4 to get rid the error.

0
 
rd1966Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now