se importan las dll, se toma como base la informacion que se encuentra en un archivo excel, esto puede variar de hecho la creacion del archivo la hice tambien en .net pero para efectos de la creacion de la tabla dinamica tome desde aki para que fuera mas rapido y no tuviera que estar creando el archivo cada vez que queria crear la tabla, asi que lo primero es contar con la informacion.
Código
Imports System.IO Imports Microsoft.Office.Interop Imports System.Diagnostics Imports System.Runtime.InteropServices Public Sub Dinamic3() Dim xlapp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlSheet1 As Excel.Worksheet Dim PRange As Excel.Range Dim FinalRow As Long Dim FinalCol As Long Try ' Modify the File Name as necessary xlapp.Workbooks.Open("C:\Informe Diario\InforDiario-20101111.xls") xlBook = xlapp.ActiveWorkbook() xlSheet1 = xlapp.ActiveSheet() FinalRow = xlSheet1.Cells(xlSheet1.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row FinalCol = xlSheet1.Cells(1, xlSheet1.Columns.Count).End(Excel.XlDirection.xlToLeft).Column PRange = xlSheet1.Cells(1, 1).Resize(FinalRow, FinalCol) 'Creo una hoja y la coloco al inicio xlSheet = xlapp.Worksheets.Add(Before:=xlapp.Worksheets("Datos")) 'xlSheet.Visible = True xlSheet.Activate() xlSheet.Name = "Tablaaaa" xlBook.ActiveSheet.Cells(3, 1).Select() 'creo la Tabla Dinamica xlBook.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, SourceData:="Datos!R1C1:R65536C10", _ Version:=Excel.XlPivotTableVersionList.xlPivotTableVersion10).CreatePivotTable _ (TableDestination:=xlSheet.Range("A1:D25"), TableName:="PivotTable1", _ DefaultVersion:=Excel.XlPivotTableVersionList.xlPivotTableVersion10) With xlSheet.PivotTables("PivotTable1").PivotFields("Convenio") .Orientation = Excel.XlPivotFieldOrientation.xlPageField .Position = 1 End With With xlSheet.PivotTables("PivotTable1").PivotFields("Servicio") .Orientation = Excel.XlPivotFieldOrientation.xlPageField .Position = 1 End With With xlSheet.PivotTables("PivotTable1").PivotFields("Etapa") .Orientation = Excel.XlPivotFieldOrientation.xlRowField .Position = 1 End With xlSheet.PageSetup.Zoom = 90 xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _ ("PivotTable1").PivotFields("Valor"), _ "Cuenta de Valor", Excel.XlConsolidationFunction.xlCount) xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _ ("PivotTable1").PivotFields("Dias"), _ "Cuenta de Dias", Excel.XlConsolidationFunction.xlCount) With xlSheet.PivotTables("PivotTable1").DataPivotField .Orientation = Excel.XlPivotFieldOrientation.xlColumnField .Position = 1 End With With xlSheet.PivotTables("PivotTable1").PivotFields("Cuenta de Dias") .Caption = "Promedio de Dias" .Function = Excel.XlConsolidationFunction.xlAverage .NumberFormat = "0" End With xlSheet.PivotTables("PivotTable1").AddDataField(xlSheet.PivotTables _ ("PivotTable1").PivotFields("Valor"), _ "Cuenta de Valor2", Excel.XlConsolidationFunction.xlCount) With xlSheet.PivotTables("PivotTable1").PivotFields("Cuenta de Valor2") .Caption = "Suma de Valorrrr" .Function = Excel.XlConsolidationFunction.xlSum .NumberFormat = "$ #.##0" End With Dim pvi As Excel.PivotItem With xlSheet.PivotTables("PivotTable1").PivotFields("Etapa") For Each pvi In .PivotItems If pvi.Name = "11 Exportacion" Or _ pvi.Name = "12 Espera RIPS" Or _ pvi.Name = "13 Transmitir" Or _ pvi.Name = "14 Impresion" Or _ pvi.Name = "15 Entregado a Armado" Or _ pvi.Name = "16 Esperar Radicación" Or _ pvi.Name = "17 Radicacion" Or _ pvi.Name = "18 Radicacion" Or _ pvi.Name = "19 FCI Pendiente" Or _ pvi.Name = "20 FCI Anulación" Then pvi.Visible = False End If Next End With xlBook.ShowPivotTableFieldList = False xlSheet.SaveAs("C:\Informe Diario\InforDiario-201011rafafa.xls", _ Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7) xlapp.Workbooks.Close() xlSheet = Nothing xlBook = Nothing If Not xlapp Is Nothing Then xlapp.Quit() xlapp = Nothing End If 'Mato todos los procesos de excel. Try For Each vl_objProcess As Process In System.Diagnostics.Process.GetProcesses() If vl_objProcess.ProcessName = "EXCEL.EXE" Or _ vl_objProcess.ProcessName = "EXCEL" Then vl_objProcess.Kill() End If Next Catch ex As Exception End Try MessageBox.Show("Listo") Catch ex As Exception MessageBox.Show(ex.ToString) End Try End Sub
espero les alla servido