30 thoughts on “Create Pivottable-reports with VB.NET”

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

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

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

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

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

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

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

8. please help .. automating pivot table with VBA causes WRONG DATA!!

   mike
   212-622-0545

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

10. I’m Using your code, I get the Exception: HRESULT: 0×800A03EC, in Line :
    With ptCache
    .Connection = stCon

11. Can you please submit the C# version of this code

    Thanks in Advance
    Peter

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

13. I am also having problems with .connection = stcon.

    I am trying to connect to sql server. Please help

14. I got the “HRESULT: 0×800A03EC” too. But I was trying it with Excel 2007.

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

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

17. I’m Using your code, I get the Exception: HRESULT: 0×800A03EC, in Line :
    With ptCache
    .Connection = stCon

18. Paul: make sure the connection string has the correct path to your database.

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

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

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

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

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

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

25. I am geeting the error “Exception from HRESULT: 0×800A03EC” near connection=stcon.
    Can anyone please help me out

26. Pls Help:
    Im receiving this message : Unable to set the Orientation property of the PivotField class

    Dim xlApp As Excel.Application

    Dim stSql As String = “select areaDescription, regionDescription, districtDescription, outletCode, materialCode, periodMonth, periodYear, SMBXCount, sttlastyearactualvolume from salesvolumefacts”
    ‘ Data Source works fine just not indicated

    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(“A2?)

    ‘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(“areaDescription”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“regionDescription”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“districtDescription”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“outletCode”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“materialCode”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“periodMonth”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“periodYear”).Orientation = Excel.XlPivotFieldOrientation.xlRowField
    .PivotFields(“SMBXCount”).Orientation = Excel.XlPivotFieldOrientation.xlDataField
    .PivotFields(“sttlastyearactualvolume”).Orientation = Excel.XlPivotFieldOrientation.xlDataField
    .Format(Excel.XlPivotFormatType.xlReport2)
    End With

    Thanks, MJ

27. I am trying to convert your code to work with an excel worksheet of data without success – are you able to post an example using excel data please.

    Many thanks

28. Ayuda porfavor:
    recibi el siguiente error al ejecutar el codigo de ejemplo para la tabla pivote

    No se puede convertir el objeto COM del tipo ‘System.__ComObject’ al tipo de interfaz ‘Microsoft.Office.Interop.Excel.Application’. Ocurrió un error de operación debido a que la llamada QueryInterface en el componente COM para la interfaz con IID ‘{000208D5-0000-0000-C000-000000000046}’ generó el siguiente error: Biblioteca no registrada. (Excepción de HRESULT: 0×8002801D (TYPE_E_LIBNOTREGISTERED)).

    el siguiente es el codigo de mi objeto:

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Const conectabd As String = “data source = (local); initial catalog = Northwind; user id = sa; password = Administrador01?

    Const consulta As String = “SELECT * FROM Categories”

    ‘Dim AppExcel As Excel.Application
    Dim AppExcel As Excel.Application
    Try
    ‘Grab a running instance of Excel.
    ERROR1 —> AppExcel = Marshal.GetActiveObject(“Excel.Application”)–> en esta linea me marca error y si lo cambio por la siguiente linea Me marca el mismo error ern la linea q tiene la etiqueta ERROR2
    ‘ AppExcel = New Excel.Application
    Catch ex As COMException
    ‘If no instance exist then create a new one.
    AppExcel = New Excel.Application
    MessageBox.Show(“ese creo una nueva instancia por que no existia ” & ex.Message)
    End Try

    ERROR2 —> Dim libro As Excel.Workbook = AppExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)

    Dim hoja As Excel.Worksheet = DirectCast(libro.Worksheets(1), Excel.Worksheet)
    Dim rango As Excel.Range = DirectCast(hoja, Excel.Worksheet).Range(“B2?)

    ‘Create the Pivotcache.
    Dim ptCache As Excel.PivotCache = libro.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlExternal)

    ‘Setup the Pivotcache.
    With ptCache
    .Connection = conectabd
    .CommandText = consulta
    .CommandType = Excel.XlCmdType.xlCmdSql
    End With

    ‘Create the Pivottable.
    Dim ptTable As Excel.PivotTable = _
    hoja.PivotTables.Add( _
    PivotCache:=ptCache, _
    TableDestination:=rango, _
    TableName:=”Tablapivotegenerada”)

    ‘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

    libro.SaveAs(“c:EXCELReportEGENERADO.xls”)

    ‘Switch to Excel.
    With AppExcel
    .Visible = True
    .UserControl = True
    End With

    ‘Tell the Garbage Collector that these objects are ready to be destroyed.
    ptTable = Nothing
    ptCache = Nothing
    hoja = Nothing
    libro = Nothing
    AppExcel = Nothing
    End Sub

29. Hi ,

    Hi i have developed in similar manner ….

    but place stSQL = “EXEC Storedprocedure”

    AT the Pivottable add point my debugcursor in vanising …..I am not able to find out the cause….but when place the output of the storedprocedure in to some table and written stSQL =”Select * from tbl” it is executing…………

    Please let me know wats happening

    Thaks lot…

30. hi,

    i am using vb.net. and i am creating pivot table programatically and i want to add calculation field “perentofcolumn” in that .
    when i added this in program. i got an error message (“unable to set the function property of the pivot field class”)

    if anybody please send me the solution for the same..

    thanks ..

Posting code? Use <pre> tags for VBA and <code> tags for inline.