Create Pivottable-reports with VB.NET
I thought it would be of general interest to see how we can create reports based on Pivottable(s) by automating Excel from VB.NET 2003.
Depending on what the purpose is with a report we can control what the end-users can do via the creation of the SQL-query and how we setup of the Pivotable(s).
In the example early binding is in use and the following namespaces must be imported to the project:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices
Maincode:
Const stCon As String = _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Northwind.mdb;DefaultDir=C:\;" & _
"DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;"
Const stSQL As String = _
"SELECT ShipCountry, " & _
"COUNT(Freight) AS [# Of Shipments], " & _
"SUM(Freight) AS [Total Freight] " & _
"FROM Orders " & _
"GROUP BY ShipCountry;"
Dim xlApp As Excel.Application
Try
'Grab a running instance of Excel.
xlApp = Marshal.GetActiveObject("Excel.Application")
Catch ex As COMException
'If no instance exist then create a new one.
xlApp = New Excel.Application
End Try
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 = CType(xlWSheet, Excel.Worksheet).Range("B2")
'Create the Pivotcache.
Dim ptCache As Excel.PivotCache = xlWBook.PivotCaches.Add( _
SourceType:=Excel.XlPivotTableSourceType.xlExternal)
'Setup the Pivotcache.
With ptCache
.Connection = stCon
.CommandText = stSQL
.CommandType = Excel.XlCmdType.xlCmdSql
End With
'Create the Pivottable.
Dim ptTable As Excel.PivotTable = _
xlWSheet.PivotTables.Add( _
PivotCache:=ptCache, _
TableDestination:=xlRange, _
TableName:="PT_Report")
'Setup the Pivottable.
With ptTable
.ManualUpdate = True
.PivotFields("ShipCountry").Orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields("# Of Shipments").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields("Total Freight").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Format(Excel.XlPivotFormatType.xlReport2)
.ManualUpdate = False
End With
xlWBook.SaveAs("c:\Report.xls")
'Switch to Excel.
With xlApp
.Visible = True
.UserControl = True
End With
'Tell the Garbage Collector that these objects are ready to be destroyed.
ptTable = Nothing
ptCache = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Northwind.mdb;DefaultDir=C:\;" & _
"DriverId=25;FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout=5;"
Const stSQL As String = _
"SELECT ShipCountry, " & _
"COUNT(Freight) AS [# Of Shipments], " & _
"SUM(Freight) AS [Total Freight] " & _
"FROM Orders " & _
"GROUP BY ShipCountry;"
Dim xlApp As Excel.Application
Try
'Grab a running instance of Excel.
xlApp = Marshal.GetActiveObject("Excel.Application")
Catch ex As COMException
'If no instance exist then create a new one.
xlApp = New Excel.Application
End Try
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 = CType(xlWSheet, Excel.Worksheet).Range("B2")
'Create the Pivotcache.
Dim ptCache As Excel.PivotCache = xlWBook.PivotCaches.Add( _
SourceType:=Excel.XlPivotTableSourceType.xlExternal)
'Setup the Pivotcache.
With ptCache
.Connection = stCon
.CommandText = stSQL
.CommandType = Excel.XlCmdType.xlCmdSql
End With
'Create the Pivottable.
Dim ptTable As Excel.PivotTable = _
xlWSheet.PivotTables.Add( _
PivotCache:=ptCache, _
TableDestination:=xlRange, _
TableName:="PT_Report")
'Setup the Pivottable.
With ptTable
.ManualUpdate = True
.PivotFields("ShipCountry").Orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields("# Of Shipments").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields("Total Freight").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Format(Excel.XlPivotFormatType.xlReport2)
.ManualUpdate = False
End With
xlWBook.SaveAs("c:\Report.xls")
'Switch to Excel.
With xlApp
.Visible = True
.UserControl = True
End With
'Tell the Garbage Collector that these objects are ready to be destroyed.
ptTable = Nothing
ptCache = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing
Personally I use more and more VB.NET to create "reporttools" instead of VB 6.0 although I still find it hard to switch between VBA/VB and VB.NET.
Kind regards,
Dennis
Stephen Bullen:
What's interesting to note is how similar VB.NET is to VBA, when all you're doing is automating the Excel objects. There are only a few differences:
- The use of constructors when declaring variables (i.e. Dim stCon As String = "...") rather than doing the same over two lines in VBA.
- The use of CType to explicitly convert between object types (where we'd let VBA do that for us)
- The use of the Try...Catch block to get/create an Excel instance
- Not using 'Set' to assign objects to variables
Also, in VB.Net we have to fully-qualify the constants, such as Excel.XlPivotTableSourceType.xlExternal, whereas that's optional (but works fine just the same) in VBA
28 June 2005, 3:00 pmXL-Dennis:
Thanks for Your input
I agree and per se VB.NET should not be considered as a difficult enviroment to work with in respect to the automation of Excel.
As for the type conversion there exist also another (better?) approach then using CType:
Dim xlWSheet As Excel.Worksheet = _
DirectCast(xlWBook.Worksheets(1), Excel.Worksheet)
For more info about it please see MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vakeydirectcast.asp
It's a cool feature when it comes to 'Set' in VB.NET!
I sometime forget and write SET when working in VB.NET and immediately it's deleted.
If we don't want to try to grab a running instance of Excel then we can use the following one-liner declaration:
Dim xlApp As Excel.Application = New Excel.Application
I sincerely hope that MSFT will allow us to work with ADO.NET in the next release of Office.
From my point of view this would be one of the most important new features.
Kind regards,
29 June 2005, 6:11 amDennis
thanasis:
Hi
I've tried the above code and I take the exception "Old format or invalid type library" in the following line:
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
I've tried it with Office XP (Excel 10 library).
I've turned the above code to VB6 and it works OK !!!
Do you know what is happend?
Thanks in advance
7 November 2005, 10:02 amThanasis
Birgir Erlendsson:
thanasis: Check this out
BUG: "Old format or invalid type library" error when automating Excel
4 March 2006, 4:30 amhttp://support.microsoft.com/default.aspx?scid=kb;en-us;320369
Rick Snyder:
Thanks for the information.
I tried the code verbatum in VS2005 and it worked fine.
Very much appreciated. Finding information on creating a pivot table programatically has been a real hassle. Your example gave me exactly what I was after.
Where did you get the references from? The MS documentation wasn't very clear on this. They had good information on using a chart but was quite lacking in respect to a pivot table.
Thanks again!
17 February 2007, 3:04 amJon Peltier:
Rick -
I do a lot of pivot table manipulation in VBA, and the code has come from a combination of recorded macros, study of the object model (mostly using the VBE's Object Browser), and a lot of experimentation. I don't recall whether I got much code from MS or other sources, but I don't think so.
17 February 2007, 8:31 amneed help automating pivot tables:
Please. I am using a simple VBA code to change a variable in a pivot table.
to be specific:
ActiveSheet.PivotTables("Custom-AL").PivotFields("counted event type"). _
CurrentPage = axisevent
pretty simple... the problem is that once I change a field from the macro, the fields start showing the wrong data. I confirmed this several times.
please help!! I can be reached at 212-622-0545
28 June 2007, 1:15 pmor michael.x.zumchak@jpmchase.com
need help automating pivot tables:
please help .. automating pivot table with VBA causes WRONG DATA!!
mike
28 June 2007, 1:15 pm212-622-0545
Dennis Wallentin:
Mike,
It can either be the SQL query that generates wrong data or that You're showing the wrong fields.
Follow Jon's suggestion to record a macro and then evaluate the generated code.
Kind regards,
30 June 2007, 7:02 amDennis
Ernesto:
I'm Using your code, I get the Exception: HRESULT: 0x800A03EC, in Line :
10 July 2007, 2:53 pmWith ptCache
.Connection = stCon
Peter:
Can you please submit the C# version of this code
Thanks in Advance
17 September 2007, 12:16 amPeter
fira:
I have the same problem as Ernesto. I have problem with With ptCache
.Connection = stCon
I believe I need to change to my ODBC SQL connection.
Any help with that?
Thanks
3 December 2007, 12:08 pmParesh:
I am also having problems with .connection = stcon.
I am trying to connect to sql server. Please help
27 February 2008, 3:38 amDaniel:
I got the "HRESULT: 0x800A03EC" too. But I was trying it with Excel 2007.
11 July 2008, 2:23 amjames davenport:
I got the "HRESULT: 0x800A03EC" too @
With ptCache
.Connection = stCon 'uses an SQL server connection string
VS2003, excel 2003 , winXP
if anyone got this working after this error can you post the fix.
29 July 2008, 5:53 am