SQL to Excel Application

Excel
Refer to the online data, do your own SQL to Excel case,
1, need to install the case database of MS first: pubs
2, pre-existing C:\temp\test.xls (macro code has been written, including ' Sheet1 ' and ' people ' two sheet)
3, the implementation of this SQL, data can be imported into the Test.xls
4, open the Test.xls, press the button, can produce the data chart




Sql:
--------------------------------------------------------------------------------------------------------------- ------------
PRINT ' Begin createxls script at ' +rtrim (CONVERT (varchar, GETDATE (), 121)) + '
PRINT '
Go

SET NOCOUNT on
DECLARE @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 variables
SELECT @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 scheduled
SET @Path = ' C:\TEMP\Test.xls '
--set @Path = ' C:\TEMP\Test_ ' +convert (varchar), GETDATE (), 112) + '. xls '
--Assign the ADO connection string for the XLS creation
SET @Connect = ' Provider=Microsoft.Jet.OLEDB.4.0;Data source= ' + @Path + '; Extended Properties=excel 8.0 '
--%%% Assign the linked Server name for the XLS population
SET @ServerName = ' excel_test '
--%%% Rename Table as required, this would also be the XLS worksheet name
SET @WKS_Name = ' People '
--%%% Table creation DDL, uses JET4 syntax,
---Text data type = varchar (255) when accessed from T-SQL
SET @DDL = ' CREATE TABLE ' + @WKS_Name + ' (SSN text, Name text, Phone text, Zip numeric) '
--%%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
--Inserts into SELECT, inserts into VALUES, and EXEC SP types are supported
--linked Server does not support SELECT into types
SET @SQL = ' INSERT into ' + @ServerName + ' ... ' + @WKS_Name + ' (SSN, Name, Phone, Zip) '
SET @SQL = @SQL + ' SELECT au_id as SSN '
SET @SQL = @SQL + ', LTRIM (RTRIM (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 object
EXEC @hr = sp_OACreate ' ADODB. Connection ', @Conn out
IF @hr <> 0--have to use <> as Ole/ado can return negative error numbers
BEGIN
--Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src out, @desc out
SELECT Error=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End

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 in the Open method
EXEC @hr = sp_OASetProperty @Conn, ' ConnectionString ', @Connect
IF @hr <> 0
BEGIN
--Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src out, @desc out
SELECT Error=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End

IF @Log = 1 PRINT char (9) + ' Open Connection to XLS, for file Create or Append '
--Call the Open method to create the XLS if it does not exist, can ' t use parameters
EXEC @hr = sp_OAMethod @Conn, ' Open '
IF @hr <> 0
BEGIN
--Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src out, @desc out
SELECT Error=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End

--%%% This section could is repeated for multiple worksheets (Tables)
IF @Log = 1 PRINT char (9) + ' Execute DDL to create ' + @WKS_Name + ' worksheet '
--called 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
--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 Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, ' Execute ', NULL, @DDL, NULL, 129--adCmdText + adExecuteNoRecords
--0x80040e14 for table exists in ADO
IF @hr = 0x80040e14
--Kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
OR @hr = 0x80042732
BEGIN
--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 errors
End
IF @hr <> 0
BEGIN
--Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src out, @desc out
SELECT Error=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End

IF @Log = 1 PRINT ' destroyed OLE ADODB. Connection object '
--Destroy The Conn object, +++ important to not leak memory
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
--Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src out, @desc out
SELECT Error=convert (varbinary (4), @hr), source= @src, description= @desc
Return
End

print ' 2: '
--linked Server allows T-SQL to access the XLS worksheet (Table)
--This must is performed after the ADO stuff as the XLS must exist
--and contain the schema for the table, or worksheet
IF 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 '
, @datasrc = @Path
, @provstr = ' Excel 8.0 '
--No login name or password are required to connect to the JET4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, ' false '
End

--Have to EXEC-SQL, otherwise the SQL is evaluated
--for the linked server before it exists
EXEC (@SQL)
PRINT Char (9) + ' populated ' + @WKS_Name + ' ' Table with ' +convert (varchar,@ @ROWCOUNT) + ' Rows '

--%%% Optional you leave the linked Server for other XLS operations
--Remember that the linked Server won't create the XLS, so remove it
--When you are do with it, especially if your delete or move the file
IF 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 '
End
Go

SET NOCOUNT off
PRINT '
PRINT ' finished Createxls script at ' +rtrim (CONVERT (varchar, GETDATE (), 121)) + '
Go
--------------------------------------------------------------------------------------------------------------- ------------------------------------





Excel. Sheet1.commandbutton macro code:
-----------------------------------------------------------------------
Private Sub CommandButton1_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). HasTitle = False
End With

End Sub
----------------------------------------------------------------------------------