deyken
02-22-2011, 12:05 AM
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!
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!