[RESOLVED] runing an addin macro

[Muddy]

I recorded the following excel macro:

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$G$4:$G$8"), _
        "NewName", , False, True, True, False

I want to do this programatically from VB.

Here is my code:

Set wAddin = OutputSheet.Application.Workbooks.Open(AddIns(1).FullName)
 
     Application.AddIns(AddIns(1).Title).Application.Run "Histogram", Sheet1.Range(strTemp), _
        "NewName", , False, True, True, False

I get the error "The macro "Histogram" cannot found". I also tried a ! preceding Histogram ... same results ..

How can I make this work?

Thanks in advance for any help!

[Muddy]

here some exact code that causes the problem ... ANY help with this will be greatly appreciated ...

Private Sub Command1_Click()
Dim objXL As New Excel.Application
Dim mySheet As Object
objXL.Visible = True
Set mySheet = objXL.Workbooks.Add.worksheets(1)
objXL.Workbooks.Open (AddIns(1).FullName)
objXL.Run AddIns(1).FullName & "Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
objXL.Quit
Set objXL = Nothing
Set mySheet = Nothing
End Sub

[si_the_geek]

When specifying the macro to run you need to either specify just the macro name, or the filename and macro name with a ! between them.

In your last post you appear to be missing the ! again, I think it should be:

objXL.Run AddIns(1).FullName & "!Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False

This is of course assuming that AddIns(1).FullName is something which is valid within your VB program, rather than an Excel object you are trying to use.

[Muddy]

Thanks for the reply ... but that doesnt work either ...

[si_the_geek]

Ok.. what exactly is AddIns(1)?

Is this something specific to your program, or something in Excel?

[Muddy]

Addins is an excel object
Addins(1) is ANALYS32.XLL ... I actually should have posted the code with Addins(2) which is ATPVBAEN.XLA ... but neither work for me. The order might be different on different machines ... I dont know ...

you can run the following code from any Excel Workbook VBA Project to see all the addins available:

Sub ListAddins()
Dim i As Integer
For i = 1 To AddIns.Count
    MsgBox AddIns(i).FullName
Next i
End Sub

Thanks for the responses, btw!

[si_the_geek]

I see, I thought as much..

Any Excel objects you use from VB must be qualified with a parent object (Application, Workbook, etc), or VB doesn't know what they are - sometimes it will use another object in a different object library (such as Word, if it is referenced) with the same name, which can obviously cause a few problems.

I presume you haven't got Option Explicit enabled in your VB code, as this is the sort of issue that it should pick up on when you try to run the program.

After some quick digging I can see that Addins is a child of the Application object, so you should be using code like this:

MsgBox objXL.AddIns(i).FullName

As you have noticed, the Addins may not be in the same order on another machine, so you should use an expanded version of the code in your previous post to find out which one it is, eg:

Private Sub Command1_Click()
Dim objXL As Excel.Application
Dim mySheet As Object
 
Set objXL = New Excel.Application  '(using an extra line for the New makes all code which uses objXL a bit faster)
objXL.Visible = True
 
Dim iCount As Integer               'Find the Addin
Dim iAddin as Integer
iAddin = -1
For iCount = 1 To objXP.AddIns.Count
   If UCase(objXP.AddIns(i).Name) = "ATPVBAEN.XLA"
      iAddin = iCount
      Exit For
   End If
Next iCount
If iAddin = -1 Then   '(warn if couldn't find it)
  MsgBox "Could not load the addin!"
Else
  Set mySheet = objXL.Workbooks.Add.worksheets(1)
  objXL.Workbooks.Open (objXL.AddIns(iAddin).FullName)
  objXL.Run objXL.AddIns(iAddin).FullName & "!Histogram",   mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
End If
 
'(see note below) 
 
Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
objXL.Quit
Set objXL = Nothing
End Sub

Note - I know this is based on what was probably just example code, but you haven't got any code here to close the added Workbook (or the Addin), which could cause Excel to stay open in the background.

[Muddy]

Thank Si ... but I still get the same error ... below is your code with some typos fixed ... but i still get that same error ...

Private Sub Command1_Click()
Dim objXL As Excel.Application
Dim mySheet As Object
 
Set objXL = New Excel.Application  '(using an extra line for the New makes all code which uses objXL a bit faster)
objXL.Visible = True
 
