1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComCtrls, OleCtnrs, DBGrids, Menus, ComObj,
Grids, ShellApi,DBCtrls, CheckLst, dblookup, FileCtrl, math, db, ExtCtrls;
...
procedure TForm28.Button5Click(Sender: TObject);
begin
vMSExcel := CreateOleObject('Excel.Application');
vMSExcel.visible := false;
vMSExcel.Application.DisplayAlerts := false;
vXLWorkbooks := vMSExcel.Workbooks;
vXLWorkbook := vXLWorkbooks.Open('...');
vXLWorkbook.save;
vMSExcel.run('Macro1');
VMSExcel.visible := true;
end;
Code appelé sous vba :
Sub Macro1()
Dim chemin as String
chemin = "..."
Sheets("Feuil1").Select
Sheets("Feuil1").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=dBASE Files;DefaultDir=;DriverId=277;FIL=dBASE IV;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT FACTU.IDMISSION, FACTU.TYPE, FACTU.JANVIER, FACTU.FEVRIER, FACTU.MARS, FACTU.AVRIL, FACTU.MAI, FACTU.JUIN, FACTU.JUILLET, FACTU.AOUT, FACTU.SEPTEMBRE, FACTU.OCTOBRE, FACTU.NOVEMBRE, FACTU.DECEM" _
, _
"BRE, FACTU.AN_ACOMPTE, FACTU.MOIS_SE, FACTU.CODECLIENT, FACTU.DESIGNA_C" & Chr(13) & "" & Chr(10) & "FROM `"& chemin & "`\FACTU.DBF FACTU" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.PreserveFormatting = True
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"Tableau_Lancer_la_requête_à_partir_de_dBASE_Files5"
.Refresh BackgroundQuery:=False
End With
End Sub |
Partager