Delphi calls Excel macro

Delphi calls Excel macro

In my understanding, macros are functions. As long as I can call VBA functions, I can call Excel macros.
I have been studying how to use Delphi to call Excel macros for a long time. I have found a lot of materials, mostly about how to call excel. There are several methods, but there are very few macros, I will share my materials to save the trouble of having to learn more about this. :
1. Load ActiveX and vbide97 units. The specific path of vbide97 is in C:/program files/Borland/delphi5/OCX/servers.

{Methods to dynamically load macros: V. codemodule. addfromstring
V. codemodule. addfromfile
V. codemodule. insertlines
Some other methods can be found in vbide97.pas.
}
V: _ vbcomponent; Type

I added the addexcelmaro function in the useexcel cell, which can be called directly.

The following is a function of msgboxvba in useexcel. Pas.

Unit useexcel; // The unit for importing data into Excel
{Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx}
Interface
Uses dbgrids, sysutils, stdctrls, extctrls, windows, dialogs, classes, forms, excel97,
Controls, DB, oleserver, ActiveX, vbide97;
{------------------------------------------------------------------------------}
Procedure putoutdata (DBN: tdataset; pxm: TDBGrid );
// Pxm = content displayed by the TDBGrid control, and DBN = TDBGrid control connected to tdataset.
// Output data displayed by pxm to excel
Procedure addexcelmaro ();
{------------------------------------------------------------------------------}
{Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx}
Implementation
VaR exapp: texcelapplication; exlwb: texcelworkbook; exws: texcelworksheet;
{*************************************** ***************************************}
Function createexcel: Boolean;
Begin
Exapp: = texcelapplication. Create (forms. Application); // This is also critical. In vbide, application is also a type, so only forms. can be added before it; otherwise, an error is reported during compilation.
Exapp. connectkind: = cknewinstance;
Exlwb: = texcelworkbook. Create (forms. application );
Exlwb. connectkind: = ckrunningornew;
Exws: = texcelworksheet. Create (forms. application );
Exws. connectkind: = ckrunningornew;
Createexcel: = true;
Try
Exapp. Connect;
Except
Createexcel: = false;
Exapp. Free;
Exlwb. Free;
Exws. Free;
End;
End;
{*************************************** ***************************************}
Function checkprtxm (DBN: tdataset; dbfield: string): Boolean; var X: integer;
Begin
Checkprtxm: = false;
For X: = 0 to DBN. fielddefs. Count-1 do if dbfield = DBN. fielddefs [X]. name then
Begin
Checkprtxm: = true;
Exit;
End;
End;
{*************************************** ***************************************}
Procedure writetoexcel (DBN: tdataset; pxm: TDBGrid); var X, Y, row, column: integer;

Begin
Exapp. Visible [0]: = true;
Exapp. Caption: = 'edit printed data ';
Exapp. workbooks. Add (null, 0 );
Exlwb. connectto (exapp. workbooks [1]);
Exws. connectto (exlwb. worksheets [1] As _ worksheet );
Y: = 1;
For X: = 0 to pxm. Columns. Count-1 do if (pxm. Columns [X]. Visible = true)
And (checkprtxm (DBN, pxm. Columns [X]. fieldname) = true) then
Begin
Exws. cells. item [1, y]: = pxm. Columns [X]. Title. Caption;
Y: = Y + 1;
End;
DBN. Last;
DBN. first;
Row: = 2;
While not DBN. EOF do
Begin
Column: = 1;
For X: = 0 to pxm. Columns. Count-1 do if (pxm. Columns [X]. Visible = true)
And (checkprtxm (DBN, pxm. Columns [X]. fieldname) = true) then
Begin
Exws. cells. item [row, column]: =
DBN. fieldbyname (pxm. Columns [X]. fieldname). asstring;
Column: = column + 1;
End;
DBN. Next;
Row: = row + 1;
End;
End;

Procedure addexcelmaro ();
VaR
V: _ vbcomponent;
Varg1, varg2, varg3, varg4, varg5, varg6, varg7, varg8, varg9, varg10,
Varg11, varg12, varg13, varg14, varg15, varg16, varg17, varg18, varg19, varg20,
Varg21, varg22, varg23, varg24, varg25, varg26, varg27, varg28, varg29, varg30: olevariant;
Begin
Varg1: = emptyparam; varg2: = emptyparam; varg3: = emptyparam; varg4: = emptyparam;
Varg5: = emptyparam; varg6: = emptyparam; varg7: = emptyparam; varg8: = emptyparam;
Varg9: = emptyparam; varg10: = emptyparam; varg11: = emptyparam; varg12: = emptyparam;
Varg13: = emptyparam; varg14: = emptyparam; varg15: = emptyparam; varg16: = emptyparam;
Varg17: = emptyparam; varg18: = emptyparam; varg19: = emptyparam; varg20: = emptyparam;
Varg21: = emptyparam; varg22: = emptyparam; varg23: = emptyparam; varg24: = emptyparam;
Varg25: = emptyparam; varg26: = emptyparam; varg27: = emptyparam; varg28: = emptyparam;
Varg29: = emptyparam; varg30: = emptyparam;
V: = exlwb. vbproject. vbcomponents. Add (toleenum ($00000001 ));
V. Name: = 'module ';
// V. codemodule. addfromstring ('Public sub test () '+ #13 + 'msgbox ("test")' + #13 + 'end sub ');
{Methods to dynamically load macros: V. codemodule. addfromstring
V. codemodule. addfromfile
V. codemodule. insertlines
}
Exapp. Visible [0]: = true;
Exapp. Run ('test', varg1, varg2, varg3, varg4, varg5, varg6, varg7, varg8, varg9, varg10,
Varg11, varg12, varg13, varg14, varg15, varg16, varg17, varg18, varg19, varg20,
Varg21, varg22, varg23, varg24, varg25, varg26, varg27, varg28, varg29, varg30 );

Exapp. Disconnect;
Exapp. Quit;
Exapp. Free;
Exlwb. Free;
Exws. Free;

End;

 

{*************************************** ***************************************}
Procedure putoutdata (DBN: tdataset; pxm: TDBGrid );
Begin
If createexcel = false then showmessage ('excel is not installed in the system') else
Begin
Writetoexcel (DBN, pxm );

End;
End;
{*************************************** ***************************************}
End.