SQLServerCentral / Discuss Content Posted by David Ziffer / Article Discussions / Article Discussions by Author / Build Great Looking Excel Spreadsheets / Latest Posts

RE: Build Great Looking Excel Spreadsheets

Clarie-457179 — Thu, 06 Nov 2008 06:46:18 GMT

Like others before me, I've always used export tools to get the data to Excel. However, the formatting of the spreadsheet for alternate colors makes me *almost* wish that I knew more about VB. Execllent on formatting! ~Clarie

RE: Build Great Looking Excel Spreadsheets

Gosta Munktell — Wed, 05 Nov 2008 12:32:19 GMT

Thank you David JIt seems we are thinking at the same direction.You can do it in VBA and VB script. In fact some time I development and testsin VBA (testing and editing macros) and then translate the result to VB script. But so far we have been talking about formating. If you need more complicated actions dependingon actual data like subsums filtering linking to other sheets etc I stay with ADO and VBA because it works. And it is really fashinating what you can do with Excel. I have for a client made a "customer simulator". This workbook is loaded with 6 different queries (in different sheets) from the data ware house and there is a lot of logic inside. A salesman can key in a prospect and simulate different situations to calculate the assumed net margin.:) Gosta

RE: Build Great Looking Excel Spreadsheets

pileofmush — Wed, 05 Nov 2008 12:05:33 GMT

When I need to grab column names to paste into Excel, I just push the "Results to Text" button, run the query, copy the column names and paste them into Excel, then switch back to "Results to Grid" and rerun the query. It works for anyone who only infrequently needs to copy column names.

RE: Build Great Looking Excel Spreadsheets

David Jackson — Wed, 05 Nov 2008 09:43:00 GMT

[quote][b]divison (11/5/2008)[/b][hr]Thanks, David J. -- that'll do it! :)...Thanks again...[/quote]No problem :DHere is another handy macro that demonstrates formatting, and tells you the numbers Excel uses for colours as a bonus[code]Sub colors56()'57 colors, 0 to 56 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManualDim i As LongDim str0 As String, str As StringFor i = 0 To 56 Cells(i + 1, 1).Interior.ColorIndex = i Cells(i + 1, 1).Value = "[Color " & i & "]" Cells(i + 1, 2).Font.ColorIndex = i Cells(i + 1, 2).Value = "[Color " & i & "]" str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6) 'Excel shows nibbles in reverse order so make it as RGB str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2) 'generating 2 columns in the HTML table Cells(i + 1, 3) = "#" & str & "" '& "#" & str & "" Cells(i + 1, 3).Font.Name = "Courier" Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)" Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)" Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)" Cells(i + 1, 7) = "[Color " & i & "]"Next idone: Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic Application.ScreenUpdating = TrueEnd Sub[/code]This is a macro to be run in excel, try it in a new workbook. All credit to http://www.mvps.org/dmcritchie/excel/colors.htm ;)Dave J

RE: Build Great Looking Excel Spreadsheets

Abbs — Wed, 05 Nov 2008 09:35:37 GMT

[quote][b]corneld (11/5/2008)[/b][hr]I concur with: :hehe:SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'Otherwise, good effort and nice article!!![/quote]Indeed - you'd think this would be turned on by default, along with word wrap and display line numbers.Good article - I've seen a similar Excel macro solution for Oracle, so I can see this has its applications.

RE: Build Great Looking Excel Spreadsheets

divison — Wed, 05 Nov 2008 09:22:11 GMT

Thanks, David J. -- that'll do it! :)As icing on the cake, starting from what you gave, I may prefer to let an export task do most of the work of creating the spreadsheet, and just use ADO in VBScript for: objExcel.Range("A1:AD1").Font.Bold = TRUE objExcel.Range("A1:AD1").Interior.ColorIndex = 6 If you have comments, they are appreciated.Thanks again...

RE: Build Great Looking Excel Spreadsheets

Anirban Paul — Wed, 05 Nov 2008 09:16:37 GMT

Interesting article...

RE: Build Great Looking Excel Spreadsheets

peter.graney — Wed, 05 Nov 2008 09:06:12 GMT

David,Actually there is a way to get the column names from the grid view in Sql Server Management Studio.Goto:Tools|Options|+Query Results +SQL Server Results to Grid Check the checkbox (Include column headers when copying or saving the results)Peter

RE: Build Great Looking Excel Spreadsheets

Jerry Hung — Wed, 05 Nov 2008 08:52:28 GMT

In SSMS 2008 query results window, you can right-click and choose "Copy with headers"(Copy, Copy with Headers, Select All)if you didn't set the Option settings many mentioned aboveCopy with Headers[quote]FirstName MiddleName LastName285 E Abbas293 R. Abel[/quote]

RE: Build Great Looking Excel Spreadsheets

David Jackson — Wed, 05 Nov 2008 08:42:07 GMT

[quote][b]divison (11/5/2008)[/b][hr]The article was interesting, and the conversation it spawned:...And I can see it would be useful, for me, to be able to do this kind of Excel formatting from ADO/VBScript... ... so, Gosta, does your article include Excel *formatting* from VBScript? If so, by all means please share the link here, if & when your article is published.Thanks, alll...[/quote]Here is a VBS Script that does exactly that.Oh, and another tweak to the original macro. If you change [code]Set S = ThisWorkbook.ActiveSheet[/code]to [code]Set S = ActiveSheet[/code]you can store it in your PERSONAL.xls and use it in any workbook without pasting the code in everytime.Here's the afore mentioned (and afore posted, albeit in a different thread) VB script[code]Dim connectionString'The connection string goes hereconnectionString = "Provider = SQLOLEDB;Data Source=SERVER_NAME;" & _ "Trusted_Connection=Yes;Initial Catalog=Northwind;" Dim Query' The query goes hereQuery = "SELECT CompanyName, count(o.CustomerID) as Total" & vbCrLf & _"FROM Northwind.dbo.Orders o" & vbCrLf & _"Inner Join Northwind.dbo.Customers c on o.CustomerID = c.CustomerID" & vbCrLf & _"Group by CompanyName" Const adOpenStatic = 3Const adLockOptimistic = 3Dim iconst xlColumnClustered = 51const xl3DColumnClustered = 54const xl3DColumn = -4100const xlBarClustered = 57const xl3DBarClustered = 60const xlLineMarkers = 65const xl3DLine = -4101const xlPie = 5const xlPieExploded = 69const xl3DPie = -4102const xl3DPieExploded = 70const xlArea = 1const xl3DArea = -4098const xlSurface = 83const xlCylinderColClustered = 92const xlCylinderBarClustered = 95const xlConeColClustered = 99const xlConeBarClustered = 102const xlPyramidBarClustered = 109const xlPyramidColClustered = 106 Set objConnection = CreateObject("ADODB.Connection")Set objRecordSet = CreateObject("ADODB.Recordset")objConnection.Open connectionString ' creating the Excel object applicationSet objExcel = CreateObject("Excel.Application")objExcel.Visible = TrueobjExcel.ScreenUpdating = FalseSet objWorkbook = objExcel.Workbooks.Add()Set objWorksheet = objWorkbook.Worksheets(1)'msgBox QueryobjRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistici = 1objRecordSet.MoveFirstDo Until objRecordset.EOF i = i + 1' This is setting the column names, font, colors, etc.' This code can be simplified by ranging if desired. objExcel.Cells(1, 1).Value = "Company Name" objExcel.Cells(1, 1).Font.Size = 10 objExcel.Cells(1, 1).Font.Bold = TRUE objExcel.Cells(1, 1).Interior.ColorIndex = 6 objExcel.Cells(1, 2).Value = "Total" objExcel.Cells(1, 2).Font.Size = 10 objExcel.Cells(1, 2).Font.Bold = TRUE objExcel.Cells(1, 2).Interior.ColorIndex = 6 ' Now we are getting the data and highlighting certain columns objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("CompanyName") objExcel.Cells(i, 1).Font.Size = 10 objExcel.Cells(i, 1).Borders.LineStyle = True objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("Total") objExcel.Cells(i, 2).Font.Size = 10 objExcel.Cells(i, 2).Borders.LineStyle = True objRecordset.MoveNext objExcel.Range("A1:B1").Borders.LineStyle = True Loop' automatically fits the data to the columnsSet objRange = objWorksheet.UsedRangeobjRange.EntireColumn.Autofit()'Create a chartobjRange.ActivateobjWorkbook.Charts.AddobjWorkbook.ActiveChart.ChartType = xlCylinderColClusteredobjWorkbook.ActiveChart.SetSourceData objRange, 1objWorkbook.ActiveChart.Location 2, "Sheet1"objExcel.ScreenUpdating = True' cleaning upobjRecordSet.CloseobjConnection.Close[/code]

