June 13, 2009 at 6:57 pm
Good Morning Sir/Maam
would you kindly guide me in exporting a data result query from sql to a single excel file. my scenario is that i will only be creating an additional sheet if my query will only exceed the row limit of my 2003 excel (65536 rows). i would also like to do it manually or do it by Transact SQL (NO SSIS intervention) if possible. my query result will be extracted from the view i've made so that i can retrieve it much faster ad properly formatted for export. any
help you will extend will be greatly appreciated.
Noel
A.K.A
Stylez
June 14, 2009 at 9:05 am
To start you out on your project may I suggest reading the following: (Note the article includes a sample code download which should get you started)
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
June 15, 2009 at 9:26 am
Instead of doing everything in T-SQL you can create an Excel macro in VBA.
I include a small example to work on.
Sub test
Dim con As New ADODB.Connection 'Set reference to ADO
Dim rs As New ADODB.Recordset
Dim source,key,userid,SQLstatement as string
Dim i,j,norows, nocolumns , rowoffset, columnoffset as long
Dim array as Variant
'Userid the user is promted and maybe validated.
source = "Data source=Name of/IP adr of databaseserver;User ID=XXX;Password=YYY;Initial Catalog=databasename"
'Comment string source or part of I usually save in file not accesable for the user.
'Remote oledb which means that the database and the client Excel can be separated on a company network or Internet
con.Provider = "sqloledb"
con.Properties("Network Library").Value = "DBMSSOCN"
con.Open source
Set rs.ActiveConnection = con
rs.open SQLstatement ' Your SQL question as string
array = rs.GetRows ' Store the result in an array
rs.close
norows = UBound(array, 2) 'Number of rows
nocolumns = UBound(array, 1) 'Number of columns
' Paste the result as values in the sheet the format is already there by design.
Rowoffset = 10 ' Start from row 10 example
columnoffset = 2 ' Start from column 2 example
sheets(“name”).select
For i = 0 To norows
' If i exceeds 65535 plus offset create a new sheet and continue with I etc
For j = 0 To nocolumns
Cells(i + rowoffset, j + columnoffset).Value = variant(j, i)
Next j
Next i
Set rs = Nothing
Set con = Nothing
end sub
As always report your progress to the community
//Gosta
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply