Visual Basic - Duda tablas dinámicas VB

Life is soft - evento anual de software empresarial

Pos: 112

Val: 14

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 clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 1.259

Duda tablas dinámicas VB

Publicado por Antoni Masana (558 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.
\\//_

VBA-Excel.zip(17,4 MB)

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 112

Val: 14

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:

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:

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:

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

Comentar

Pos: 2

Val: 1.259

Duda tablas dinámicas VB

Publicado por Antoni Masana (558 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

Comentar

Pos: 112

Val: 14

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

Comentar

Pos: 2

Val: 1.259

Duda tablas dinámicas VB

Publicado por Antoni Masana (558 intervenciones) el 26/04/2018 07:38:25

Te pongo los cambios a realizar para que el número de filas sea dinamico:

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

Comentar

Pos: 112

Val: 14

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

Comentar

Pos: 112

Val: 14

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:

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

Comentar

Pos: 2

Val: 1.259

Duda tablas dinámicas VB

Publicado por Antoni Masana (558 intervenciones) el 27/04/2018 18:20:27

Sin un ejemplo no puedo ver donde falla.

Valora esta respuesta

Comentar

Pos: 112

Val: 14

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

Comentar

Pos: 112

Val: 14

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:

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

Comentar

Necesito ayuda para adaptar una macro

¿Como puedo darle focus a un documento de word?