/build/static/layout/Breadcrumb_cap_w.png

VBScript to add a Macro in Excel

Hey guys,

Is there a reason why this:


Dim oXL, oAddin
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add("C:\Program Files\Datastream\Datastream Advance\AdvanceOffice.xla", True)
oAddin.Installed = True
oXL.Quit
Set oXL = Nothing


Will work when run manually via a VBS file but will not work if used via a Embedded VBScript Custom Action and/or Execute VBScript custom action either.

Now, I'm in NO way a VB expert, I fouind this piece of code on Google, tried it and it worked, hence why I was trying to include it in my package. If there are any problems with it, let me know how I could improve it please?

Thanks!

Stephane

0 Comments   [ + ] Show comments

Answers (7)

Posted by: anonymous_9363 14 years ago
Red Belt
0
Stephane, what error do you get when executing as a CA?

let me know how I could improve it please? Easy! Start again. That code is dreadful. No error-trapping, no 'Option Explicit'....hard-coded paths...ugh....Someone posted a reasonable script here on AppDeploy which included adding AND removing XL add-ins. Have a search for it.
Posted by: Fau 14 years ago
Senior Purple Belt
0
Wow are you serious?

This is what I hate from VB code found on Google, and not being an expert, well I rarely spot dreadful code. However, I will have a look on these message boards for the post you speak of.

You did ask for the error message. I don't see any when I install the package, that's what's odd. However, digging through the log file, searching for my CA, I see this:


MSI (s) (40:30) [13:01:29:537]: Doing action: Excel_Addin
Action ended 13:01:29: PublishProduct. Return value 1.
MSI (s) (40:30) [13:01:29:537]: Transforming table CustomAction.
MSI (s) (40:30) [13:01:29:537]: Transforming table CustomAction.
Action start 13:01:29: Excel_Addin.
MSI (s) (40:30) [13:01:29:537]: Doing action: InstallFinalize
Action ended 13:01:29: Excel_Addin. Return value 1.
Action start 13:01:29: InstallFinalize.


And further down...


MSI (s) (40:30) [13:02:35:412]: Executing op: ActionStart(Name=Excel_Addin,,)
MSI (s) (40:30) [13:02:35:412]: Executing op: CustomActionSchedule(Action=Excel_Addin,ActionType=3158,Source=C:\Program Files\Datastream\Datastream Advance\ExcelMacro.vbs,,)


Now, unless I'm really blind, I don't see anything that stands out. (In this occurence, my CA runs a local VB Script from installed files.)

I'll let you know if I find it.

Thanks Ian!

Stephane
Posted by: anonymous_9363 14 years ago
Red Belt
0
'Return value 1' means that the CA was successful. I presume, though, that your add-in doesn't appear when you start XL? Does it appear in the list of available add-ins, when you select 'Tools/Add-Ins'? And, if it does appear in the list, is the item unchecked?
Posted by: Fau 14 years ago
Senior Purple Belt
0
Hello Ian,

Sadly, the answer is no. It doesn't appear in Excel under the Addins menu. And naturally, doesn't seem to be installed.

I also found this script posted by "Scripting Machine" if I remember correctly.


Dim strXLApath : strXLApath = "C:\Program Files\Datastream\Datastream Advance\AdvanceOffice.xla"

'Call RemoveXLA(strXLApath)

Call AddXLA(strXLApath)

Sub AddXLA(strXLApath)
Dim oXL : Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Dim oAddin : Set oAddin = oXL.AddIns.Add(strXLApath, True)
oAddin.Installed = True
oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing
End Sub

Sub RemoveXLA(strXLApath)
Dim oXL : Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Dim oAddin : Set oAddin = oXL.AddIns.Add(strXLApath, False)
oAddin.Installed = False
oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing
End Sub


However, like you mentionned in your initial response, there's still no "on error resume" or nothing of the kind. So I'm not sure if this script is better either.

But... This script still doesn't add my macro in Excel either via Executed VBS file or Embedded VBS code.

I'll keep on looking!

Stephane
Posted by: Fau 14 years ago
Senior Purple Belt
0
Just found another post from Robo Scripter!

Going to give it a whirl!

Stephane
Posted by: anonymous_9363 14 years ago
Red Belt
0
I'm not sure if this script is better either.The fact that almost 100% of the code is identical in both functions should provide a clue as to its quality LOL.

Here's a quick test you can do - set up XL with low macro security and record a macro where you add the add-in. Take that code and paste it into your editor. Remove all the data-typing (i.e. where variables are declared "As String" or "As Object", etc) and compare it to what you have. I can't *see* anything missing but sometimes, the commas and quotes get hard to see when you're looking so hard!

[EDIT 16:10]On the above note, something nagging at the back of my mind suggests that you can either use the .Installed method on the same line as the .Add method or, if using separate lines, you need to specify the XLA's title not its name, as in:oAddin("Whatever text appears in Excel's add-ins list goes here").Installed = True[/EDIT]
Posted by: pgiesbergen 14 years ago
Orange Belt
0
Just some dumb questions, but where did you sequence your custom action? Make sure the xla file is installed before running the CA. And perhaps you set the CA to ignore the exit code?
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