Dim iCount As Integer               'Find the Addin
Dim iAddin As Integer
iAddin = -1
For iCount = 1 To objXL.AddIns.Count
   If UCase(objXL.AddIns(iCount).Name) = "ATPVBAEN.XLA" Then
      iAddin = iCount
      Exit For
   End If
Next iCount
If iAddin = -1 Then   '(warn if couldn't find it)
  MsgBox "Could not load the addin!"
Else
  Set mySheet = objXL.Workbooks.Add.worksheets(1)
  objXL.Workbooks.Open (objXL.AddIns(iAddin).FullName)
  objXL.Run objXL.AddIns(iAddin).FullName & "!Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
End If
 
'(see note below)
 
Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
objXL.Quit
Set objXL = Nothing
End Sub

[dannymking]

Muddy,

You are setting the object mySheet to a new spreadsheet and then attempting to open the addin??

The addin will be already open but not installed if it is found within the addin list.. you will need to install it rather than open it, once loaded you do not need to refer to the fullname of the addin.. just Run "Histogram" followed by parameters will do the job..

Private Sub Command1_Click()
  Dim objXL As Excel.Application
  Dim objAdd as Excel.Addin
  Dim myWb As Excel.Workbook
  Dim mySheet As WorkSheet
 
  Set objXL = New Excel.Application  '(using an extra line for the New makes all code which uses objXL a bit faster)
  objXL.Visible = True
 
'get addin position
  Dim i As Integer
  For i = 1 To objXL.AddIns.Count
    If objXL.AddIns(i).Name = "ATPVBAEN.XLA" Then
        Set objAdd = objXL.AddIns(i)
        Exit For
    End If
  Next i
  If i > objXL.AddIns.Count Then GoTo NotAvailable
  If not objAdd.Installed Then
    objXL.Addins.Add(objAdd.FullName).Installed = True
  End If
  On Error Resume Next
  Set myWb = objXL.Workbooks.Add
  Set mySheet = myWB.Sheets("Sheet1")
  'I'm presuming that there is some data input at this point, other wise this will fail due to no data..
  objXL.Run "Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
End If
 
'(see note below)
 
Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
Set myWb = Nothing
Set objAdd = Nothing
objXL.Quit
Set objXL = Nothing
End Sub

Give that a shot and post back if no joy..

[Muddy]

Thanks for the advice, but I still get the same error ... I corrected some typos in the code you posted (also added some code to write some data into the sheet) and still get the error ...

Private Sub Command1_Click()
  Dim objXL As Excel.Application
  Dim objAdd As Excel.Addin
  Dim myWb As Excel.Workbook
  Dim mySheet As WorkSheet
 
  Set objXL = New Excel.Application  '(using an extra line for the New makes all code which uses objXL a bit faster)
  objXL.Visible = True
 
'get addin position
  Dim i As Integer
  For i = 1 To objXL.Addins.Count
    If objXL.Addins(i).Name = "ATPVBAEN.XLA" Then
        Set objAdd = objXL.Addins(i)
        Exit For
    End If
  Next i
  'If i > objXL.Addins.Count Then GoTo NotAvailable
  If Not objAdd.Installed Then
    objXL.Addins.Add(objAdd.FullName).Installed = True
  End If
  'On Error Resume Next
  Set myWb = objXL.Workbooks.Add
  Set mySheet = myWb.Sheets("Sheet1")
  mySheet.Cells(4, 7).Value = 1
  mySheet.Cells(5, 7).Value = 11
  mySheet.Cells(6, 7).Value = 11
  mySheet.Cells(7, 7).Value = 15
  mySheet.Cells(8, 7).Value = 1
objXL.DisplayAlerts = False
myWb.Save
objXL.DisplayAlerts = True
  'I'm presuming that there is some data input at this point, other wise this will fail due to no data..
  objXL.Run "Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
 
 
'(see note below)
 
Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
Set myWb = Nothing
Set objAdd = Nothing
objXL.Quit
Set objXL = Nothing
End Sub

[Bruce Fox]

Try preceeding "Histogram" with it's parent object (maybe objXL.).

I had posted something along these lines a few days back. Ie calling a presaved Excel Macro from within VB (once the Excel Object was loaded).

