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
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
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,
Dennis
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
Thanasis
thanasis: Check this out
BUG: “Old format or invalid type library” error when automating Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
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!
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.
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
or michael.x.zumchak@jpmchase.com
please help .. automating pivot table with VBA causes WRONG DATA!!
mike
212-622-0545
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,
Dennis
I’m Using your code, I get the Exception: HRESULT: 0×800A03EC, in Line :
With ptCache
.Connection = stCon
Can you please submit the C# version of this code
Thanks in Advance
Peter
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
I am also having problems with .connection = stcon.
I am trying to connect to sql server. Please help
I got the “HRESULT: 0×800A03EC” too. But I was trying it with Excel 2007.
I got the “HRESULT: 0×800A03EC” 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.
I got the “HRESULT: 0×800A03EC” 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.
if any one is able to fix let me know, Thank you
I’m Using your code, I get the Exception: HRESULT: 0×800A03EC, in Line :
With ptCache
.Connection = stCon
Paul: make sure the connection string has the correct path to your database.
I like your code but I am having trouble trying to change the function the data fields. I would like it to be Xlsum and not Count.
I have tried something like
.PivotFields(”Total Freight”).Function = Excel.xlconsolidationfunction = xlsum
but I get a ComException. I have changed the SQL statement to not having a groupby.
Anybody done this??
Thanks for the help.
I get the Exception: HRESULT: 0×800A03EC, in Line :
With ptCache
.Connection = stCon
like the others.
excel 2003 XP VS2005
Did anyone get past this issue?
actual error appears when setting the ptCache object and therefore before the .Connection:
Error =
” at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCache.get_ADOConnection()”
Connection : {”Not an ODBC data source or Web query”}
i changed the line to:
Dim ptCache As Excel.PivotCache = CType(xlWBook, Excel.Workbook).PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlExternal)
but still received the same error.
the structure of the strConn is:
Const stCon As String = _
“Server=xyzzz;” & _
“DataBase=Db_MyDb;” & _
“Uid=ro;”
The server “xyzzz” can be pinged ok.
i will try to redo the strConn and see if this helps.
tried all of the following styles of Conn - no resolution:
‘Const stCon As String = “Provider=sqloledb;Data Source=XXX;Initial Catalog=Dw_YYY;Integrated Security=SSPI; ” ‘OLEDB Trusted Connection
‘Const stCon As String = “Driver={SQL Server};Server=XXX;Database=Dw_YYY;Uid=ro;Pwd=; ” ‘ODBC DB Login
‘Const stCon As String = “Driver={SQL Server};Server=XXX;Database=Dw_YYY;Trusted_Connection=yes; ” ‘ODBC Trusted connection
Just use:
OLEDB;Provider=SQLOLEDB.1;Data Source=127.0.0.1;Initial Catalog=yourDB;User Id=sa;Password=;Connect Timeout=0
(Note the “OLEDB;” before your connectionstring)
I also get the Exception: HRESULT: 0×800A03EC, in Line :
With ptCache
.Connection = stCon
I want to get the OLAP cube data from Ms Analysis Service 9.0.
I use this as my connection string
Const stCon As String = _
“Provider=MSOLAP.3;Datasource=localhost; Initial Catalog=Analysis Services Project5;”
What should I do?