Histogram macro / atpvbaen cannot be found

C

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
I have following macro in Excel 2003:

Sub Macro102()
Workbooks.Open Filename:= _
"C:\Test Histogram.xls"
Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$A$10"), _
ActiveSheet.Range("$C$1"), , False, False, True, False
End Sub

When running this macro manually from XL works fine. Now I have a script designed for automation purposes:

'Open an instance of Excel
Set objExcel=CreateObject("Excel.Application")
objExcel.Visible=True
'Open PERSONAL.XLS file
Set objWorkbook=objExcel.Workbooks.Open("C:\Documents and Settings\ctimot\Application Data\Microsoft\" & _
"Excel\XLSTART\PERSONAL.XLS")
'Call macro
objExcel.Run "PERSONAL.XLS!Macro102"
'Close the instance of Excel
objExcel.Quit
Set objExcel=Nothing

The issue is that this scripts stops at the following line from Macro102:

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$A$10"), _
ActiveSheet.Range("$C$1"), , False, False, True, False

with the following error:

Run time error '1004':
The macro " cannot be found.

What could be wrong here? I have atpvbaen listed in VBA Project as atpvbaen.xls (ATPVBAEN.XLA).
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
When Excel is started through the UI add-ins are loaded automatically.

When Excel is started through automation they are not. You will have to do so through code. A major PITA but that's the way it is.
 
Upvote 0
I tried to find out ways to load references programatically. I found this code to load references:

Sub LoadExcelReferences()

'load Excel object library
'When using AddFromGUID, you can use zero for the major
'and minor versions to pick the latest.
on Error Resume Next
'adding VBE object library:
ActiveWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 0, 0
'Adding excel object library:
Application.VBE.ActiveVBProject.References _
.AddFromGuid "{ 00020813-0000-0000-C000-000000000046}", 0, 0
On Error Goto 0

End Sub

Not sure if the Guids are accurate or relevant for my job. When ran the code after inserting the above sub I still stumbled at the same line of code. Could you suggest something different? Thank you.
 
Upvote 0
Why are you trying to load references to the VBE? You want the load the *Excel* addins.
Use the macro recorder to find out how to load the required add-ins? I've never used a GUID to load an add-in.
 
Upvote 0
I tried already the line of code captured from macro recording inserted in my macro:

Sub Macro102()
Workbooks.Open Filename:= "C:\Test Histogram.xls"
AddIns(“Analysis ToolPak – VBA”).Installed=True
Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A$1:$A$10"), _
ActiveSheet.Range("$C$1"), , False, False, True, False
End Sub

but makes no difference I get the same error.
 
Upvote 0
Hi,

See description of how-to tricks in the comments of code below:
Rich (BB code): 
<font face=Courier New>
' ZVI:2009-06-15 http://www.mrexcel.com/forum/showthread.php?t=396285
Sub ExcelAutomationWithAddIn()
  
  ' Open an instance of Excel
  With CreateObject("Excel.Application")
    
    ' Trick1: load ATP-VBA AddIn using .Workbooks.Open method
    .Workbooks.Open .Path & "\Library\Analysis\ATPVBAEN.XLA"
    
    ' Trick2: initialize AddIn to register its functions
    .Run "ATPVBAEN.XLA!Auto_Open"
    
    ' Load PERSONAL.xls file
    .Workbooks.Open (Environ("APPDATA") & "\Microsoft\Excel\XLSTART\PERSONAL.XLS")
    
    ' Execute Sub Macro102() of PERSONAL.xls workbook
    .Run "PERSONAL.XLS!Macro102"
    
    ' Do visible the instance of Excel (for debug?)
    .Visible = True
    
    ' Close the instance of Excel
    .Quit
    
  End With

End Sub
</FONT>

Code of the macro in Personal.xls
Rich (BB code): 
<font face=Courier New>
' Code in PERSONAL.XLS
Sub Macro102()
  With Workbooks.Open("C:\Test Histogram.xls")
    Application.Run "ATPVBAEN.XLA!Histogram", Range("$A$1:$A$10"), Range("$C$1"), , False, False, True, False
    ' Uncomment line below to auto saving of Histogram.xls
    '.Close SaveChanges:=True
  End With
End Sub</FONT>

Regards,
Vladimir
 
Upvote 0
Thank you Vladimir, it worked fine except the fact that for whatever reason got a message at the end 'Cannot close Microsof Excel'. Probably is connected with the fact that the first module I ran it as a VB script. I have to look into it for potential causes.
 
Upvote 0
Corni said:
Thank you Vladimir, it worked fine except the fact that for whatever reason got a message at the end 'Cannot close Microsof Excel'. Probably is connected with the fact that the first module I ran it as a VB script. I have to look into it for potential causes.
Click to expand...
Code for VBScript should be as follows:
Rich (BB code): 
<font face=Courier New>
' VBScript - copy all code to your *.VBS file
Call ExcelAutomationWithAddIn

' ZVI:2009-06-16 http://www.mrexcel.com/forum/showthread.php?t=396285
Sub ExcelAutomationWithAddIn()

  ' Workbook variable, %APPDATA% enviromnent variable
  Dim Wb, appdata

  ' Ignore errors
  On Error Resume Next

  ' Get APPDATA environment variable
  With CreateObject("WScript.Shell")
    appdata = .ExpandEnvironmentStrings("%APPDATA%")
  End With

  ' Open an instance of Excel
  With CreateObject("Excel.Application")

    ' Trick1: load ATP-VBA AddIn using .Workbooks.Open method
    .Workbooks.Open .Path & "\Library\Analysis\ATPVBAEN.XLA"

    ' Trick2: initialize AddIn to register its functions
    .Run "ATPVBAEN.XLA!Auto_Open"

    ' Load PERSONAL.xls file
    Set Wb = .Workbooks.Open(appdata & "\Microsoft\Excel\XLSTART\PERSONAL.XLS")

    ' Execute Sub Macro102() of PERSONAL.xls workbook
    .Run "PERSONAL.XLS!Macro102"

    ' Do visible the instance of Excel (for debug)
    .Visible = True

    ' Close workbook for correct closing of Excel instance
    Wb.Close False

    ' Close the instance of Excel
    .Quit

  End With

End Sub
</FONT>

Regards,
Vladimir
 
Last edited:
Upvote 0
May be Wb.Close True is more correct for saving
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
444
kpost
K
S
  • Solved
Miscrosoft has Blocking The Macro Sometimes and disabling the macros
Replies
5
Views
447
NdNoviceHlp
NdNoviceHlp
B
  • Question
Excel macro pre 2016
2 3
Replies
26
Views
1K
Joe4
Joe4
E
  • Question
Macro to Pull Summary from Multiple Files
Replies
2
Views
288
dreid1011
dreid1011
B
  • Question
Macro to Assign "UniqueID's" to Judges tables for a competition
Replies
1
Views
130
BeerMan23
B

Forum statistics

Threads
1,215,963
Messages
6,127,959
Members
449,412
Latest member
montand

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top