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