SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Build Great Looking Excel Spreadsheets


Build Great Looking Excel Spreadsheets

Author
Message
Jerry Hung
Jerry Hung
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3546 Visits: 1208
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
peter.graney
peter.graney
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9891 Visits: 1407
Interesting article...



divison
divison
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 181
Thanks, David J. -- that'll do it! Smile

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...
Abbs
Abbs
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 807
corneld (11/5/2008)
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!!!


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
David Jackson
David Jackson
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 2020
divison (11/5/2008)
Thanks, David J. -- that'll do it! Smile

...

Thanks again...


No problem BigGrin

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 Wink

Dave J


http://glossopian.co.uk/
"I don't know what I don't know."
pileofmush
pileofmush
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
Gosta Munktell
Gosta Munktell
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 2161
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.

Smile Gosta
Clarie DeWayne
Clarie DeWayne
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 151
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
andreaburton
andreaburton
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Hey this is a great template! Thanks for the help

Andrea B.
Excel Spreadsheets
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search