RE: Build Great Looking Excel Spreadsheets

divison — Wed, 05 Nov 2008 08:00:50 GMT

The article was interesting, and the conversation it spawned:Though I knew how to paste columns from SSMS, and I knew how to *automate* columns-inclusion using export tasks, what I did NOT know was how to *format* the Excel output.Something I find especially interesting, from Phil's article:--format the headings in Bold nicely IF @hr=0 SELECT @strErrorMessage='formatting the column headings in bold ', @objErrorObject=@objWorkSheet, @command='Range("A1:' +SUBSTRING(@alphabet,@currentColumn/26,1) +SUBSTRING(@alphabet,@currentColumn % 26,1) +'1'+'").font.bold' IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1 (I'm leaving out the context, which you can get from Phil's article.)And I can see it would be useful, for me, to be able to do this kind of Excel formatting from ADO/VBScript... ... so, Gosta, does your article include Excel *formatting* from VBScript? If so, by all means please share the link here, if & when your article is published.Thanks, alll...

RE: Build Great Looking Excel Spreadsheets

Gosta Munktell — Wed, 05 Nov 2008 07:30:50 GMT

:)I agree with Phil Factor. There is not just one method.But what I miss sometimes is how is the customer of this application and what are the needs etc. I am involved in projects where the purpose is to provide users regulary some time once a day (or many times a day) with data wraped in Excel. The process must be fully automated! At first will it be a batch process ora customer demand process? I use T-SQL somtimes but I prefere VBA using all the methods available in VBA. ;)Gosta

RE: Build Great Looking Excel Spreadsheets

Leon Carpay — Wed, 05 Nov 2008 07:27:50 GMT

newbie, what you are missing is that David wrote his drill for <= Excel 2003. In Excel 2007 formatting and importing data is much, much easier...

RE: Build Great Looking Excel Spreadsheets

kbe-682284 — Wed, 05 Nov 2008 06:40:56 GMT

Hummm! Must be missing something? Excel 2007 does a great job of getting data from SQL Server 2005, with the headers? No coding, very, very easy. Just click on Data->From Other Sources->SQL.What am I missing?

RE: Build Great Looking Excel Spreadsheets

jpratt-797544 — Wed, 05 Nov 2008 06:37:06 GMT

I have been getting column names with the data when copying the grid to Excel all along. I just click the blank square in the upper left corner of the grid, which selects everything, then Ctrl+C, open Excel, hit Ctrl+V, and all the data plus column names show up.The technique of using the built-in views is great though, and more published info on those semi-hidden gems is great.

RE: Build Great Looking Excel Spreadsheets

Leon Carpay — Wed, 05 Nov 2008 06:31:12 GMT

Hi David,The SP doesn't work when there is a SPACE in the SQL-table name.gr leon

RE: Build Great Looking Excel Spreadsheets

Madhivanan-208264 — Wed, 05 Nov 2008 06:06:13 GMT

