Microsoft has recently released a new version of their toolkit Data Mining for MS Excel. It’s for SQL Server 2012 and explicit target MS Excel 2010. To download it click on the following text:
In addition, Microsoft has released a new version of their PowerPivot tool for MS Excel. To download it click on the following text:
Enjoy!
Kind regards,
Dennis
If You’re a VBA Developer You should know what best practices is to export data to MS Excel. Two expressions summarize it; Microsoft ActiveX Data Object Library and CopyFromRecordset.
However, if You’re a .NET developer and face a situation where You must find a solution to export data to MS Excel then there is a chance that You don’t know the best practices. In general, working on the .NET platform means that we use .NET classes to solve various tasks. When it comes to data acquiring two of the more popular approaches are to use ADO.NET and LINQ. However, as this blog article shows using classic ADO is the fastest way to dump data into a worksheet.
When lurking around various public Q&A forums I have noticed that the question “how to export data to MS Excel” is asked over and over. This article is the answer to that question and it will provide You with the best approach when it comes to speed.
Initially we need to set a reference to “Microsoft ActiveX Data Object x.x Library” where “x.x” refers to the version number in our .NET solution. Next, we must explicit add a reference to the Object Library which is done in the below code. Don’t forget to add the library to the prerequisites or copy the required files to the project.
Imports ADODB
Imports System.Text
Imports excel = Microsoft.Office.Interop.Excel
Public Class frmMain
Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) _
Handles btnExport.Click
Const stcon As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\NorthWind.accdb"
Const stsql As String = "SELECT * FROM Invoices"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the database connection.
cnt.Open(stcon)
'Open the Recordset.
With rst
.CursorLocation = CursorLocationEnum.adUseClient
.Open(stsql, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly, _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing
End With
'Closing the database connection.
cnt.Close()
'Variables for Excel and the created workbook.
Dim xlapp As New excel.Application
Dim xlwbook As excel.Workbook = xlapp.Workbooks.Add( _
excel.XlWBATemplate.xlWBATWorksheet)
Dim xlwsheet As excel.Worksheet = _
CType(xlwbook.Worksheets(1), excel.Worksheet)
Dim xlrange As excel.Range = xlwsheet.Range("A2")
Dim xlcalc As excel.XlCalculation
'Temporarily turning off the auto calculation.
With xlapp
xlcalc = .Calculation
.Calculation = excel.XlCalculation.xlCalculationManual
End With
Dim ifieldcounter As Integer = Nothing
'Writing the field names to the worksheet.
For Each fld In rst.Fields
xlrange.Offset(0, ifieldcounter).Value = fld.Name
ifieldcounter = ifieldcounter + 1
Next
'Dump the recordset into the worksheet.
xlrange.Offset(1, 0).CopyFromRecordset(rst)
'Closing the recordset.
rst.Close()
With xlapp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlcalc
End With
fld = Nothing
rst = Nothing
cnt = Nothing
xlrange = Nothing
xlwsheet = Nothing
xlwbook = Nothing
xlapp = Nothing
End Sub
End Class
That’s it!
Kind regards,
Dennis
Introduction
Life can be rather unpredictable; You go to bed and believes that the next day will be like the other days. But in the following day You find Yourself in a complete new situation. The situation can either be good but it also can be bad depending on how life’s dice are rolled. But, despite the direction of the situation (bad/good) it explains why it has been so quiet around me and the blog.
One of the major news with Excel 2010 is the PowerPivot (P/P) tool. Last year I wrote a light weighted introduction to it; Data Visualization – The PowerPivot Tool – Part I. This time I come back with three reviews on recently published books about the tool.
Books should always try to answer the following three questions:
If a book tries to answer the first and second questions I generally classify it as a descriptive book. If a book emphasizes on all three questions or the two last items then I consider it as an explaining book that emphasizes best practice. Of course, from a practical stand it may not be an easy task to classify books. However, when reviewing books I try to classify them.
P/P is a new shining tool and as such the practical use is limited. OK, the authors appear to have spent considerable time with the beta versions of the tool and have also practical experience with related tools like SQL Server Analysis Services (SSAS). But the lack of practical use has still a negative impact on the reviewed books. So if You’re looking for real practical experience in these three books then I’m afraid You will be disappointed.
Whenever new software tools are released they seem to be shipped with new concepts and acronyms. P/P is no exception from that rule. It comes with the concept of Managed Self-Service BI. Sounds great, right? Basically the concept is about that the business side of the corporates doesn’t need any longer to ask for support from the centralized IT-department whenever they want to analyze larger data sets acquired from centralized data sources.
By the way, the formal name of P/P is Microsoft SQL Server PowerPivot for Excel.
The Books
Title: Practical PowerPivot & DAX Formulas for Excel 2010
Author: Art Tennick
Publishing Year: 2010
This book clearly consist of two parts; an introduction part to quickly get the reader to start using the tool and a presentation part for the major Data Analysis Expression (DAX) functions and formulas. It’s a descriptive book and is a great book to place on the desktop, i.e. to use it as a primer to lookup DAX functions.
The author manages to keep a relaxing writing style and at the same makes it easy to follow. It’s written in plain English and I didn’t have any problems to understand it. OK, all the acronyms can be quite frustrating to keep in mind. I wished that the author had dedicated more pages to the first part of the book. In that way he could elaborate certain parts more. The book does not provide any knowledge about Excel itself in this context. So if You’re new to some built-in tools like Pivot Table, Slicers and Charts You need to grab that knowledge elsewhere. Anyway, the book lives up to its title and the author have made a good work.
This book became quickly my favorite book. I still have it on my desk and I still need to quickly find information about individual DAX functions.
Title: PowerPivot for Excel 2010
Authors: Marco Russo and Alberto Ferrari
Publishing Year: 2010
The book is well written and the authors have put a lot of efforts to describe complex areas in the most simplest ways. Both authors seem to be experts in SSAS and to a certain degree they leverage their knowledge in this field in the book. The book is indeed a descriptive book and I find it to be rather compact, perhaps too compact.
For native Excel developers it may be interesting to take part of chapters like the one about Data Models and the chapter about Evaluation Context and CALCULATE. One chapter is aside for Pivot Tables which is good, especially when the different types of Pivot Tables are compared with each other.
In the end of the book the authors discuss the option to publish P/P reports on SharePoint Server. Practical it means that we install the P/P Server software on the SharePoint Server farm and where SSAS/Report Server, Excel Services work together with the P/P Server program. It will certainly give more raw power in order to work with super large data sets and to generate aggregated reports.
The book gives a lot of practical hands on examples in each chapter which is also good. But at the same time the book lacks any deeper discussions. I also get the impression that the authors view Excel as a basic environment for P/P reports and nothing else. Compared with the other two books here this book is more technical advanced. Practical it means that the book is written by SSAS specialists for SSAS specialists.
Title: PowerPivot for the Data Analyst: Microsoft Excel 2010
Author: Bill Jelen (aka MrExcel)
Publishing Year: 2010
Bill Jelen is today the #1 Excel author as he produce several books whenever a new Excel version is launched. It’s good to see that Bill has devoted a whole book about the P/P utility. It gives a signal to other developers and to Microsoft that Excel developers also have an interest in the P/P tool and are also committed to leverage the new utility.
This book is also classified as a descriptive book and it’s also a compact book. Bill has always an exhilarating writing style and this book is no exception from that. Of course, the book gives a detailed picture of the built-in tools Pivot Table, Charts and Slicers. Actually, the book gives some gems for Pivot Tables (unless You already have a copy of Bill’s book or Debra Dalgleish’s book about the Pivot Table tool).
It also explains the basic of the P/P tool and how to use it in a lightweight way. It also contains some nice tips with the utility. The book also contains a lot of hands on examples that are easy to follow. It makes it also easier to understand the tool and how we can use it. In the end of the book Bill shortly discuss the option to use a P/P server.
To summarize the book; it’s written by an Excel specialist for Excel specialists.
The End
All three books belongs to the same group, i.e. they are descriptive books. For a new tool it’s welcoming to cover the basic level which all these books do. However, as we gain more practical experience I hope that coming books give more guidelines towards best practices.
Kind regards,
Dennis
As a side note;
Two of these three books seems to have been initialized either at a lunch or at a dinner time where the table of contents was written down on a napkin.
My good friend Will Riley posted last month on his blog a very good series about creating Dynamic OLAP Cube Reports in Excel. The series is in three parts and walk us through the required steps in order to create this type of reports.
In addition to its main purpose the series shows clearly, at least to me:
If You have some time over then I strongly encourage You to visit Will’s blog and read it Yourself:
Thanks Will! And I’m looking forward to take part of more series of this kind.
Kind regards,
Dennis
Background
If You suspect that I, at present, work a lot with SQL Server Compact Edition (SSCE) databases than You are absolutely right about it. I have developed two larger solutions where SSCE databases are central parts of the respectively solution. In both projects data is exported to SSCE databases and then the data is acquired from these two sources to populate Excel reports with.
In one of the projects the client also wanted a simple tool to query the database with. I had two alternatives to choose between; build a query tool from scratch or look for a free/commercial query tool that could work with SSCE databases. For several reasons the client wanted in the end me to build a new tool. However, at that time I had already made some investigations and among the few tools I have found CEQuery Tool quickly became a favorite.
The CEQuery Tool
Working with the CEQuery Tool is very simple and straightforward. The tool provides us with several tools:
The below screen shot shows CEQuery’s main menu, i.e for the available tools:
For some unknown reasons, at least to me, the Script Generator tool is available via CEQuery’s Command bar and via the right click menu in the Database window but not in the main menu.
The Excel Export tool is only available via the right click menu in the Datagrid’s window. Also commands to create and delete tables are only available via the right click menu in the Database’s windows and not via its main menu.
The screen shot of the Export/Import tool’s main window.
The screen shot of the Script Generator’s main window:
The next screen shot below shows CEQuery’s main window where we have opened a SSCE database, created the SQL expression and also acquired the data:
If we right click in the Database window we get access to additional commands which the below screen shot also shows:
After we have executed the Script Generator the Query file is generated. The following screen shot shows how its content can look like:
I have been using the tool for some time now and all its main tools and functions works as expected. The tool feels reliable and stable. Of course, it has some shortcomings like:
If You work extensively with SSCE databases and are looking for a tool the CEQuery tool should be among the tools that should be evaluated.
Kind regards,
Dennis
In a previously post I wrote about how we can retrieve data from a SQL Server Compact Edition (SSCE) database with ADO and Excel. Here I will discuss how we actually can create a database with VBA in Excel. In a coming post I will show how it can be done in VSTO which will also show us the differences between VBA and .NET.
I have created an Excel Template file which can be downloaded from here. It gives us a structure to work in and to make sure we get a desired database created. The following screen shot below shows the customized template. The template itself can either be used with Excel 2007 or Excel 2010. Of course, it can be opened in older versions as well but without any access to a GUI to run the methods. It is implicit assumed that the database to be created consists of two or more tables, i.e. an error will appear if only one table is used:
To create the database in code we need to use the Microsoft ADOX library which was explicit designed to work with the Microsoft Jet OLE DB Provider. When we use it with other providers not all methods and not all properties may be available. This is indeed true when using the Microsoft SQL Server Compact 3.5 provider.
The Excel Template file contains code to select location of the database that will be created, code to create the database and code to clear the table from all data. The most interested code is the one that actually creates the database.
The first part of the code solution controls so that all entries are made and that it contains correct data:
'Callback for rxbtnCreate onAction
Sub Create_Database(control As IRibbonControl)
Const CstEmptyMsg As String = _
"A path to the database and " & _
"a name for the database " & _
"must exist. " & vbNewLine & _
"Please correct it and try again."
Const CstErrorMsg As String = _
"The database could not be created." & vbNewLine & _
"Make sure You have used correct " & vbNewLine & _
"values in the table and try again."
Const CstExtension As String = ".sdf"
Const CstFError As String = _
"You cannot have two fields with an identical name in the table."
Const CstFieldError As String = _
"At least one item lacks a field name."
Const CstPK As String = "TRUE"
Const CstTableError As String = _
"At least one item lacks a table name."
Const CstTypeSizeError As String = _
"At least one cell is empty in the DataType and DataSize fields."
Dim rnCheckTable As Range
Dim rnCheckTypeSize As Range
Dim rnCheckField As Range
Dim rnDataInput As Range
Dim rnUnique As Range
Dim lnEmptyCell As Long
Dim lnFieldCounter As Long
Dim lnLastRow As Long
Dim vaDataInput As Variant
Dim vaFields As Variant
Dim vaField As Variant
Dim vaTable As Variant
Dim vaTables As Variant
Dim stFileName As String
Dim stName As String
Dim stSuccessMsg As String
Dim stPath As String
Application.ScreenUpdating = False
wksTable.Unprotect
With wksTable
'Check that it exist a path and a name for the database.
stPath = .Range("dbPath").Value
stName = .Range("dbName").Value
If (stPath = Empty) Or (stName = Empty) Then
MsgBox Prompt:=CstEmptyMsg, Buttons:=vbOKOnly + vbCritical, Title:=Cm_stTitle
GoTo ExitSub
End If
'Grab the last used row.
lnLastRow = .Range("A102").End(xlUp).Row
'If no table exist than exit sub.
If lnLastRow = 2 Then GoTo ExitSub
'If one or more items lack table name then exit sub.
lnEmptyCell = 0
Set rnCheckTable = .Range("A3:A" & lnLastRow)
On Error Resume Next
lnEmptyCell = rnCheckTable.SpecialCells(xlCellTypeBlanks).Rows.Count
On Error GoTo 0
If lnEmptyCell > 0 Then
rnCheckTable.SpecialCells(xlCellTypeBlanks).Select
MsgBox CstTableError, vbOKOnly + vbCritical, Cm_stTitle
GoTo ExitSub
End If
'If one or more fields lack field name then exit sub.
lnEmptyCell = 0
Set rnCheckField = .Range("B3:B" & lnLastRow)
On Error Resume Next
lnEmptyCell = rnCheckField.SpecialCells(xlCellTypeBlanks).Rows.Count
On Error GoTo 0
If lnEmptyCell > 0 Then
rnCheckField.SpecialCells(xlCellTypeBlanks).Select
MsgBox CstFieldError, vbOKOnly + vbCritical, Cm_stTitle
GoTo ExitSub
End If
'Create a unique list of table names.
.Range("A2:A" & lnLastRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("H1"), Unique:=True
vaTables = .Range("H2:H" & .Range("H102").End(xlUp).Row).Value
.Range("H1:H" & .Range("H102").End(xlUp).Row).ClearContents
'If it exist two or more fields with the same name in one table.
vaFields = rnCheckField.Value
For Each vaTable In vaTables
For Each vaField In vaFields
lnFieldCounter = .Evaluate("=SUMPRODUCT(--((" & rnCheckTable.Address & ")= _
""" & vaTable & """),--((" & rnCheckField.Address & ")=""" & vaField & """))")
If lnFieldCounter > 1 Then
MsgBox CstFError, vbOKOnly + vbCritical, Cm_stTitle
GoTo ExitSub
End If
Next vaField
Next vaTable
'If it exist two or more tables with the same name.
'No need to add any check for this as only one table with a specific name will be
'created and added to the database. And the related rows will be added to the created table.
lnEmptyCell = 0
Set rnCheckTypeSize = .Range("C3:D" & lnLastRow)
On Error Resume Next
lnEmptyCell = rnCheckTypeSize.SpecialCells(xlCellTypeBlanks).Rows.Count
On Error GoTo 0
If lnEmptyCell > 0 Then
rnCheckTypeSize.SpecialCells(xlCellTypeBlanks).Select
MsgBox CstTypeSizeError, vbOKOnly + vbCritical, Cm_stTitle
GoTo ExitSub
End If
'Grab the data entries.
Set rnDataInput = .Range("A3:E" & lnLastRow)
vaDataInput = rnDataInput.Value
End With
stSuccessMsg = "The database " & stName & CstExtension & vbNewLine & _
"has successfully been created in the " & vbNewLine & _
"folder " & stPath & "!"
'Put the name of the database together with the selected path to the location of the
'new database.
If Not stPath = "c:\" Then
stFileName = stPath & "\" & stName & CstExtension
Else
stFileName = stPath & stName & CstExtension
End If
'OK, time to send the data to the function that actually will create the database.
If Create_SSCE_Database(stFileName, vaTables, vaDataInput) Then
MsgBox Prompt:=stSuccessMsg, Buttons:=vbInformation, Title:=Cm_stTitle
Else
MsgBox Prompt:=CstErrorMsg, Buttons:=vbCritical, Title:=Cm_stTitle
End If
ExitSub:
wksTable.Protect
Exit Sub
End Sub
The second part of the main solution is the one that creates the database as the following code also shows:
Private Function Create_SSCE_Database(ByVal stDatabase As String, _ ByVal vaUniqueTables As Variant, _ ByVal vaData As Variant) As Boolean Const CstErrorMsg = "The following error has occured:" Dim ADOXCat As ADOX.Catalog Dim ADOXTable As ADOX.Table Dim iDataSize As Integer Dim lnColCounter As Long Dim lnRowCounter As Long Dim stAttributes As String Dim stConnection As String Dim stFieldName As String Dim stPK As String Dim bFlag As Boolean Dim vaDataType As Variant Dim vaItem As Variant On Error GoTo Error_Handling bFlag = True 'Connection string. stConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _ "Data Source=" & stDatabase & ";" & _ "Persist Security Info=False;" 'Instantiate the ADOX variable. Set ADOXCat = New ADOX.Catalog 'In case we have an already existing database. On Error Resume Next Kill stDatabase On Error GoTo 0 'Create the empty database. ADOXCat.Create stConnection 'All the properties of the connection string is printed to 'the Immediate Window. It can be valuable to access this 'information. 'Debug.Print xCat.ActiveConnection 'Here we create the tables and append the columns to them. For Each vaItem In vaUniqueTables Set ADOXTable = New ADOX.Table ADOXTable.Name = vaItem For lnRowCounter = LBound(vaData) To UBound(vaData) If vaData(lnRowCounter, 1) = vaItem Then stFieldName = CStr(vaData(lnRowCounter, 2)) vaDataType = vaData(lnRowCounter, 3) iDataSize = CInt(vaData(lnRowCounter, 4)) stAttributes = vaData(lnRowCounter, 5) Select Case vaDataType Case "adBoolean": ADOXTable.Columns.Append stFieldName, adBoolean, iDataSize Case "adCurrency": ADOXTable.Columns.Append stFieldName, adCurrency, iDataSize Case "adDBTimeStamp": ADOXTable.Columns.Append stFieldName, adDBTimeStamp, iDataSize Case "adDouble": ADOXTable.Columns.Append stFieldName, adDouble, iDataSize Case "adGUID": ADOXTable.Columns.Append stFieldName, adGUID, iDataSize Case "adInteger": ADOXTable.Columns.Append stFieldName, adInteger, iDataSize Case "adLongVarBinary": ADOXTable.Columns.Append stFieldName, adLongVarBinary, iDataSize Case "adSingle": ADOXTable.Columns.Append stFieldName, adSingle, iDataSize Case "adSmallInt": ADOXTable.Columns.Append stFieldName, adSmallInt, iDataSize Case "adUnsignedTinyInt": ADOXTable.Columns.Append stFieldName, adUnsignedTinyInt, iDataSize Case "adVarBinary": ADOXTable.Columns.Append stFieldName, adVarBinary, iDataSize Case "adVarWChar": ADOXTable.Columns.Append stFieldName, adVarWChar, iDataSize Case "adWChar": ADOXTable.Columns.Append stFieldName, adWChar, iDataSize End Select 'We cannot set this attribute for all data types but for most of them. 'For some unknown reason I was not able to use the names of the attributes. If stAttributes <> Empty Then Select Case stAttributes Case 1: ADOXTable.Columns(stFieldName).Attributes = 1 'adColFixed Case 2: ADOXTable.Columns(stFieldName).Attributes = 2 'adColNullable Case 3: ADOXTable.Columns(stFieldName).Attributes = 3 'adColFixed Or adColNullable End Select End If End If Next lnRowCounter 'Add the created table with its columns to the database. ADOXCat.Tables.Append ADOXTable Set ADOXTable = Nothing Next vaItem ExitHere: Set ADOXTable = Nothing Set ADOXCat = Nothing If bFlag Then Create_SSCE_Database = True Else Create_SSCE_Database = False End If Exit Function Error_Handling: bFlag = False Resume ExitHere End Function
Because I always try to use early binding a reference to the Object Library “Microsoft ADO Ext. x.x for DLL and Security” must be made (x.x refers to version number). After filling in the required data and running the above code a SSCE database is created in the wanted location. When we open the database in the SQL Server Management Studio it looks like the following:
That’s all! Now we can populate the SSCE database with data. Let me know if You think that the code can be improved.
The attached template file is not protected in any ways except the sheet protection is activated but with no password.
Kind regards,
Dennis
Introduction
Often we see questions about acquiring data from various data sources like SQL Server Databases, MDBs, Excel and text/xml-files in webbased Q&A forums and in blogs. But what I see very rarely is any discussions on how to retrieve data from Microsoft SQL Server Compact Edition (SSCE) databases and to populate Excel worksheets with the acquired data. This article will introduce how to use SCEE databases as data sources and dump the retrieved data into a pre-defined range in a worksheet.
SSCE is a lightweight version of the SQL Server database engine. It requires a minimal installation and configuration to use. Unlike its bigger “brother” it’s a server less database engine.
For an introduction to SSCE databases please see the following sites:
This article describes how to proceed to get data into an Excel solution from a SSCE database by using VBA. But before diving into it let us first take a look on how to create a SSCE database and its limitations.
Creating SSCE Databases
As with many other targets to achieve; creating SSCE databases can be done in some ways. Here I will show how simple it is by using the SQL Server Manager Studio which I regular use (as opposite to my use of the standalone SSCE database engine which I never use.)
Actually, it’s very simple to create a SSCE database from scratch:
When the database has been created we need to add the wanted tables and their contents inside the SQL Server Manager Studio.
But what if we have a SQL Server database and would like to convert it to a SSCE database? The easiest way I have found out is simple to use a third-party tool to do it for us. In my opinion the best tool for the job is the inexpensive Data Port Wizard tool from Primeworks. In addition to convert SQL Server databases it can also convert in a smooth way MS Access databases to SSCE databases. In other words, it shouldn’t be difficult to port these database types to SSCE database type.
Limitations of SSCE Databases
SSCE is on its face very attractive but before we get carried away we need to take a closer look into its limitations.
Populate a Worksheet by using VBA, ADO and a SSCE Database
Before we start to take a closer look on the code solution let me first mention that if You want more knowledge about ADO than You can download MDAC 2.8 which includes a superb help file (ado28.hlp) on the subject: MDAC 2.8 SDK
The next thing I feel I must point out is the driver to use with SSCE databases and the required system files for running them. When distributing solutions that involve SSCE databases we must also distribute theses files. Actually, in my experience this is a must in all cases. To do so we first need to get the redistributing MSI file which we do by downloading the package Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop. When running the EXE file we get the required MSI file for x86 as well as the MSI file for the x64 platform. Actually included in the package is also the MSI file that includes both these two files.
What is also notable is that although we know it’s available the driver does not show up in the Data Link Wizard or in the .NET Wizard. Because of its anonymous character we can only get the correct connection string by trying. But a good start for the efforts is SQL Server Compact Edition Connection Strings Samples.
After installing the required files we can execute, for instance, the following code in order to populate a worksheet with data from a SSCE database:
Option Explicit
'Make sure You have a reference set to Microsoft
'ActiveX Data Objects 2.5 Library or later.
Sub Retrieve_Data_From_SSCE_In_VBA()
'Name of the SSCE database.
Const C_stDBName = "XLDennis.sdf"
'The SQL Expression to be used.
Const C_stSQL = "SELECT CompanyName AS Company, City FROM Customers;"
Dim stPath As String
Dim stConnection As String
Dim ADODBcnt As ADODB.Connection
Dim ADODBrst As ADODB.Recordset
Dim ADODBfld As ADODB.Field
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnstart As Range
Dim lnFieldCounter As Long
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
Set rnstart = wsSheet.Range("A2")
'Path to the SSCE database.
stPath = "C:\Users\Dennis Wallentin\SSCE\"
'Connection string.
stConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
"Data Source=" & stPath & C_stDBName & ";"
'Instantiate the ADODB objects.
Set ADODBcnt = New ADODB.Connection
Set ADODBrst = New ADODB.Recordset
'Open the connection to the databas.
ADODBcnt.Open stConnection
'Create a disconnected recordset.
With ADODBrst
.CursorLocation = CursorLocationEnum.adUseClient
.Open C_stSQL, _
ADODBcnt, _
ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly, _
ADODB.CommandTypeEnum.adCmdText
.ActiveConnection = Nothing
End With
'Close down the connection.
ADODBcnt.Close
Application.ScreenUpdating = False
'Populate the worksheet with the field names.
For Each ADODBfld In ADODBrst.Fields
rnstart.Offset(-1, lnFieldCounter).Value = _
ADODBrst.Fields(lnFieldCounter).Name
lnFieldCounter = lnFieldCounter + 1
Next ADODBfld
'Dump the data into the worksheet.
rnstart.CopyFromRecordset ADODBrst
'Close the recordset.
ADODBrst.Close
'Release objects from memory.
Set ADODBrst = Nothing
Set ADODBcnt = Nothing
End Sub
Because of the limited differences between VBA and VB.NET I decided to exclude the sample when using VB.NET. I believe it should not be a problem for You.
Kind regards,
Dennis
I have also published a post about how to create a SSCE database with VBA.
Introduction
With Excel 2010 around the corner I decided to make a blog series about visualizing data by using the new PowerPivot tool.
My plan with the series is to present and discuss the PowerPivot. It will also include pointers to other online resources as well as to upcoming books where it’s appropriated.
Anyway, let’s start with some keywords the series will be building around.
What is Data Visualization?
The term Data Visualization is defined as “the study of the visual representation of data” and its main goal is to “communicate information clearly and effectively through graphical means” (Wikipedia).
I have no intention to discuss Data Visualization in more details but why I bring it up here is that the PowerPivot tool’s ultimate goal is to visualize large data sets for decision makers.
What is PowerPivot?
The PowerPivot tool is a free add-in to Excel 2010 and later versions. It’s available in an x86 version as well as in an x64 version. It requires version 2010 or later of Excel. During the beta stage the tool was named Gemini but MSFT changed it to PowerPivot when it was finally released. It should also be mentioned that a version for SharePoint 2010 and later is available, it’s shipped with SharePoint 2010.
Although it’s said that PowerPivot is a new brand tool we can track it back, at least to some degree, to the Data Mining add-in. The Data Mining tool exists in two versions; one version for SQL Server 2005 and one version for SQL Server 2008. The two versions both target Excel 2007. Previously a version for Excel 2002/2003 has been available. If You’re interested to learn more about this tool You can read the excellent guide Introducing the SQL Server 2005 Data Mining Add-ins for Office 2007.
Another important aspect is that MS Query is still available in Excel 2010 so we don’t need to worry that PowerPivot will replace MS Query. If we compare these two tools we can say that the later, i.e. the PowerPivot add-in, is the modern big brother to MS Query as it’s more powerful (can handle very large data sets) and it’s more sophisticated then MS Query.
For us, who have been around for a while, it does not come as a surprise that Excel with its toolbox can be used as a Business Intelligence (BI) tool. However, MSFT seems to first now recognize Excel as a BI tool because the PowerPivot tool has been released. In other words, the PowerPivot tool can be described as a BI tool.
For a more detailed introduction of the PowerPivot for Excel I recommend to read the following entries in MSDN:
As a general note to the above online resources:
PowerPivot can work with a great number of external data sources and not only with SQL Server .
I find the following site as the best starting point and it also serves as a portal for the PowerPivot tool:
In my next post I will show how we can work with the PowerPivot tool in Excel.
Kind regards,
Dennis
Now that I have managed to get a new MSDN subscription I’m about to upgrade my physical testserver with Windows 2008 R2 Server, SQL Server 2008 and SharePoint 2010. Despite all the good installation wizards included in the softwares it requires more knowledge and understanding then just click on the right buttons so to speak.
One of the best online resources that can be found is Critical Path Training who provides some great articles about MOSS. In addition to the mentioned online resources in my blog article SharePoint – The Start I would recommend to take a closer look into their excellent article SharePoint Server 2010 RTM Virtual Machine Setup Guide. To get access to it You need to create a new account at their site and the article can then be found under the Members section.
Microsoft has recently released the SDK for SharePoint 2010 which can be found at Welcome to the Microsoft SharePoint 2010 SDK. The Microsoft SharePoint Team has also recently published a blog article about available language packs to SharePoint. For more information please see the article Language Offerings for SharePoint 2010 Products.
At present I have no intention to look for new books that cover version 2010. I still have some chapters left in the books I bought the first time I set up the server.
The main reason that drives me to invest time with the SharePoint platform is, of course, the Excel Services. For me it’s still a compliment to develop custom solutions with Excel and with .NET (automation and add-ins).
Do You do any work with SharePoint and in particular with Excel Services? What is Your experience with the Excel Services?
Kind regards,
Dennis
Edit Note:
The same day as this article was published Erika Erhli (MSFT) published a compiled list of online resources at MSDN for SharePoint 2010: MSDN: Getting Started with SharePoint 2010 and SharePoint 2010 Advanced Developer Training
Andreas Glaser has had the kindness to update his great tutorial for installning SharePoint 2010 with SQL Server 2008 R2 and Windows 2008 R2 Server. For more information please see: Installing SharePoint Server 2010 on Windows Server 2008 R2 and SQL Server 2008 R2 – Part 1: Overview
For some weeks ago Microsoft announced that they had launched the final version of Open XML SDK 2.0 for Microsoft Office. With this SDK we can create and manipulate Excel workbooks in code without involving Excel and also without having Excel installed.
What comes as a bonus with SDK 2.0 is the Open XML SDK 2.0 Productivity Tool for Microsoft Office. I was very pleased and surprised to discover that this is a multipurpose tool that allows us to a) generate reflected code, i.e to see the correct code to interact with the Open XML Documents file format, b) comparing source and target files code and b) validate code. We can also via the tool easily get access to the Open XML documentation.
The process to generate reflected code is very simple:
1. Create an Excel workbook and customize it as You want the final product to look like. Save it with the Open XML file format.
2. Open the Productivity Tool and open the created Excel workbook.
3. Navigate to the part of the created Excel workbook you want the code for.
4. Hit the button Reflect Code.
5. Done!
The following screen shot shows the tool in action:
However, we cannot control which language the generated reflected code is presented to us. The output is always in C# so if we want to see the generated VB.NET code we need to convert the generated C# code to VB.NET with a tool like Code Converter from Telerik.
The blog Brian Jones & Zeyad Rajabi: Office Solutions has published an excellent map over available online (MSFT’) resources for Open XML, which can be found at the following URL Zeyad Rajabi’s Open XML SDK Blog Map.
Personally I’m still in the initially phase and up to this date I have only done five smaller Excel jobs that involved manipulation of the Open XML file format. Have You done any work that involves it and if yes what are Your experiences?
The more I work with it and the more I explore the above tool the more attractive it becomes. The real potential, at least to me, is that with Open XML SDK installed on a server we can generate Excel Reports in a smooth and structural way without having Excel installed.
Enjoy it!
Kind regards,
Dennis
