Procedimiento con rangos complicado

Al tratarse de la actualización de una consulta, la cosa se complica bastante.

El problema es que el evento Change no se disparará al actualizarse los datos. Sí lo hará el evento Calculate, pero el problema, si se utilizara dicho evento, es que también se dispararía como consecuencia de cualquier otro recálculo.

Pienso que la forma más segura (y posiblemente la más elegante) de hacer lo que necesitas es usar el evento AfterRefresh de la consulta, pero esto resulta algo complicado. Los pasos serían:
- sacar una copia del libro y trabajar con ella ("por si las flies")
- en el módulo del libro (ThisWorkbook) poner el siguiente código:

Dim miQT As New ClaseQT

Private Sub Workbook_Open()
miQT.InicializarEventosDeQueryTables QT:=Worksheets("Hoja1").QueryTables(1) 'Cambiar Hoja1 por el nombre real de la hoja.
End Sub

- si la hoja donde está la consulta no se llama Hoja1, hacer el cambio en el código
- crear un módulo de clase (Insertar > Módulo de clase)
- seleccionar el módulo de clase y pulsar F4
- cambiar el nombre (propiedad Name) del módulo de clase: se tiene que llamar ClaseQT
- poner el siguiente código en el módulo de clase:

Public WithEvents qtQT As QueryTable

Sub InicializarEventosDeQueryTables(QT)
Set qtQT = QT
End Sub

Private Sub qtQT_AfterRefresh(ByVal Success As Boolean)
With Worksheets(qtQT.Parent.Name)
If WorksheetFunction.CountA(.[B1:B60]) = 60 Then
If WorksheetFunction.CountA(.[C1:C60]) = 60 Then
If WorksheetFunction.CountA(.[D1:D60]) = 60 Then
MsgBox "Error: rango B1:D60 lleno"
Else
.Range("D" & WorksheetFunction.CountA(.[D1:D60]) + 1) = .[A62].Value
End If
Else
.Range("C" & WorksheetFunction.CountA(.[C1:C60]) + 1) = .[A62].Value
End If
Else
.Range("B" & WorksheetFunction.CountA(.[B1:B60]) + 1) = .[A62].Value
End If
End With
End Sub

Si guardas y cierras el libro y luego lo vuelves a abrir, cada vez que se actualice la consulta el código debería hacer lo que necesitas.
Un saludo.


Fernando Arroyo
MS MVP - Excel



"Rantamplan" escribió en el mensaje news:

"Fernando Arroyo" Escribió el día mié 20
abr 2005 12:55:01p:

> En principio no parece necesario usar un temporizador. Si he entendido
> bien, las celdas se actualizan automáticamente cada minuto. Al
> producirse dicha actualización, se debería producir también la de la
> celda que tiene la media (A62),

Correcto, la actualización se realiza de forma automática mediante una
Query con otra aplicación.

> luego entonces lo único que habría que
> hacer sería usar el evento Worksheet_change "vigilando" la celda que
> tiene la media y pasando su valor a la primera celda vacía en la
> columna B, a la C si la B ya está llena, o a la D si la C ya está
> llena.

Una puntualización:
Primero ha de rellenarse completamente la columna A (sus 60 celdas) antes
de pasar a la columna B.

Resumiendo por si no me he explicado bien:
El valor que calcula la media se actualiza cada minuto, pero como se
actualiza sobre la misma celda, se pierde el valor anterior, por eso, antes
de actualizarse el valor, se copia a otra columna (columna B) y se guardan
en dicha columna 60 valores (osea 1 hora). Cuando se han copiado esos 60
valores (ha transcurrido una hora) pasa a la columna C y se rellenan otros
60 valores y asú sucesivamente.

>
> Lo que no veo tan claro es el número de celdas implicadas. El rango
> A1:A61 tiene 61 celdas, pero sin embargo tu estás hablando de 60
> valores. ¿Quizás es que la primera fila es de títulos?.

Sip, eso no es problema, en realidad son 60 valores, asi que el rango
correcto seria A1:60
>
> Si nos das esta información (y si lo de la actualización automática es
> así), no creo que sea difícil escribir el código del evento. Un
> saludo.
>
A ver si entre todos podemos sacarle punta a esto
Muchas Gracias!!
>
> Fernando Arroyo
> MS MVP - Excel
>
>
> "Rantamplan" escribió en el mensaje
> news:
>> Rantamplan Escribió el día mié 20 abr 2005
>
>> 12:03:49p:
>> Fale, voy haciendo progresos.
>> He creado un pequeño procedimiento que copia la media ubicada en la
> celda
>> a62 a un rango que he creado y que está formado por
> Hora1,Hora2,Hora3:
>>
>> Dim as integer,j as integer
>> Dim rango as range
>> Dima valor as variant
>> valor = hoja2.range("a62").value
>> set rango = hoja2.range("b1:b61")
>> for i=1 to 60
>> for j=1 to 3
>> rango.cells(i,j) = valor
>> next j
>> next i
>>
>> Pero claro, ese codigo me copia en todo el rango el mismo valor, y lo
> que
>> yo quiero es que se copie en cada celda del rango el valor que le
>> corresponda :-(
>> Tal vez tenga que utilizar un Timer para que controle el tiempo y
> copie
>> cada minuto donde debe...
>>
>> A ver si algun iluminado me ayuda please!!
>

"Fernando Arroyo" Escribió el día mié 20
abr 2005 04:51:41p:

Al tratarse de la actualización de una consulta, la cosa se complica
bastante.

El problema es que el evento Change no se disparará al actualizarse
los datos. Sí lo hará el evento Calculate, pero el problema, si se
utilizara dicho evento, es que también se dispararía como consecuencia
de cualquier otro recálculo.

Pienso que la forma más segura (y posiblemente la más elegante) de
hacer lo que necesitas es usar el evento AfterRefresh de la consulta,
pero esto resulta algo complicado. Los pasos serían:
- sacar una copia del libro y trabajar con ella ("por si las flies")
- en el módulo del libro (ThisWorkbook) poner el siguiente código:

Dim miQT As New ClaseQT

Private Sub Workbook_Open()
miQT.InicializarEventosDeQueryTables
QT:=Worksheets("Hoja1").QueryTables(1) 'Cambiar Hoja1 por el
nombre real de la hoja.
End Sub

- si la hoja donde está la consulta no se llama Hoja1, hacer el
cambio en el código - crear un módulo de clase (Insertar > Módulo de
clase) - seleccionar el módulo de clase y pulsar F4
- cambiar el nombre (propiedad Name) del módulo de clase: se tiene
que llamar ClaseQT - poner el siguiente código en el módulo de clase:

Public WithEvents qtQT As QueryTable

Sub InicializarEventosDeQueryTables(QT)
Set qtQT = QT
End Sub

Private Sub qtQT_AfterRefresh(ByVal Success As Boolean)
With Worksheets(qtQT.Parent.Name)
If WorksheetFunction.CountA(.[B1:B60]) = 60 Then
If WorksheetFunction.CountA(.[C1:C60]) = 60 Then
If WorksheetFunction.CountA(.[D1:D60]) = 60 Then
MsgBox "Error: rango B1:D60 lleno"
Else
.Range("D" & WorksheetFunction.CountA(.[D1:D60]) +
1) = .[A62].Value
End If
Else
.Range("C" & WorksheetFunction.CountA(.[C1:C60]) + 1)
= .[A62].Value
End If
Else
.Range("B" & WorksheetFunction.CountA(.[B1:B60]) + 1) > .[A62].Value
End If
End With
End Sub

Si guardas y cierras el libro y luego lo vuelves a abrir, cada vez que
se actualice la consulta el código debería hacer lo que necesitas. Un
saludo.


Fernando Arroyo
MS MVP - Excel



"Rantamplan" escribió en el mensaje
news:

"Fernando Arroyo" Escribió el día

mié 20

abr 2005 12:55:01p:

> En principio no parece necesario usar un temporizador. Si he

entendido

> bien, las celdas se actualizan automáticamente cada minuto. Al
> producirse dicha actualización, se debería producir también la

de la

> celda que tiene la media (A62),

Correcto, la actualización se realiza de forma automática mediante

una

Query con otra aplicación.

> luego entonces lo único que habría que
> hacer sería usar el evento Worksheet_change "vigilando" la celda

que

> tiene la media y pasando su valor a la primera celda vacía en la
> columna B, a la C si la B ya está llena, o a la D si la C ya

está

> llena.

Una puntualización:
Primero ha de rellenarse completamente la columna A (sus 60 celdas)

antes

de pasar a la columna B.

Resumiendo por si no me he explicado bien:
El valor que calcula la media se actualiza cada minuto, pero como se
actualiza sobre la misma celda, se pierde el valor anterior, por eso,

antes

de actualizarse el valor, se copia a otra columna (columna B) y se

guardan

en dicha columna 60 valores (osea 1 hora). Cuando se han copiado esos

60

valores (ha transcurrido una hora) pasa a la columna C y se rellenan

otros

60 valores y asú sucesivamente.

>
> Lo que no veo tan claro es el número de celdas implicadas. El

rango

> A1:A61 tiene 61 celdas, pero sin embargo tu estás hablando de 60
> valores. ¿Quizás es que la primera fila es de títulos?.

Sip, eso no es problema, en realidad son 60 valores, asi que el rango
correcto seria A1:60
>
> Si nos das esta información (y si lo de la actualización

automática es

> así), no creo que sea difícil escribir el código del evento.

Un

> saludo.
>
A ver si entre todos podemos sacarle punta a esto
Muchas Gracias!!
>
> Fernando Arroyo
> MS MVP - Excel
>
>
> "Rantamplan" escribió en el mensaje
> news:
>> Rantamplan Escribió el día mié 20 abr

2005

>
>> 12:03:49p:
>> Fale, voy haciendo progresos.
>> He creado un pequeño procedimiento que copia la media ubicada en

la

> celda
>> a62 a un rango que he creado y que está formado por
> Hora1,Hora2,Hora3:
>>
>> Dim as integer,j as integer
>> Dim rango as range
>> Dima valor as variant
>> valor = hoja2.range("a62").value
>> set rango = hoja2.range("b1:b61")
>> for i=1 to 60
>> for j=1 to 3
>> rango.cells(i,j) = valor
>> next j
>> next i
>>
>> Pero claro, ese codigo me copia en todo el rango el mismo valor, y

lo

> que
>> yo quiero es que se copie en cada celda del rango el valor que le
>> corresponda :-(
>> Tal vez tenga que utilizar un Timer para que controle el tiempo y
> copie
>> cada minuto donde debe...
>>
>> A ver si algun iluminado me ayuda please!!
>

Ok, muchisismas gracias por el codigo, voy a probarlo y te cuento...
El problema que le veo (corrigeme si me equivoco) es que se tiene que
cerrar y volver a abrir el libro para que se actualicen los datos y la
hoja esta no puede cerrarse!.
La hoja es crítica ya que va recogiendo los datos de un autómata
constántemente y supondría la pérdida de datos en ese pequeño lapsus de
tiempo :-(

Salu2!.

Ok, muchisismas gracias por el codigo, voy a probarlo y te cuento...
El problema que le veo (corrigeme si me equivoco) es que se tiene que
cerrar y volver a abrir el libro para que se actualicen los datos y la
hoja esta no puede cerrarse!.
La hoja es crítica ya que va recogiendo los datos de un autómata
constántemente y supondría la pérdida de datos en ese pequeño lapsus de
tiempo :-(

Salu2!.

No, eso no es así. Lo único que hay que hacer es abrir el libro una sola vez, para que se ejecute el código que hay en el evento Workbook_Open, y, de hecho, bastaría con ejecutar dicho código a mano (una sola vez) para que los eventos de la consulta quedaran activados.

Una vez ejecutado dicho código, el evento AfterRefresh de la consulta seguirá disparándose indefinidamente cada vez que se actualice la consulta.
Un saludo.


Fernando Arroyo
MS MVP - Excel

"Fernando Arroyo" Escribió el día mié 20
abr 2005 05:55:18p:

Ok, muchisismas gracias por el codigo, voy a probarlo y te cuento...
El problema que le veo (corrigeme si me equivoco) es que se tiene que
cerrar y volver a abrir el libro para que se actualicen los datos y
la

hoja esta no puede cerrarse!.
La hoja es crítica ya que va recogiendo los datos de un autómata
constántemente y supondría la pérdida de datos en ese pequeño

lapsus de

tiempo :-(

Salu2!.

No, eso no es así. Lo único que hay que hacer es abrir el libro una
sola vez, para que se ejecute el código que hay en el evento
Workbook_Open, y, de hecho, bastaría con ejecutar dicho código a mano
(una sola vez) para que los eventos de la consulta quedaran activados.

Una vez ejecutado dicho código, el evento AfterRefresh de la consulta
seguirá disparándose indefinidamente cada vez que se actualice la
consulta. Un saludo.


Fernando Arroyo
MS MVP - Excel

Al abrir el libro me salta un error de VB:
'Se ha producido el error '9' en tiempo de ejecución:
Subindice fuera del intervalo
Si le doy a depurar me salta al procedimiento Workbook_Open, resaltandome
en amarillo la linea de codigo que contiene.

Salu2!.

"Rantamplan" escribió en el mensaje news:

"Fernando Arroyo" Escribió el día mié 20
abr 2005 05:55:18p:

Ok, muchisismas gracias por el codigo, voy a probarlo y te cuento...
El problema que le veo (corrigeme si me equivoco) es que se tiene que
cerrar y volver a abrir el libro para que se actualicen los datos y
la

hoja esta no puede cerrarse!.
La hoja es crítica ya que va recogiendo los datos de un autómata
constántemente y supondría la pérdida de datos en ese pequeño

lapsus de

tiempo :-(

Salu2!.

No, eso no es así. Lo único que hay que hacer es abrir el libro una
sola vez, para que se ejecute el código que hay en el evento
Workbook_Open, y, de hecho, bastaría con ejecutar dicho código a mano
(una sola vez) para que los eventos de la consulta quedaran activados.

Una vez ejecutado dicho código, el evento AfterRefresh de la consulta
seguirá disparándose indefinidamente cada vez que se actualice la
consulta. Un saludo.


Fernando Arroyo
MS MVP - Excel

Al abrir el libro me salta un error de VB:
'Se ha producido el error '9' en tiempo de ejecución:
Subindice fuera del intervalo
Si le doy a depurar me salta al procedimiento Workbook_Open, resaltandome
en amarillo la linea de codigo que contiene.

Salu2!.

¿Pero entonces los datos no los obtienes mediante una consulta? Me refiero a una creada desde Datos > Obtener datos externos > Nueva consulta de base de datos. Yo pensaba que estábamos hablando de una consulta de este tipo en la que se había establecido que se actualizara cada minuto. Si se trata de otra cosa, el código no te va a servir.
Un saludo.


Fernando Arroyo
MS MVP - Excel