Edit: Here is the Link (for what it's worth ):
http://www.vbforums.com/showthread.php?t=354005

[Muddy]

The "parent", I assume, would be ATPVBAEN.XLA ... and Ive tried ATPVBAEN.XLA.Histogram
ATPVBAEN.XLA!Histogram

and even the full path to ATPVBAEN.XLA in the above examples ... nothing works so far

Thanks again to all for the replies, though

[dannymking]

muddy..

I have tested the code that I gave you on a sheet with some data, and it actually created a histogram..

You are switching off the display alerts and then attempting to save a new workbook.. This will prompt for a saveas dialog..

Change:

objXL.DisplayAlerts = False
myWb.Save
objXL.DisplayAlerts = True

To:

objXL.DisplayAlerts = False
myWb.SaveAs "Path and FileName"
objXL.DisplayAlerts = True

Something I missed off from the original code was the error trap.. so uncomment this line If i > objXL.Addins.Count Then GoTo NotAvailable and change the bottom to reflect this..

ExitProc:
  Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
  Set myWb = Nothing
  Set objAdd = Nothing
  objXL.Quit
  Set objXL = Nothing
  Exit Sub
 
NotAvailable:
  Msgbox "The Addin 'APTVBAEN.XLA' Is not present on this machine."
  Resume ExitProc
End Sub

The Addin to be used needs to be present on the machine and also installed through the addin's menu option in Excel.. The Code I gave you checks to make sure that the addin is present and installs it if not installed.. Please Step through the code (F8) and tell which line presents the error.

[Muddy]

OK, here is something interesting. If I open Excel, uncheck all the addins and then run your code it works (with the exception I had to change):

If Not objAdd.Installed Then
    objXL.Addins.Add(objAdd.FullName).Installed = True
  End If

TO:

If Not objAdd.Installed Then
    objAdd.Installed = True
  End If

NOTE that I took out if condition to see if this would work without unchecking the excel addins before running with NO LUCK. I need this to work even if the user already has the addins checked Ijnstalled) when he runs my app. Any ideas?
Could this be an Office 2003 bug? Which version of XL are you running?
Thanks for all the great help!

[Muddy]

OK, strange development, but I did find a fix thanks to all the help here. It turns out that (on my machine at least) I have to programatically uninstall and reinstall the adding before I can access its macros. The two lines that got me going are:

objAdd.Installed = False
    objAdd.Installed = True

The following is the complete snippet that works for me

Private Sub Command1_Click()
  Dim objXL As Excel.Application
  Dim objAdd As Excel.Addin
  Dim myWb As Excel.Workbook
  Dim mySheet As WorkSheet
 
  Set objXL = New Excel.Application  '(using an extra line for the New makes all code which uses objXL a bit faster)
  objXL.Visible = True
 
'get addin position
  Dim i As Integer
  For i = 1 To objXL.Addins.Count
    If objXL.Addins(i).Name = "ATPVBAEN.XLA" Then
        Set objAdd = objXL.Addins(i)
        Exit For
    End If
  Next i
  If i > objXL.Addins.Count Then GoTo NotAvailable
 
    objAdd.Installed = False
    objAdd.Installed = True
 
  Set myWb = objXL.Workbooks.Add
  Set mySheet = myWb.Sheets("Sheet1")
  mySheet.Cells(4, 7).Value = 1
  mySheet.Cells(5, 7).Value = 11
  mySheet.Cells(6, 7).Value = 11
  mySheet.Cells(7, 7).Value = 15
  mySheet.Cells(8, 7).Value = 1
objXL.DisplayAlerts = False
myWb.Save
objXL.DisplayAlerts = True
  'I'm presuming that there is some data input at this point, other wise this will fail due to no data..
  objXL.Run "Histogram", mySheet.Range("$G$4:$G$8"), _
        "Hist", , False, True, True, False
 
 
'(see note below)
ExitProc:
  Set mySheet = Nothing  'slight re-ordering here to help Excel close properly
  Set myWb = Nothing
  Set objAdd = Nothing
  objXL.Quit
  Set objXL = Nothing
  Exit Sub
 
NotAvailable:
  MsgBox "The Addin 'APTVBAEN.XLA' Is not present on this machine."
  Resume ExitProc
 
End Sub

Thanks everyone for all the great help!