Bonjour,
Je cherche à générer via DTS autant de fichiers excel qu'il y a de fournisseurs dans ma table Sql server 2000. Ainsi chaque fournisseur aura son fichier propre qui pourra être envoyé par mail.
Une idée?
Version imprimable
Bonjour,
Je cherche à générer via DTS autant de fichiers excel qu'il y a de fournisseurs dans ma table Sql server 2000. Ainsi chaque fournisseur aura son fichier propre qui pourra être envoyé par mail.
Une idée?
Chouette, je fais les questions et les réponses!
J'ai trouvé du code que j'ai adapté. Pas complètement élégant car j'ai mis en dur les colonnes qui m'intéressent mais en tout cas ça marche. la fonction me génère un fichier excel dont le nom est en paramètre par copie d'un fichier modèle (permet de préparer des mises en forme) et m'insère les enregistrements correspondant à une requête passée également en paramètre.
Create proc sp_Export_Excel (@fileName varchar(100),
@NumOfColumns tinyint,
@query varchar(200))
as
begin
declare @dosStmt varchar(200)
declare @tsqlStmt varchar(500)
declare @colList varchar(200)
declare @charInd tinyint
declare @rmtsrvname varchar(100)
declare @useself varchar(100)
declare @locallogin varchar(100)
declare @rmtuser varchar(100)
declare @rmtpassword varchar(100)
declare @NomServ varchar(100)
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
-- set @charInd=0
-- set @colList = 'A'
-- while @charInd < @NumOfColumns - 1
-- begin
-- set @charInd = @charInd + 1
-- set @colList = @colList + ',' + char(65 + @charInd)
-- end
set @colList = 'article,"Libellé Article",émission,cde,RAL,"à quai",besoin,confirm,R,"4S",">S4",cad,mag,Montt,ctrl,transit,statut,pda'
print @fileName
-- Create an Empty Excel file as the target file name by copying the template Empty excel File
set @dosStmt = ' copy c:\temp\manquants\vide.xls ' + @fileName
exec master..xp_cmdshell @dosStmt
-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@fileName,
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin
@rmtsrvname ='ExcelSource',
@useself = false,
@locallogin =NULL,
@rmtuser ='ADMIN',
@rmtpassword =NULL;
set @NomServ ='ExcelSource';
-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[Feuil1$] ' + ' ( ' + @colList + ' ) '+ @query
print @tsqlStmt
-- execute dynamically the TSQL statement
exec (@tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource', 'droplogins'
set nocount off
end
GO
Salut,
Je continue de me répondre à moi-même... La procédure permet de copier un fichier modèle (vide.xls) contenant une macro qui met en forme la feuille excel.
CREATE PROCEDURE [sp_ExportToExcel] (
@SourceServer VARCHAR(30),
@SourceUID VARCHAR(30)=NULL,
@SourcePWD VARCHAR(30)=NULL,
@QueryText VARCHAR(500),
@filename VARCHAR(100),
@WorksheetName VARCHAR(100),
@RangeName VARCHAR(80),
@Macro VARCHAR(80))
AS
DECLARE @objServer INT,
@objQueryResults INT,
@objCurrentResultSet INT,
@objExcel INT,
@objWorkBooks INT,
@objWorkBook INT,
@objWorkSheet INT,
@objRange INT,
@hr INT,
@Columns INT,
@Rows INT,
@Output INT,
@currentColumn INT,
@currentRow INT,
@ResultSetRow INT,
@off_Column INT,
@off_Row INT,
@command VARCHAR(500),
@ColumnName VARCHAR(500),
@value VARCHAR(255),
@strErrorMessage VARCHAR(500),
@objErrorObject INT,
@Alphabet VARCHAR(27),
@dosStmt varchar(200)
SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
IF @QueryText IS NULL
BEGIN
RAISERROR ('A query string is required for spDMOExportToExcel',16,1)
RETURN 1
END
-- Sets the server to the local server
IF @SourceServer IS NULL SELECT @SourceServer = @@servername
-- SET NOCOUNT ON
set @dosStmt = ' copy c:\temp\manquants\vide.xls ' + @filename
exec master..xp_cmdshell @dosStmt
SELECT @strErrorMessage = 'instantiating the DMO',
@objErrorObject=@objServer
EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
IF @SourcePWD IS NULL OR @SourceUID IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @strErrorMessage=
'Setting login to windows authentication on '
+@SourceServer, @objErrorObject=@objServer
IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1
IF @hr=0 SELECT @strErrorMessage=
'logging in to the requested server using windows authentication on '
+@SourceServer
IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,
'Connect', NULL, @SourceServer
IF @SourceUID IS NOT NULL AND @hr=0
EXEC @hr=sp_OAMethod
@objServer, 'Connect', NULL, @SourceServer ,@SourceUID
END
ELSE
BEGIN
IF @hr=0
SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
''' with user ID '''+@SourceUID+'''',
@objErrorObject=@objServer
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL,
@SourceServer, @SourceUID, @SourcePWD
END
--now we execute the query
IF @hr=0 SELECT @strErrorMessage='executing the query "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objServer,
@command = 'ExecuteWithResults("' + @QueryText + '")'
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT
IF @hr=0
SELECT @strErrorMessage='getting the first result set for "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults
IF @hr=0 EXEC @hr=sp_OAMethod
@objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
IF @hr=0
SELECT @strErrorMessage='getting the rows and columns "'
+@querytext+'", on '+@SourceServer
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT
--so now we have the queryresults. We start up Excel
IF @hr=0
SELECT @strErrorMessage='la création de l''application Excel '
+@SourceServer, @objErrorObject=@objExcel
IF @hr=0
EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT
IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object '
--IF @hr=0
-- EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks', @objWorkBooks OUT
-- open the file to be processed
IF @hr=0
SELECT @strErrorMessage='opening the workbook "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults
IF @hr=0
EXEC @hr = sp_OAMethod @objExcel, 'WorkBooks.Open', @objWorkBook OUT,@filename
SELECT @WorksheetName='WorkSheets("' + @WorkSheetName + '")'
EXEC @hr = sp_oaGetProperty @objWorkBook,@WorkSheetName, @objWorkSheet out
--format the headings in Bold nicely
--IF @hr=0
-- SELECT @strErrorMessage='formatting the column headings in bold ',
-- @objErrorObject=@objWorkSheet,
-- @command='Range("A1:Z1").font.bold'
--IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1
--now we write out the data
SELECT @currentRow = 2
WHILE (@currentRow <= @Rows+1 AND @hr=0)
BEGIN
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT
@strErrorMessage=
'getting the value from the query string'
+ LTRIM(STR(@currentRow)) +','
+ LTRIM(STR(@currentRow))+')',
@objErrorObject=@objQueryResults,
@ResultSetRow=@CurrentRow-1
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString',
@value OUT, @ResultSetRow, @currentColumn
IF @hr=0
SELECT @strErrorMessage=
'assigning the value from the query string'
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn))+')' ,
@objErrorObject=@objExcel,
@command='Cells('+STR(@currentRow) +', '
+ STR(@CurrentColumn)+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @value
SELECT @currentColumn = @currentColumn + 1
END
SELECT @currentRow = @currentRow + 1
END
IF @Macro IS NOT NULL
BEGIN
SELECT @command = 'Run '+ @Macro
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
END
IF @hr=0
SELECT @strErrorMessage='Saving the workbook"'+@filename+'"',
@objErrorObject=@objRange,
@command = 'Save'
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
IF @hr=0 SELECT @strErrorMessage='closing Excel ',
@objErrorObject=@objExcel
EXEC @hr=sp_OAMethod @objWorkBook, 'Close'
EXEC sp_OAMethod @objExcel, 'Close'
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,
@Helpfile output,@HelpID output
SELECT @hr, @source, @Description,@Helpfile,@HelpID output
SELECT @strErrorMessage='Erreur pendant '
+COALESCE(@strErrorMessage,'doing something')
+', '+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkBooks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
RETURN @hr
GO
On fait l'appel de la façon suivante:
declare @four varchar(6),
@QueryTexttmp varchar(500)
set @four='000200'
set @QueryTexttmp = 'select nom, prénom from essaifm'
EXEC sp_ExportToExcel @SourceServer=NULL,
@SourceUID= 'ADMIN',
@SourcePWD = NULL,
@QueryText = @QueryTexttmp,
@filename = 'C:\temp\manquants\000300.xls',
@WorksheetName='A_Manquant',
@RangeName =NULL,
@Macro='"MAJ"'
Le seul hic, c'est que la macro ne s'exécute pas, ou plutôt je ne récupère jamais la main dans l'analyseur de requête. La macro fait :
Sub MAJ()
'
' MAJ Macro
' Macro enregistrée le 29/06/2007 par Franck MOLMY
'
'
Rows("A1:Q1").Select
Selection.Font.Bold = True
End Sub
Quelqu'un a une idée?