You dont need a cursor to get column namesRefer point 5[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[/url]

RE: Build Great Looking Excel Spreadsheets

WayneS — Wed, 05 Nov 2008 06:05:47 GMT

Neat article.You can eliminate the cursor (though in this case there's really not a performance hit) by: -- construct an execution string declare @ExecString varchar(max) set @ExecString = '' select @ExecString = @ExecString + case when @ExecString = '' then '' else ',' end + COLUMN_NAME + '=''' + COLUMN_NAME + '''' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName order by ORDINAL_POSITION set @ExecString = 'select ' + @ExecString

RE: Build Great Looking Excel Spreadsheets

rascasse83 — Wed, 05 Nov 2008 05:51:05 GMT

Yes guys but even that technique is useless as it doesn't retain datatypes. What's the use of dumping the results in Excel if you have to spend time to reset all the columns formats datatypes?The one technique a collegue of mine uses with success is : Go into SQL Management Studio, right click on database, all tasks, export data.That will retains all datatypes and also headings.

RE: Build Great Looking Excel Spreadsheets

corneld — Wed, 05 Nov 2008 05:42:37 GMT

I concur with: :hehe:SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'Otherwise, good effort and nice article!!!

RE: Build Great Looking Excel Spreadsheets

lweismantel — Wed, 05 Nov 2008 05:36:40 GMT

The article states that unfortunately there's no way to also grab the column names from SQL Management Studio's output grid, but there is a checkbox option on the Query Options dialogue box that reads "Include column headers when copying or saving the results" that works great to bring along the column headers. Additionally, Excel 2007 now has built-in table formatting that allows you to automatically apply different colors based on conditional values from any column.Respectfully, LGW

RE: Build Great Looking Excel Spreadsheets

rascasse83 — Wed, 05 Nov 2008 05:01:48 GMT

The technique from this article doesn't seem to retain the correct data types from the database.For example a number (data type varchar in SQL) from the SQL Output grid with a leading zero, once copied into Excel with loose it's leading Zero as it will be converted to Number.Even if you format the whole spreadsheet to text before you copy the results, its will then mean real numbers are converted as text and you can't do any more sums in Excel.This means this technique is not really usefull as a fully automated technique. You will have to spend some time setting the Excel column cell data types acordinly. Which is a requirement from most of us i guess.However using the SQL Server data export functionality (right click on a table, "export") will retain column names and data types. And is really as automated as running the code from the article. I would say even more !Excel formatting bit is usefull though and the stored procedure / use of schema an eye opener.thanks

RE: Build Great Looking Excel Spreadsheets

David Jackson — Wed, 05 Nov 2008 04:48:58 GMT

Nice Macro. :)The only thing I'd add to the Sub ColorAlternateDataRows is this line at the start[code]Application.ScreenUpdating = False[/code]And at the end[code]Application.ScreenUpdating = True[/code]Then it runs [b]much[/b] quickerHTHDave J

RE: Build Great Looking Excel Spreadsheets

Phil Factor — Wed, 05 Nov 2008 03:40:28 GMT

You can do a lot of this by simply using VBA from Excel. I've done it both ways. If the structure and formatting of the spreadsheet requires knowledge of the data, or maybe if it is a timed report, or requested report, to be sent out via email using an Excel file attachment, then I do it from TSQL. Where it is a much simpler report than VBA is the obvious strategy. There are a lot of ways of producing Excel spreadsheets from database data, and the more techniques you have in your armory, the more likely you are to find one that is a good fit for a particular requirement.

RE: Build Great Looking Excel Spreadsheets

Gosta Munktell — Wed, 05 Nov 2008 02:39:29 GMT

PhilThank you for pointing at your article. It was new for me.But still why this labor in T-SQL when you can do all in VBA coding?Best regardsGosta

RE: Build Great Looking Excel Spreadsheets

Phil Factor — Wed, 05 Nov 2008 02:00:00 GMT

I realise I'm a bit off-topic, as what you are aiming to do here is a semi-manual process. but all the things you do here, and more, can be done via OLE automation. Robyn and I wrote something a while back which will work in SQL Server 2000 as well http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/The advantage in using the metadata from ADODB or odbc is that it will work for any result to give you your column titles, not just a table or view.

RE: Build Great Looking Excel Spreadsheets

Gosta Munktell — Wed, 05 Nov 2008 00:54:05 GMT

"Excel is a great tool for formatting data that you've extracted from tables or views. You can copy such data into an Excel spreadsheet straight from SQL Management Studio's output grid. Unfortunately there's no way to also grab the column names, so unless you have some way of generating them, you're stuck either omitting them or filling them in by hand."David I like your articel but one question:As you incoporate VBA macros why not use ADO as alternative to get out the data fromSQL Server. This is easier and you can get the column the names by a method like:Just part of the scriptI use this one to print TAB sep files with column headersDim rs As New ADODB.RecordsetDim utskriftrad as stringDim K as longcmd.CommandText = sqltext 'An SQL statementrs.open cmd For K = 0 To rs.Fields.Count - 1 utskriftrad = utskriftrad & rs.Fields(K).Name & Chr(9)Next Kprint utskriftrad etcI yesterday wrote an article to SQL ServerCentral maybe that one will beaccepted. And there is a lot more to write how to interact with Excel.//Gosta

RE: Build Great Looking Excel Spreadsheets

kccasey — Tue, 04 Nov 2008 23:34:54 GMT

SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'... the macros good though :)

Build Great Looking Excel Spreadsheets

David Ziffer — Tue, 04 Nov 2008 22:57:12 GMT

Comments posted to this topic are about the item [B]Build Great Looking Excel Spreadsheets[/B]