SQL to Excel application

For more information, see the SQL to excel example,
1. First install the MS case database: pubs
2. Pre-existing C:/temp/test.xls (macro code has been written, including two sheets: 'sheet1' and 'others)
32.16execute this SQL statement to import data to test.xls.
4. Open test.xls and press the button to generate a chart of data.

SQL:
Bytes ---------------------------------------------------------------------------------------------------------------------------
Print 'in in createxls script at' + rtrim (convert (varchar (24), 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(10},getdate({,{}}'.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 will 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
-- Insert into select, insert into values, and exec SP types are supported
-- Linked server does not support select into types
Set @ SQL = 'insert' + @ servername + '...' + @ wks_name + '(SSN, name, phone, zip )'
Set @ SQL = @ SQL + 'select au_id as ssn'
Set @ SQL = @ SQL + ', ltrim (rtrim (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 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 properties'
-- Set a conn object's connectionstring Property
-- Work-around und for error using a variable parameter on the Open Method
Exec @ hR = sp_oasetproperty @ conn, 'ononstring', @ 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 cocould be repeated 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
-- 1st character in T-SQL to force EXCEL to leave it alone
Exec @ hR = sp_oamethod @ conn, 'execute ', null, @ DDL, null, 129 -- ad1_text + 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 'deststroyed 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 be completed MED 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_addmediaserver @ 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_add1_srvlogin @ servername, 'false'
End

-- Have to Exec the 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 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 file
If exists (select srvname from Master. DBO. sysservers where srvname = @ servername)
Begin
If @ log = 1 print 'ed ed linked server ''' + @ servername + ''' and login'
Exec sp_dropserver @ servername, 'droplogins'
End
Go

Set nocount off
Print''
Print 'finished createxls script at' + rtrim (convert (varchar (24), getdate (), 121) +''
Go
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------

Excel. sheet1.commandbutton macro code:
-----------------------------------------------------------------------
Private sub commandbutton#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
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

End sub