Build Great Looking Excel Spreadsheets

  • 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?

  • 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...

  • 🙂

    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 or

    a customer demand process?

    I use T-SQL somtimes but I prefere VBA using all the methods available in VBA.

    😉

    Gosta

  • 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...

  • divison (11/5/2008)


    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...

    Here is a VBS Script that does exactly that.

    Oh, and another tweak to the original macro. If you change

    Set S = ThisWorkbook.ActiveSheet

    to

    Set S = ActiveSheet

    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

    Dim connectionString

    'The connection string goes here

    connectionString = "Provider = SQLOLEDB;Data Source=SERVER_NAME;" & _

    "Trusted_Connection=Yes;Initial Catalog=Northwind;"

    Dim Query

    ' The query goes here

    Query = "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 = 3

    Const adLockOptimistic = 3

    Dim i

    const xlColumnClustered = 51

    const xl3DColumnClustered = 54

    const xl3DColumn = -4100

    const xlBarClustered = 57

    const xl3DBarClustered = 60

    const xlLineMarkers = 65

    const xl3DLine = -4101

    const xlPie = 5

    const xlPieExploded = 69

    const xl3DPie = -4102

    const xl3DPieExploded = 70

    const xlArea = 1

    const xl3DArea = -4098

    const xlSurface = 83

    const xlCylinderColClustered = 92

    const xlCylinderBarClustered = 95

    const xlConeColClustered = 99

    const xlConeBarClustered = 102

    const xlPyramidBarClustered = 109

    const xlPyramidColClustered = 106

    Set objConnection = CreateObject("ADODB.Connection")

    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open connectionString

    ' creating the Excel object application

    Set objExcel = CreateObject("Excel.Application")

    objExcel.Visible = True

    objExcel.ScreenUpdating = False

    Set objWorkbook = objExcel.Workbooks.Add()

    Set objWorksheet = objWorkbook.Worksheets(1)

    'msgBox Query

    objRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistic

    i = 1

    objRecordSet.MoveFirst

    Do 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 columns

    Set objRange = objWorksheet.UsedRange

    objRange.EntireColumn.Autofit()

    'Create a chart

    objRange.Activate

    objWorkbook.Charts.Add

    objWorkbook.ActiveChart.ChartType = xlCylinderColClustered

    objWorkbook.ActiveChart.SetSourceData objRange, 1

    objWorkbook.ActiveChart.Location 2, "Sheet1"

    objExcel.ScreenUpdating = True

    ' cleaning up

    objRecordSet.Close

    objConnection.Close


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • 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

    FirstNameMiddleNameLastName

    285EAbbas

    293R.Abel

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • 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

  • Interesting article...

  • 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...

  • 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.



    https://www.abbstract.info/ - my blog
    http://www.sqlsimon.com/ - my experiences with SQL Server 2008

  • divison (11/5/2008)


    Thanks, David J. -- that'll do it! 🙂

    ...

    Thanks again...

    No problem 😀

    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."

  • 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.

  • 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

  • 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

  • Hey this is a great template! Thanks for the help

    Andrea B.

    Excel Spreadsheets

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply