Visual Basic - Duda tablas dinámicas VB

Life is soft - evento anual de software empresarial
 
Vista:
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 24/04/2018 21:37:02
Muy buenas tardes estimados! Les cuento, me tienen de practicante de apoyo en área comercial y todos sus reportes los bajan de una plataforma online como archivos excel. He estado intentando crear una macro o un código de vb que me permita obtener la información que quiero sin tener que hacer tablas dinámicas y formulas una y otra vez.
Soy nuevo en el mundo del VB pero por lo menos tengo una idea de como funciona la programación, ya que tuve los cursos de plan común de ing civil (progra, bases de datos y un ramo practico q uno ambos). Lamentablemente se me ha hecho mas difícil de los que pensaba y agradecería un montón la ayuda de alguno de ustedes.

Lo que necesito automatizar es lo siguiente:

Una vez descargado el reporte de excel necesito que el código cree una tabla dinámica de rango dinámico (ya que los reportes nunca tienen la misma cantidad de filas.. las columnas no varían)

Luego necesito introducir dentro de la tabla los campos correspondientes, pero me conformo con saber el codigo para crear una tabla dinámica en base a un rango dinámico. Luego me podré enfocar en como agregar los campos a la tabla.

Agradecería enormemente algún ejemplo de como crear el código q necesito o por lo menos alguna guia realmente buena, ya que los vídeos en youtube han sido inútiles.

Saludos y muchas gracias de antemano!
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Antoni Masana
Val: 1.259
Plata
Ha mantenido su posición en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Antoni Masana (560 intervenciones) el 25/04/2018 07:37:17
Te adjunto varios documentos para macros y otras cosas de Excel.

Una solución rápida a tu problema es GRABAR MACRO realizar las acciones que quieres automatizar.
Si errores, si te equivocas tendrás que repetir todo el proceso. Parar la macro y volver a empezar.
Al finalizar para la macro y estudia lo que hay escrito.
Ahora solo tendrás que modificar aquellas cosas que puedan variar como son los rangos.dinámicos.

Este es el primer paso que yo haría, cuando lo tengas hablamos del segundo.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 25/04/2018 18:10:14
Estimado Antoni, muchas gracias por tu pronta respuesta! Me diste un muy buen consejo. Ya cree la tabla dinámica y al ver el código creado, entiendo varias lineas pero a la vez hay algunas entradas que no tengo idea de que hacen exactamente o si realmente son necesarias para el código.

El código es el siguiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub TD()
'
' TD Macro
'
' Acceso directo: CTRL+k
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "srirmam!R2C1:R12112C13", Version:=6).CreatePivotTable TableDestination:= _
        "Hoja1!R3C1", TableName:="TablaDinámica2", DefaultVersion:=6
    Sheets("Hoja1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("TablaDinámica2")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("TablaDinámica2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("TablaDinámica2").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("TablaDinámica2").PivotFields("RUT VERIFICADO")
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub




Lineas de código que no entiendo o q necesito cambiar:

1
2
3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "srirmam!R2C1:R12112C13", Version:=6).CreatePivotTable TableDestination:= _
        "Hoja1!R3C1", TableName:="TablaDinámica2", DefaultVersion:=6
--> Necesito que me tome los datos desde A2 hasta la ultima fila existente (variable en cada informe), en vez de que me tome hasta un rango determinado (R12112C13). El numero de columnas se va a mantener constante, por lo que no habría que cambiar el C13

En lo que sigue de código, voy a escribir lo que entendí que hace cada linea o lo dejo con un signo de interrogación si simplemente no se qué es lo que hace:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
With ActiveSheet.PivotTables("TablaDinámica2")
        .ColumnGrand = True -->Totales de clumna
        .HasAutoFormat = True  -->autoformato
        .DisplayErrorString = False  --> No mostrar errores de string
        .DisplayNullString = True --> mostrar campos nulos de string
        .EnableDrilldown = True -->?
        .ErrorString = ""  --> ?
        .MergeLabels = False --> que no una las labels
        .NullString = "" --> ?
        .PageFieldOrder = 2 --> ?
        .PageFieldWrapCount = 0  ---> ?
        .PreserveFormatting = True  --> Preservar formato?
        .RowGrand = True  -> totales de file
        .SaveData = True  --> guardar data
        .PrintTitles = False  --> mostrar titulos
        .RepeatItemsOnEachPrintedPage = True  --> repetir items en cada pagina impresa
        .TotalsAnnotation = False  --> ?
        .CompactRowIndent = 1 --> ?
        .InGridDropZones = False  --> ?
        .DisplayFieldCaptions = True   -->?
        .DisplayMemberPropertyTooltips = False -->?
        .DisplayContextTooltips = True  -->?
        .ShowDrillIndicators = True --> ?
        .PrintDrillIndicators = False --> mostrar el código anterior
        .AllowMultipleFilters = False  --> Dejar poner filtros multiples
        .SortUsingCustomLists = True --> ?
        .FieldListSortAscending = False --> ?
        .ShowValuesRow = False --> ?
        .CalculatedMembersInFilters = False --> ?
        .RowAxisLayout xlCompactRow --> ?


Hay bastantes lineas de códigos que no entiendo que hacen exactamente, me imagino que hay muchas lineas que son innecesarias y que perfectamente se pueden eliminar. Ojala alguien pueda darme una solución para tomar el rango de la tabla dinámica con filas no constantes y ojalá poder completar qué hace específicamente cada linea de código que tiene un signo de interrogación a su lado.

Saludos y muchas gracias de antemano!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 1.259
Plata
Ha mantenido su posición en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Antoni Masana (560 intervenciones) el 25/04/2018 19:33:35
Tendría que ver el libro y ejecutar paso a paso la macro para saber que hace.
Todas las opciones que indicas que quizás sobran es relativo: cuando cambias un parámetro dentro de un grupo de ellos, la macro te los escribe todos con sus valores por defecto.
Prueba a cambiar el tamaño, el color o poner en negrita una celda y veras la cantidad de opciones que tiene y tu solo has tocado una.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 25/04/2018 20:28:47
Estimado Anotni, lo único que hice fue crear la tabla dinamica con todos los datos de la hoja "srirmam". Lo que necesito ahora es que al momento de crear la tabla dinamica, no tome un rango fijo, sino que ocupe la ultima fila disponible...

Como se puede observar en el código, el rango está fijo en la fila 12.112 y necesito que tome la ultima fila con contenido en vez de que esté fijo.

Sabes como podría solucionar esto?

De antemano, muchas gracias.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 1.259
Plata
Ha mantenido su posición en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Antoni Masana (560 intervenciones) el 26/04/2018 07:38:25
Te pongo los cambios a realizar para que el número de filas sea dinamico:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub TD()
    Dim Ultima As Long
 
    Ultima = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:="srirmam!R2C1:R" & Ultima & "C13", _
                                      Version:=6).CreatePivotTable _
                   TableDestination:="Hoja1!R3C1", _
                   TableName:="TablaDinámica2", _
                   DefaultVersion:=6
 
    Sheets("Hoja1").Select
    Cells(3, 1).Select

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
2
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 26/04/2018 21:51:03
Crack!! Muchísimas gracias Antoni, hace exactamente lo que quería, ahora puedo seguir programando arriba de este para las otras acciones mas sencillas.

GRACIAS!
Atte.
Javier del Campo
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 27/04/2018 15:49:09
Estimado Antoni, ojala todavía estés por estos lugares jaja

Antes de crear la tabla dinámica, en la hoja de los datos tengo q poner dos columnas adicionales con formulas para verificar si los ruts fueron rechazados o aceptados. logré agregar el titulo de columna y agregar las formulas a las celdas correspondientes pero no me funciona hacer el autocompletado hasta la ultima celda con contenido que no sea rango fijo (ojala poder hacerlo de la misma manera que tu, contando las filas y definendo como "Ultima")

Así los estoy intentando y marqué donde falla el codigo:

1
2
3
4
5
6
7
8
9
10
11
12
Ultima = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("N2").Select
ActiveCell.FormulaR1C1 = "Aceptado"
    Range("O2").Select
ActiveCell.FormulaR1C1 = "Rechazado"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""aceptado"",1,0)"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""rechazado"",1,0)"
Range("N3:O3").Select
    Selection.AutoFill Destination:=Range("N3:N" & Ultima)              'Aquí me aparece el error... 
    Range("N3:N" & Ultima).Select

Ojala puedas ayudarme al igual que antes, ya que tus soluciones son perfectas y sin códigos con lineas de más.
Quedo atento.
De antemano, muchisimas gracias!
Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 02/05/2018 18:01:58
No importa Antoni, de todas formas, muchas gracias. Te aviso si aparece otra duda :)

Saludos!!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 14
Ha aumentado su posición en 6 puestos en Visual Basic (en relación al último mes)
Gráfica de Visual Basic

