November 5, 2008 at 6:40 am
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?
November 5, 2008 at 7:27 am
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...
November 5, 2008 at 7:30 am
🙂
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
November 5, 2008 at 8:00 am
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...
November 5, 2008 at 8:42 am
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
November 5, 2008 at 8:52 am
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
November 5, 2008 at 9:06 am
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
November 5, 2008 at 9:16 am
Interesting article...
November 5, 2008 at 9:22 am
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...
November 5, 2008 at 9:35 am
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.
November 5, 2008 at 9:43 am
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
November 5, 2008 at 12:05 pm
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.
November 5, 2008 at 12:32 pm
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
November 6, 2008 at 6:46 am
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
May 9, 2011 at 12:47 pm
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply