Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Build Great Looking Excel Spreadsheets Expand / Collapse
Author
Message
Posted Wednesday, November 5, 2008 8:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:43 PM
Points: 772, Visits: 1,185
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 above

Copy with Headers

FirstName MiddleName LastName
285 E Abbas
293 R. Abel




SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #597426
Posted Wednesday, November 5, 2008 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 4, 2009 4:33 PM
Points: 1, Visits: 13
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
Post #597440
Posted Wednesday, November 5, 2008 9:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Interesting article...


Post #597446
Posted Wednesday, November 5, 2008 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:03 PM
Points: 23, Visits: 173
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...
Post #597459
Posted Wednesday, November 5, 2008 9:35 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, September 5, 2010 6:28 AM
Points: 772, Visits: 807
corneld (11/5/2008)
I concur with:

SSMS > Tools > Options > Query Results > SQL Server > Results to Grid > 'Include column headers when copying or saving results'

Otherwise, good effort and nice article!!!


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.




www.abbstract.info - my blog
www.sqlsimon.com - my experiences with SQL Server 2008
Post #597468
Posted Wednesday, November 5, 2008 9:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
divison (11/5/2008)
Thanks, David J. -- that'll do it! :)

...

Thanks again...


No problem :D

Here is another handy macro that demonstrates formatting, and tells you the numbers Excel uses for colours as a bonus


Sub colors56()
'57 colors, 0 to 56
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim i As Long
Dim str0 As String, str As String
For 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 i
done:
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub


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



http://glossopian.co.uk/
"I don't know what I don't know."
Post #597481
Posted Wednesday, November 5, 2008 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 9:28 AM
Points: 1, Visits: 10
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.
Post #597611
Posted Wednesday, November 5, 2008 12:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 AM
Points: 204, Visits: 1,336
Thank you David J
It seems we are thinking at the same direction.
You can do it in VBA and VB script. In fact some time I development and tests
in 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 depending
on 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



Post #597626
Posted Thursday, November 6, 2008 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 12:38 PM
Points: 14, Visits: 127
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
Post #598078
Posted Monday, May 9, 2011 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 9, 2011 12:46 PM
Points: 1, Visits: 0
Hey this is a great template! Thanks for the help

Andrea B.
Excel Spreadsheets
Post #1105691
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse