Refer to online information, you do the SQL to Excel case, 1, you need to install the MS to install the MS: Pubs 2, the presented C: /TEMP/TEST.XLS (Macro code is written, including 'sheet1' and " People 'Two Sheets 3, perform this SQL, import data into Test.xls 4, open Test.xls, press the button to generate the data of the data SQL: -------------- -------------------------------------------------- -------------------------------------------------- --------- Print 'Begin CreateXLS Script At' RTRIM (Convert (VARCHAR (24), GetDate (), 121)) '' PRINT 'Go
Set Nocount OnDeclare @conn Int - Ado Connection Object To Create XLS, @hr Int - Ole Return Value, @src Varchar (255) - Ole Error Source, @Desc VARCHAR (255) - Ole Error Description, @path VARCHAR (255) - DRIVE or UNC PATH for XLS, @connect varchar (255) - OLE DB Connection String for Jet 4 Excel Isam, @wks_created bit - WHETHER THE XLS WORKSHEET EXISTS, @WKS_NAME VARCHAR (128) - Name of the xls Worksheet (Table), @servername nvarchar (128) - Linked Server Name for xls, @ddl varchar (8000) - jet4 ddl for the xls wks table creation, @sql varchar (8000) - Insert Into XLS T-SQL, @Recs Int - Number of Records Added to XLS, @Log Bit - WHETHER TO LOG Process Detail
- Init variablesselect @Recs = 0 - %%% 1 = verbose output detail, helps find problems, 0 = minimal output detail, @log = 1 - %%% Assign THE UNC OR PATH AND NAME for THE XLS FILE, Requires Read / Write Access - Must Be Accessable from Server Via SQL Server Service Account - & SQL Server Agent Service Account, if ScheduledSet @Path = 'c: /temp/test.xls' - set @Path =' C: /TEMP/TEST_ 'CONVERT (VARCHAR (10), Getdate(), 112) .xls' - Assign The ado connection string for the xls creationSet @connect =' provider = microsoft.jet.Oledb.4.0; data Source = ' @ Path '; Extended Properties = Excel 8.0 '- %%% assign the Linked Server name for the XLS populationSET @ServerName =' EXCEL_TEST '- %%% Rename Table as required, this will also be the XLS Worksheet name @WKS_NAME = 'people - %%% Table Creation DDL, Uses Jet4 Syntax, - Text Data Type = varchar (255) When Accessed from T-SQLSET @ddl =' create Table ' @ WKS_NAME ' (SSN TEXT, NAME TEXT, PHONE TEXT, ZIP NUMERIC '- %%% T-SQL for Table Population, Not e the 4 part naming required by Jet4 OLE DB-- INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported-- Linked Server does not support SELECT INTO typesSET @SQL = 'INSERT INTO' @ ServerName '... ' @ WKS_NAME ' (SSN, Name, Phone, Zip) 'set @SQL = @ SQL ' SELECT AU_ID AS SSN'SET @SQL = @ SQL ', LTRIM (ISNULL (Au_FName,' '') ' '' ' Isnull (au_lname,' ''))))) AS Name'Set @
SQL = @ SQL ', Phone as phone' set @SQL = @ SQL ', zip as zip' set @SQL = @ SQL 'from Pubs.dbo.authors' set @SQL = @ SQL ' Order by Zip 'Print' 1 : ' @ SQL
If @log = 1 print 'created ole adodb.connection Object' - create the conn objectExec @hr = sp_oacreate 'adoDb.connection', @conn outif @hr <> 0 - Have to use <> as ole / ado can Return NEGATIVE ERROR NUMBERSBEGIN - RETURN OLE ERROR EXEC SP_OAGETERRORINFO @Conn, @src out, @Desc out select error = convert (varbinary (4), @ hr), source = @ src, description = @ desc returnend
IF @Log = 1 PRINT char (9) 'Assigned ConnectionString property' - Set a the Conn object's ConnectionString property-- Work-around for error using a variable parameter on the Open methodEXEC @hr = sp_OASetProperty @Conn, 'ConnectionString' , @Connectif @hr <> 0begin - Return Ole Error EXEC SP_OAGETERRORINFO @desc out select error = convert (varbinary (4), @ HR), source = @ src, description = @ Desc returnend
IF @log = 1 Print Char (9) 'Open connection to xls, for fas create or append' - call the open method to create the xls if it does not exist, can't us parametersexec @hr = sp_oamethod @conn , 'Open'IF @HR <> 0begin - Return Ole Error Exec sp_oageterrorinfo @conn, @src out, @Desc out select error = convert (varbinary (4), @ hr), source = @ src, description = @ DESC Returned
- %%% this section could be used for multiple worksheets (Tables) IF @log = 1 print char (9) 'Execute Ddl to create' '' ' @ WKS_NAME ' '' Worksheet '- Call The Execute Method To Create the work sheet with the @WKS_Name caption, - which is also used as a Table reference in T-SQL-- Neat way to define column data types in Excel worksheet-- Sometimes converting to text is the only work-around for Excel's General - Cell Formatting, Even Though The Cell Contains Text, Excel Tries To Format - It in A "Smart" Way, I Have Even Had to use the Single Quote Appended As The "1st Character in T-SQL TO Force) to leave it aloneEXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 - adCmdText adExecuteNoRecords-- 0x80040E14 for table exists in ADOIF @hr = 0x80040E14 - kludge, skip 0x80042732 for ADO Optional parameters (Null) in sql7 or @hr = 0x80042732BEGIN - TRAP THESE OLE ERRORS IF @hr = 0x80040e14 Begin Print Char (9) '' '' @ WKS_NAME '' 'Worksheet Exists for append' Set @WKS_CREATED = 0 end set @hr = 0 - ignore these error eren - Return Ole Error EXEC SP_OAGETERRORINFO @Conn, @src out, @Desc out select error = convert (varbinary (4), @ hr ), Source = @ src, description = @ Desc Returnend
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object' - Destroy the Conn object, important to not leak memory EXEC @hr = sp_OADestroy @ConnIF @hr <> 0BEGIN - Return OLE error EXEC SP_OAGETERRORINFO @Conn, @src out, @Desc out select error = convert (varbinary (4), @ hr), source = @ src, description = @ Desc Returnendprint '2:' - linked server allows t-sql to access the XLS worksheet (Table) - This must be performed after the ADO stuff as the XLS must exist-- and contain the schema for the table, or worksheetIF NOT EXISTS (SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN IF @Log = 1 PRINT 'Created Linked Server' '' @ ServerName '' 'and Login' EXEC sp_addlinkedserver @server = @ServerName, @srvproduct = 'Microsoft Excel Workbook', @provider = 'Microsoft.Jet.OLEDB.4.0 ', @Datasr = @path, @Provstr =' Excel 8.0 '- no login name or password area required to connection connection to the jet4 isam linked server exec sp_addlinkedsrvlogin @servername,' FAL SE 'END
- Have to EXEC THE SQL, OTHERWISE THE SQL IS Evaluated - for the Linked Server Before It ExistSexec (@SQL) Print Char (9) 'Populated' '' @ WKS_NAME '' 'TABLE with' Convert (Varchar , @@ rowcount) 'Rows'
- %%% Optional you may leave the Linked Server for other XLS operations-- Remember that the Linked Server will not create the XLS, so remove it-- When you are done with it, especially if you delete or move the fileIF EXISTS (SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN IF @Log = 1 PRINT 'Deleted Linked Server' '' @ ServerName '' 'and Login' EXEC sp_dropserver @ServerName, 'droplogins'ENDGOSET NOCOUNT OFFPRINT' 'Print' Finished Createxls Script AT ' RTRIM (Convert (VARCHAR (24), Getdate (), 121)) ' 'Go --------------------- -------------------------------------------------- -------------------------------------------------- -------------------------- Excel .sheet1.commandbutton macro code: ---------------- -------------------------------------------------- ----- private subcommandbutton1_click () DIM B_P as boolean b_p = false for i = 1 to sheets.count if sheets (i) .name = "people" THEN B_P = True EXIT for end if next i if b_p = False Then Exit Sub Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source: = Sheets ( "People") Range. ( "B1: D24"), PlotBy _: = xlColumns ActiveChart.SeriesCollection (1) .XValues = "= People R2C2:! R24C2 "ActiveChart.Location Where: = xlLocationAsObject, Name: =" Sheet1 "With ActiveChart .HasTitle = True .ChartTitle.Characters.Text =" Zip ".Axes (xlCategory, xlPrimary) .HasTitle = False .Axes ( XLValue, Xlprimary) .haastitle = false end with
