Calling a procedure from another workbook

[mccar75287]

Here's my problem.
I have an Excel XLA which is installed on multiple workstations.
The XLA contains VBA code to create a new workbook and import a report text
file into it and format it nicely.

It then adds a custom commandbutton into the open worksheet on the new
workbook and creates a new sub procedure for the on click event of the
command button using VBA code shown here.

Sub ModifyCommandButton1()


Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub CommandButton1_Click()" & LF

'Your Procedure
Proc = "Call FlexTools.modInsertIntoMatstats.ProcessReport" & LF

'Use activeWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With ModEvent
LineNum = .CountOfLines + 1
..InsertLines LineNum, SubName & Proc & EndS
End With
End Sub




My new workbook ends up with this code in the onclick botton code.
But when I click the button it cannot find my VBA procedure called
ProcessReport which exists in the VBA Addin.

Iv'e found many links on how to call a procedure from another workbook
including the microsoft solution
below but the problem I have is that the workbook is created on the fly so I
cannot create a reference from my workbook to my XLA Addin.


XL2000: How to Use a Custom Function in Another Workbook
http://support.microsoft.com/kb/213645

Is is possoble to create this reference through VBA? If not does anyone have
any idea on how my new workbook can use code in the Addin?

Regards,
Jerry

 

[Chip Pearson]

Try something like

Sub AAA()
Dim WB As Workbook
Dim WS As Worksheet
Dim OLEObj As OLEObject

Dim CodeString As String

Set WB = Application.Workbooks.Add
CodeString = "MsgBox" & Chr(34) & "hello world" & Chr(34)

Set WS = WB.Worksheets(1)
Set OLEObj = WS.OLEObjects.Add("Forms.CommandButton.1")
OLEObj.Name = "MyButton"
' OR
Set OLEObj = WS.OLEObjects("MyExistingOLEButton")

With WB.VBProject.VBComponents(WS.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", "MyButton") + 1, CodeString
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Bob Phillips]

Have you tried Application.Run?

Application.Run "myAddin.xla!myFunction"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mccar75287]

Hi Chip,
I've tried the example you gave me but perhaps my request was not clear
enough.
The example you gave me executes code that is placed on the on click event
of the new button. In your example a messagebox. What I am trying to
accomplish is to execute code contained in my XLA by pushing that button.

The code I gave below already does what your example does in that it
populates the code event for the button. This I already have working.
The issue is that it reads like "Call
FlexTools.modInsertIntoMatstats.ProcessReport" where FlexTools is my project
name (XLA) , modInsertIntoMatstats is the module name within the XLA and
ProcessReport is the procedure.

It seems the newly created workbook cannot find my XLA procedure.

 

[mccar75287]

Hi Bob,
I've also tried that using "Application.Run "FlexTools.xla!ProcessReport"
and it doesnt work either. It reports Runtime error 424 Object Required.