How to pass parameters from Delphi to an Excel Macro function?

[deyken]

Hi Guys,

I have a Macro in MS Excel 2007 (backward compatible with Excel 2003) that simply takes a input parameter (Integer) with which it opens a ADODB Connection, runs a Query against a Table (with the aid of this param) and returns either 1 or 0 Results.

The Macro works fine in Excel, BUT, what I need to do is call this very macro from an application I wrote in Delphi (on ADO - SQL Server). My question seems simple: How can I pass a parameter (variant data type?) to a Macro function call?

My Delphi Code looks like this:

procedure TfrmBOM2.sButton7Click(Sender: TObject);
var
LCID : Integer;
XLFile : TFileName;
WB : OleVariant;
WS : Worksheets;
Range : OleVariant;
BillNo : Integer;
begin
LCID := GetUserDefaultLCID;
XLFile := 'MAIN BOM TEMPLATE Vs. 1.1.xls';
BillNo := DM1.BOMm.Fields[0].AsInteger; //Master Record Number to go to Excel

with ExcelApplication1 do
begin
connect;
try
visible[LCID] := true;
Run(XLFile + '!Button807_Click()', BillNo);
finally
//Disconnect;
end;
end;
end;

Above:
- Billno is an Integer variable assigned at Run-time in my Delphi App
- Button807_Click() is the Excel Macro.

Button807_Click() Looks like this inside my VBA:

Sub Button807_Click(BillNo As Integer) ' IMPORT A BILL OF MATERIALS FROM THE ENGINEERING DATABASE, ID BY BOM_NO
' Import the Bill of Materials
Dim DB As New ADODB.Connection
Dim BOMm As New ADODB.Recordset
Dim BOMh As New ADODB.Recordset
Dim BOMd As New ADODB.Recordset
Dim BomNo As Integer
Dim BLQ As String
Dim RFQ As String



If BillNo = 0 Then
BomNo = Sheet2.Cells(1, "Y")
Else
BomNo = BillNo
Cells(2, "Y") = "Received " & BillNo & " from External Application"
End If

DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=DEVELOPER-PC;Database=MainDB"
BOMm.Open "SELECT * from tblBOM_Master WHERE BOM_NO = '" & BomNo & "'", DB, adOpenDynamic, adLockOptimistic
BLQ = BOMm.Fields("BLQ_NO").Value
RFQ = BOMm.Fields("RFQ_TITLE").Value
MsgBox "BLQ Number: " & BLQ & " [" & RFQ & "] was loaded successfully for Bill of Materials number: " & BomNo, vbInformation

'If BOMm.RecordCount <= 0 Then
' MsgBox "No Bills of Materials were found for your Criteria: " & BomNo
'End If
End Sub

SO: My DELPHI variable (Billno : Integer) has to pass to my Excel Macro parameter (BillNo As Integer). How do I do this right?

Looking forward to any assitance!

[Jan Karel Pieterse]

Where is this buttonclick macro located? you can only use application.run to call macros in Excel which are located in a normal module.

[deyken]

Hi Jan,

My module, containing this Button_Click() function resides within the workbook modules, not within the actual worksheet.

Is that incorrect? Should I move it?

Looking forward to your reply.

[Jan Karel Pieterse]

If it is in a module in the Modules category of the VBA project (the small window on the lefthand side of the VBA editor) that is OK. Anywhere else won't work.

[Kenneth Hobs]

Here is a simple example.

1. In TestRun.xlsm's Module1:
Sub Button807_Click(BillNo As Integer)
MsgBox BillNo
End Sub
2. In the test workbook, it will open the TestRun.xlsm if it is not open already when it runs. The Module1 need not be added if you don't have any conflicts with Sub names.
Sub Test_Button807_Click()
Application.Run "'" & ThisWorkbook.Path & "\RunExample.xlsm'!Module1.Button807_Click", 3
End Sub

Note the use of single quotes to handle space characters.