Forums Últimos mensajes - Powered by IBM
 
Tags Palabras claves

problemas con Programacion de tablas dinamicas

21/07/2006 - 01:10 por Cristian | Informe spam
Ayuda Hacer una pregunta
Hola a todos:

Mi pregunta es sobre tablas dinamicas, especificamente la programacion
de ellas con vba. El tema es que el orden de los elementos de la coleccion
columnitems y rowitems miembros de pivotcell (que a su vez es miembro de
range), no es el mismo que el orden de las colecciones columnfields y
rowfields del objeto pivottable.

Bueno el problema especifico es el siguiente: "crear un codigo que sea
capaz de identificar los criterios (los elementos de columna, fila y pagina)
de la celda activa y filtre el rango base de la tabla dinamica de acuerdo a
esos criterios... similar a lo que ocurre cuando esta seleccionada una celda
en una tabla dinamica y se le da doble clic (mostrar detalles) ahi se crea
una nueva hoja que nuestra los registros que constituyen el detalle de celda
actual, pero destaco mi idea ES FILTRAR la lista original, no obtener una
"copia" como lo hace el comando que detalle a anteriormente.

Para realizar lo anterior cree el siguiente codigo, pero como dije en un
principio el orden de las colecciones no es el mismo. es decir, cuando el
usuario reordena los campos de filas o columnas, la coleccion columnitems y
rowitems se "ordena", pero las colecciones columnfields y rowfields se
mantienen tal cual (en el orden en que fueron agregados los campos)... en
definitiva aqui esta parte del codigo usado:

Dim rng As Range
origen = ActiveCell.PivotTable.SourceData
p = InStr(1, origen, "!")
hoja = Left(origen, p - 1)
Rango = Mid(origen, p + 1)
Rango = Replace(Rango, "F", "R")
w = Application.ConvertFormula(Rango, xlR1C1, xlA1, True)

Set rng = Range(hoja & "!" & w)

If rng.Worksheet.FilterMode Then rng.Worksheet.ShowAllData
For C = 1 To ActiveCell.PivotCell.ColumnItems.Count
cmpo = ActiveCell.PivotTable.ColumnFields(C)
cmpov = ActiveCell.PivotCell.ColumnItems(C)
rng.AutoFilter Application.Match(cmpo, rng.Rows(1), 0), cmpov
Next C

For R = 1 To ActiveCell.PivotCell.RowItems.Count
cmpo = ActiveCell.PivotTable.RowFields(R)
cmpov = ActiveCell.PivotCell.RowItems(R)
rng.AutoFilter Application.Match(cmpo, rng.Rows(1), 0), cmpov
Next R
rng.Worksheet.Activate

bueno como veran si lo pares de datos no corresponden la lista filtrada
arroja cero registros, pues estoy filtrando un campo con el valor de otro...

Si alguien tiene alguna idea de como hacer esto se lo agrazco desde ya

Saludos, CRISTIAN
email Siga el debateRespuesta 8 respuestasRespuesta Tengo una respuesta

Preguntas similare

Mostrar todos los temas similares

Leer las respuestas

#6 Héctor Miguel
22/07/2006 - 08:19 | Informe spam
hola, Cristian !

... sobre tablas dinamicas... programacion... con vba. El tema es
... el orden de los elementos de la coleccion columnitems y rowitems miembros de pivotcell (que a su vez es miembro de range)
... no es el mismo que el orden de las colecciones columnfields y rowfields del objeto pivottable.
... "crear un codigo que sea capaz de identificar los criterios (los elementos de columna, fila y pagina) de la celda activa
... y filtre el rango base de la tabla dinamica de acuerdo a esos criterios... similar a lo que ocurre cuando... en una tabla dinamica
... se le da doble clic (mostrar detalles)... crea una nueva hoja que muestra los registros que constituyen el detalle de celda actual
... pero destaco mi idea ES FILTRAR la lista original, no obtener una "copia" como lo hace el comando que detalle a anteriormente...



1) si 'planeas' continuar con el uso de 'pivotcell' -seguramente- perderas 'compatibilidad' hacia versiones donde no tenga 'soporte' -?-
2) la 'sustitucion' que haces de 'F' por 'R' [F de Fila <-> por R de Row] pudiera requerir de 'L' [de Linea] en lugar de 'F' [de Fila]
[segun versiones donde se ejecute el codigo e incluso del idioma instalado del excel]
3) el siguiente codigo [que NO tiene prevencion/correccion de -posibles- errores, como que la celda activa no sea del area de datos]..
'asume' lo siguiente [segun los datos que planteas en la consulta]...
a) UNA tabla que se encuentra en hoja distinta de la que contiene los datos de origen [pero]... en el mismo libro
b) la TD contiene: un campo de pagina... un campo de fila... y un campo de columna [independientemente de su 'orden']

corre [algunas] pruebas, y revisa los autofiltros aplicados en la hoja y rango con los datos 'de origen' de la TD ;)
si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

Sub Filtra_origen_segun_celda_TD()
Application.ScreenUpdating = False
Dim FLR As String, Origen As String, Hoja As String, Rango As String, Titulos As String, _
CampoPag As String, CampoFila As String, CampoCol As String, _
FiltroPag As Byte, FiltroFila As Byte, FiltroCol As Byte, _
ColFiltroTD As String, FilaFiltroTD As Integer, _
CriterioPag As String, CriterioFila As String, CriterioCol As String
FLR = Application.International(xlUpperCaseRowLetter)
With ActiveCell.Parent.PivotTables(1)
Origen = .PivotCache.SourceData
CampoPag = .PageFields(1).Name
CampoFila = .RowFields(1).Name
CampoCol = .ColumnFields(1).Name
ColFiltroTD = Mid(.RowRange.Address, 2, InStr(2, .RowRange.Address, "$") - 2)
FilaFiltroTD = CByte(Mid(.ColumnRange.Cells(1).Address, InStr(2, .ColumnRange.Cells(1).Address, "$"))) + 1
Hoja = IIf(InStr(Origen, "!") > 0, Left(Origen, InStr(Origen, "!") - 1), .Parent.Name)
CriterioPag = .PageFields(1).CurrentPage
With ActiveCell
CriterioFila = Range(ColFiltroTD & .Row).End(xlDown).End(xlUp)
CriterioCol = Cells(FilaFiltroTD, .Column)
End With
With Application: Rango = .ConvertFormula( _
.Substitute(Mid(Origen, InStr(Origen, "!") + 1), FLR, "R"), xlR1C1, xlA1)
End With
Titulos = Range(Rango).Resize(1).Address
End With
With Worksheets(Hoja)
If .AutoFilterMode Then .AutoFilterMode = False
FiltroPag = Application.Match(CampoPag, .Range(Titulos), 0)
FiltroFila = Application.Match(CampoFila, .Range(Titulos), 0)
FiltroCol = Application.Match(CampoCol, .Range(Titulos), 0)
With .Range(Rango)
If CriterioPag <> "(All)" Then _
.AutoFilter Field:=FiltroPag, Criteria1:=CriterioPag
.AutoFilter Field:=FiltroFila, Criteria1:=CriterioFila
.AutoFilter Field:=FiltroCol, Criteria1:=CriterioCol
End With
End With
End Sub
Respuesta Responder a este mensaje
#7 Cristian
22/07/2006 - 18:08 | Informe spam
Gracias por tu respuesta Hector, tu codigo al igual que el mio funciona
bien cuando solo existe 1 elemento de fila y 1 de columna (el tuyo tambien
considera la pagina activa), pero tambien tiene problemas cuando el numero
de elementos de fila o columna es mayor a 1... tal vez se le podrian hacer
algunas modificaciones para que funcionara con mas elementos, si esto es
posible agradeceria me comentaras como hacerlo.


Saludos, CRISTIAN




"Héctor Miguel" escribió en el mensaje
news:
| hola, Cristian !
|
| > ... sobre tablas dinamicas... programacion... con vba. El tema es
| > ... el orden de los elementos de la coleccion columnitems y rowitems
miembros de pivotcell (que a su vez es miembro de range)
| > ... no es el mismo que el orden de las colecciones columnfields y
rowfields del objeto pivottable.
| > ... "crear un codigo que sea capaz de identificar los criterios (los
elementos de columna, fila y pagina) de la celda activa
| > ... y filtre el rango base de la tabla dinamica de acuerdo a esos
criterios... similar a lo que ocurre cuando... en una tabla dinamica
| > ... se le da doble clic (mostrar detalles)... crea una nueva hoja que
muestra los registros que constituyen el detalle de celda actual
| > ... pero destaco mi idea ES FILTRAR la lista original, no obtener una
"copia" como lo hace el comando que detalle a anteriormente...
|
| 1) si 'planeas' continuar con el uso de 'pivotcell' -seguramente- perderas
'compatibilidad' hacia versiones donde no tenga 'soporte' -?-
| 2) la 'sustitucion' que haces de 'F' por 'R' [F de Fila <-> por R de Row]
pudiera requerir de 'L' [de Linea] en lugar de 'F' [de Fila]
| [segun versiones donde se ejecute el codigo e incluso del idioma
instalado del excel]
| 3) el siguiente codigo [que NO tiene prevencion/correccion de -posibles-
errores, como que la celda activa no sea del area de datos]..
| 'asume' lo siguiente [segun los datos que planteas en la consulta]...
| a) UNA tabla que se encuentra en hoja distinta de la que contiene los
datos de origen [pero]... en el mismo libro
| b) la TD contiene: un campo de pagina... un campo de fila... y un campo
de columna [independientemente de su 'orden']
|
| corre [algunas] pruebas, y revisa los autofiltros aplicados en la hoja y
rango con los datos 'de origen' de la TD ;)
| si cualquier duda [o informacion adicional]... comentas ?
| saludos,
| hector.
|
| Sub Filtra_origen_segun_celda_TD()
| Application.ScreenUpdating = False
| Dim FLR As String, Origen As String, Hoja As String, Rango As String,
Titulos As String, _
| CampoPag As String, CampoFila As String, CampoCol As String, _
| FiltroPag As Byte, FiltroFila As Byte, FiltroCol As Byte, _
| ColFiltroTD As String, FilaFiltroTD As Integer, _
| CriterioPag As String, CriterioFila As String, CriterioCol As
String
| FLR = Application.International(xlUpperCaseRowLetter)
| With ActiveCell.Parent.PivotTables(1)
| Origen = .PivotCache.SourceData
| CampoPag = .PageFields(1).Name
| CampoFila = .RowFields(1).Name
| CampoCol = .ColumnFields(1).Name
| ColFiltroTD = Mid(.RowRange.Address, 2, InStr(2, .RowRange.Address,
"$") - 2)
| FilaFiltroTD = CByte(Mid(.ColumnRange.Cells(1).Address, InStr(2,
.ColumnRange.Cells(1).Address, "$"))) + 1
| Hoja = IIf(InStr(Origen, "!") > 0, Left(Origen, InStr(Origen, "!") -
1), .Parent.Name)
| CriterioPag = .PageFields(1).CurrentPage
| With ActiveCell
| CriterioFila = Range(ColFiltroTD & .Row).End(xlDown).End(xlUp)
| CriterioCol = Cells(FilaFiltroTD, .Column)
| End With
| With Application: Rango = .ConvertFormula( _
| .Substitute(Mid(Origen, InStr(Origen, "!") + 1), FLR, "R"),
xlR1C1, xlA1)
| End With
| Titulos = Range(Rango).Resize(1).Address
| End With
| With Worksheets(Hoja)
| If .AutoFilterMode Then .AutoFilterMode = False
| FiltroPag = Application.Match(CampoPag, .Range(Titulos), 0)
| FiltroFila = Application.Match(CampoFila, .Range(Titulos), 0)
| FiltroCol = Application.Match(CampoCol, .Range(Titulos), 0)
| With .Range(Rango)
| If CriterioPag <> "(All)" Then _
| .AutoFilter Field:=FiltroPag, Criteria1:=CriterioPag
| .AutoFilter Field:=FiltroFila, Criteria1:=CriterioFila
| .AutoFilter Field:=FiltroCol, Criteria1:=CriterioCol
| End With
| End With
| End Sub
|
|
Respuesta Responder a este mensaje
#8 Cristian
22/07/2006 - 18:08 | Informe spam
Gracias por tu respuesta Hector, tu codigo al igual que el mio funciona
bien cuando solo existe 1 elemento de fila y 1 de columna (el tuyo tambien
considera la pagina activa), pero tambien tiene problemas cuando el numero
de elementos de fila o columna es mayor a 1... tal vez se le podrian hacer
algunas modificaciones para que funcionara con mas elementos, si esto es
posible agradeceria me comentaras como hacerlo.


Saludos, CRISTIAN




"Héctor Miguel" escribió en el mensaje
news:
| hola, Cristian !
|
| > ... sobre tablas dinamicas... programacion... con vba. El tema es
| > ... el orden de los elementos de la coleccion columnitems y rowitems
miembros de pivotcell (que a su vez es miembro de range)
| > ... no es el mismo que el orden de las colecciones columnfields y
rowfields del objeto pivottable.
| > ... "crear un codigo que sea capaz de identificar los criterios (los
elementos de columna, fila y pagina) de la celda activa
| > ... y filtre el rango base de la tabla dinamica de acuerdo a esos
criterios... similar a lo que ocurre cuando... en una tabla dinamica
| > ... se le da doble clic (mostrar detalles)... crea una nueva hoja que
muestra los registros que constituyen el detalle de celda actual
| > ... pero destaco mi idea ES FILTRAR la lista original, no obtener una
"copia" como lo hace el comando que detalle a anteriormente...
|
| 1) si 'planeas' continuar con el uso de 'pivotcell' -seguramente- perderas
'compatibilidad' hacia versiones donde no tenga 'soporte' -?-
| 2) la 'sustitucion' que haces de 'F' por 'R' [F de Fila <-> por R de Row]
pudiera requerir de 'L' [de Linea] en lugar de 'F' [de Fila]
| [segun versiones donde se ejecute el codigo e incluso del idioma
instalado del excel]
| 3) el siguiente codigo [que NO tiene prevencion/correccion de -posibles-
errores, como que la celda activa no sea del area de datos]..
| 'asume' lo siguiente [segun los datos que planteas en la consulta]...
| a) UNA tabla que se encuentra en hoja distinta de la que contiene los
datos de origen [pero]... en el mismo libro
| b) la TD contiene: un campo de pagina... un campo de fila... y un campo
de columna [independientemente de su 'orden']
|
| corre [algunas] pruebas, y revisa los autofiltros aplicados en la hoja y
rango con los datos 'de origen' de la TD ;)
| si cualquier duda [o informacion adicional]... comentas ?
| saludos,
| hector.
|
| Sub Filtra_origen_segun_celda_TD()
| Application.ScreenUpdating = False
| Dim FLR As String, Origen As String, Hoja As String, Rango As String,
Titulos As String, _
| CampoPag As String, CampoFila As String, CampoCol As String, _
| FiltroPag As Byte, FiltroFila As Byte, FiltroCol As Byte, _
| ColFiltroTD As String, FilaFiltroTD As Integer, _
| CriterioPag As String, CriterioFila As String, CriterioCol As
String
| FLR = Application.International(xlUpperCaseRowLetter)
| With ActiveCell.Parent.PivotTables(1)
| Origen = .PivotCache.SourceData
| CampoPag = .PageFields(1).Name
| CampoFila = .RowFields(1).Name
| CampoCol = .ColumnFields(1).Name
| ColFiltroTD = Mid(.RowRange.Address, 2, InStr(2, .RowRange.Address,
"$") - 2)
| FilaFiltroTD = CByte(Mid(.ColumnRange.Cells(1).Address, InStr(2,
.ColumnRange.Cells(1).Address, "$"))) + 1
| Hoja = IIf(InStr(Origen, "!") > 0, Left(Origen, InStr(Origen, "!") -
1), .Parent.Name)
| CriterioPag = .PageFields(1).CurrentPage
| With ActiveCell
| CriterioFila = Range(ColFiltroTD & .Row).End(xlDown).End(xlUp)
| CriterioCol = Cells(FilaFiltroTD, .Column)
| End With
| With Application: Rango = .ConvertFormula( _
| .Substitute(Mid(Origen, InStr(Origen, "!") + 1), FLR, "R"),
xlR1C1, xlA1)
| End With
| Titulos = Range(Rango).Resize(1).Address
| End With
| With Worksheets(Hoja)
| If .AutoFilterMode Then .AutoFilterMode = False
| FiltroPag = Application.Match(CampoPag, .Range(Titulos), 0)
| FiltroFila = Application.Match(CampoFila, .Range(Titulos), 0)
| FiltroCol = Application.Match(CampoCol, .Range(Titulos), 0)
| With .Range(Rango)
| If CriterioPag <> "(All)" Then _
| .AutoFilter Field:=FiltroPag, Criteria1:=CriterioPag
| .AutoFilter Field:=FiltroFila, Criteria1:=CriterioFila
| .AutoFilter Field:=FiltroCol, Criteria1:=CriterioCol
| End With
| End With
| End Sub
|
|
email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una pregunta AnteriorRespuesta Tengo una respuesta
Search Busqueda sugerida