Duda tablas dinámicas VB

Publicado por Javier dc (8 intervenciones) el 02/05/2018 21:01:01
Estimado Antoni, ya queda poco para terminar lo que estoy intentando hacer.. Ahora no entiendo cómo poner un rango dinámico dentro de una formula. El código va de a sgte manera:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Sub TD()
Dim Ultima As Long
 
    Ultima = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("N2").Select
ActiveCell.FormulaR1C1 = "Aceptado"
    Range("O2").Select
ActiveCell.FormulaR1C1 = "Rechazado"
Range("N3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""aceptado"",1,0)"
Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=""rechazado"",1,0)"
Range("N3").Select
    Selection.AutoFill Destination:=Range("N3:N" & Ultima)
 
Range("O3").Select
    Selection.AutoFill Destination:=Range("O3:O" & Ultima)
 
 
 
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:="srirmam!R2C1:R" & Ultima & "C15", _
                                      Version:=6).CreatePivotTable _
                   TableDestination:="Hoja1!R1C1", _
                   TableName:="TablaDinámica2", _
                   DefaultVersion:=6
    Sheets("Hoja1").Select
    Cells(3, 1).Select
 
 
    Sheets("Hoja1").Select
    ActiveSheet.Name = "Base Rut"
ActiveSheet.PivotTables("TablaDinámica2").AddFields RowFields:="RUT VERIFICADO"
ActiveSheet.PivotTables("TablaDinámica2").AddDataField ActiveSheet.PivotTables( _
        "TablaDinámica2").PivotFields("aceptado"), "Suma de Aceptado", xlCount
ActiveSheet.PivotTables("TablaDinámica2").AddDataField ActiveSheet.PivotTables( _
        "TablaDinámica2").PivotFields("rechazado"), "Suma de Rechazado", xlCount
ActiveSheet.PivotTables("TablaDinámica2").AddDataField ActiveSheet.PivotTables( _
        "TablaDinámica2").PivotFields("TRANSACCION"), "Cuenta de TRANSACCION", xlCount
 
 
Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:="srirmam!R2C1:R" & Ultima & "C15", _
                                      Version:=6).CreatePivotTable _
                   TableDestination:="Hoja2!R1C1", _
                   TableName:="TablaDinámica3", _
                   DefaultVersion:=6
Sheets("Hoja2").Select
    Cells(3, 1).Select
Sheets("Hoja2").Select
    ActiveSheet.Name = "Base puntos"
 
With ActiveSheet.PivotTables("TablaDinámica3").PivotFields("NOMBRE PC")
        .Orientation = xlRowField
        .Position = 1
    End With
 
 
ActiveSheet.PivotTables("TablaDinámica3").AddDataField ActiveSheet.PivotTables( _
        "TablaDinámica3").PivotFields("TRANSACCION"), "Cuenta de TRANSACCION", xlCount
 
 
 With ActiveSheet.PivotTables("TablaDinámica3").PivotFields( _
        "RESULTADO VERIFICACION")
        .Orientation = xlColumnField
        .Position = 1
    End With
Sheets.Add After:=ActiveSheet
    Sheets("Hoja3").Select
    Sheets("Hoja3").Name = "resumen"
    ActiveCell.FormulaR1C1 = "Métrica"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Resultado"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "N° de puntos con actividad"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "N° de transacciones"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "RUN verificados"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "RUN aceptados"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "RUN rechazados"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "RUN con mas de 10 aceptado"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "RUN aprobados al primero intento"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "RUN aprobados con menos de 3 rechazos"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "RUN aprobados con al menos 3 rechazos previos"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "Numero de intentos por RUN verificado"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "N° de RUN"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "N° de firmas promedio por RUN"
    Columns("B:B").Select
    Columns("A:A").EntireColumn.AutoFit
End Sub

El código me crea las tablas dinámicas que quería y ahora necesito rellenar la ultima tabla creada a mano. Mi problema es que no se como definir rangos variables (o dinámicos) para una función.

Necesito que en la celda B2 de la hoja "resumen" se ponga la formula: =+CONTAR.SI('Base puntos'!A3:A397;"*") pero con rango variable, no con rango fijo. (del A3 hasta la penúltima o última fila con datos(ya que la ultima es de totales)). Estaba pensando en ocupar el mismo codigo para contar las filas pero no me está funcionando...

Se agradecería un montón si me puedes ayudar un poco.
De antemano, muchas gracias
Atte,
JDC
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar