lunes, 10 de febrero de 2014

VBA: Repetir una macro cada x tiempo.

Hace algún tiempo publiqué un par de entradas donde aplicabamos el método .OnTime, en una de ellas mostrábamos lo más esencial de este método (ejemplo1) y en el segundo algo más elaborado, donde completábamos un cronómetro (ejemplo2).
En la entrada de hoy, dando respuesta a un lector, mostraremos algo interesante (parecido al ejemplo del cronómetro), y es la forma en cómo poder repetir con una carencia de tiempo definida una macro o rutina.
La idea entonces es clara, queremos automatizar la repetición controlada de una macro cada x segundos, minutos u horas. Para ello crearemos tres procesos Sub:
1- macro que inicie todo el proceso, y que además controle el final de las repeticiones
2- macro que de por finalizado el método .OnTime
3- por supuesto, nuestra macro principal, la que queremos se ejecute cada x tiempo.


Estas son nuestras macros que añadiremos nuestro código asociándolo a un Módulo; para ello accederemos a la ventana de código del explorador del editor de VBA, donde insertaremos el siguiente código VBA:

'declaración de variables para todo el módulo
Dim tiempo As Date
Dim contador As Integer

Sub IniciaOnTime()
'controla la repetición y la cadencia
Dim allea As Integer
'repetición cada 2 segundos
tiempo = Now + TimeSerial(0, 0, 2)
'llama a esta misma macro en el tiempo estipulado
Application.OnTime tiempo, "IniciaOnTime"
'controlamos el final de la repetición
contador = contador + 1
'mientras el contador sea menor a 6 ejecutamos la rutina principal
'cuando lleguemos a 6 interrumpimos la repetición
If contador < 6 Then
    Run "MacroPrincipal"
Else
    Run "CancelaOnTime"
End If
End Sub

Sub CancelaOnTime()
'ejecuta el final de la repetición y reinicia el contador
    Application.OnTime tiempo, "IniciaOnTime", , False
    contador = 0
End Sub

Sub MacroPrincipal()
'el código de la rutina a repetir
'cualquier procedimiento....
With Range("B2")
    .Value = contador & " - " & Now
    Randomize
    allea = Int(20 * Rnd)
    If allea < 5 Then
        .Interior.Color = vbRed
        .Font.Color = vbYellow
    End If
    If allea >= 5 And allea < 10 Then
        .Interior.Color = vbGreen
        .Font.Color = vbBlack
    End If
    If allea >= 10 And allea < 20 Then
        .Interior.Color = vbBlue
        .Font.Color = vbWhite
    End If
End With
End Sub

Algo fundamental es incluir algún control que finalice las reiteraciones, ya que de lo contrario nunca finalizará y tendremos que pararlo manualmente entrnado en el editor. En el ejemplo el control se ha realizado con un 'contador' que ejecuta y para el método .Ontime.. cuando contador llegue a 6 se lanza la macro CancelaOntime. Otra manera, si nos interesa que la repetición esté operativa mientras el libro esté abierto es asociar el inicio y fin a un evento WorkBook_Open y otro WorkBook_Close. Este sería el resultado visible:

VBA: Repetir una macro cada x tiempo.

26 comentarios:

  1. Oye una duda, para una condicionante se puede hacer una macro entre cierto intervalo de tiempo??
    te explico mejor, necesito una macro que me verifique la hora y si esta entre 6 y 6:30 y también entre 18 y 18:30 me deje ejecutar otra macro y si no solo me salga un mensaje!!
    es que solo lo he podido hacer pero con una hora, no con un intervalo de tiempo
    me podrías ayudar!!!

    ResponderEliminar
    Respuestas
    1. Hola Luis, que tal estás!?
      podrías emplear un condicional IF..THEN, algo así:
      IF Now()>=timeserial(6,0,0) and Now()<=timeserial(6,30,0) or Now()>=timeserial(18,0,0) and Now()<=timeserial(18,30,0) THEN
      ....

      combinándolo con OnTime.
      Saludos

      Eliminar
  2. hola, mi consulta es simple, tengo un excel que se conecta a una base de datos lo que necesito es actualizar este cada hora, es simple ejecutar el comando actualizar de la macro cada una hora a partir de que se abre la planilla, lo que no logro decodificar en que parte tengo que cambiar tu codigo para que haga esto y en que parte tengo que poner el cogido de mi macro para que lo ejecute?

    ResponderEliminar
    Respuestas
    1. Hola,
      si l oque quieres es actualizar la conexión, lo más sencillo es que entres en las propiedades de la Conexión y configures la actualización en el intervalo que desees.. basta con eso, y es mucho más sencillo que meterte en líos de macros recurrentes.
      Saludos

      Eliminar
    2. muchas gracias no sabia eso y estaba tratando de resolverlo con una macro
      al parecer es bastante simple. voy a intentarlo y te comento

      Eliminar
    3. solo me resta un detalle y es aqui donde necesito generar un timer que cada 1 hora me actualise, tengo dos tablas dinamicas que se rellenan de las hojas de datos que traigo de la coneccion, lo que necesito es hacer es ejecutar el comando ActiveWorkbook.RefreshAll cada una hora para que en dichas tablas se refleje el resultado de actualizar y asi pueda seguir con otras tareas sin recurri a hacer el trabajo manualmente como lo hago hasta ahora con un boton actualizar que tiene este comando en su codigo

      Eliminar
    4. Una forma sencilla sería añadir en la ventana de código de la hoja donde se encuentre la Conexión de datos el código:
      Private Sub Worksheet_Calculate()
      Sheets("Tabla dinámica").PivotTables("Tabla dinámica 1").RefreshTable
      ' o bien ActiveWorkbook.RefreshAll
      End Sub

      Este método es mejor que usar un temporizador recurrente que está empleando recursos constantemente.
      Saludos

      Eliminar
    5. gracias ismael por la respuesta, entiendo lo de poner el comando, lo que no me queda claro es como hago para que al iniciar mis tarear yo abro el libro de excel y hago que se ejecute el comando cada 1 hora desde que comienzo a trabajar, asi de esta manera mientras estoy con otras tareas se ejecuta solo y cuando vuelvo a ver la planilla esta actualizada y la tabla con los datos cargados.
      yo tomo tu ejemplo y estoy tratando de adaptarlo pero es mas simple con esto que me decis lo que no termino de entender es como logro hacer que ser repita la accion cada una hora.

      Eliminar
    6. Hola,
      subiré una explicación al blog
      ;-)
      Saludos

      Eliminar
    7. gracias ismael, después me dejas el link así lo reviso.

      Eliminar
    8. ismael hice lo siguiente....
      Private Sub Workbook_Open()
      Application.OnTime TimeValue("09:00:00"), "RECARGAR"
      Application.OnTime TimeValue("10:00:00"), "RECARGAR"
      .....
      Application.OnTime TimeValue("18:45:00"), "RECARGAR"
      Application.OnTime TimeValue("19:10:00"), "RECARGAR"
      End Sub
      Private Sub Workbook_close()

      End Sub

      Sub RECARGAR()
      Dim hora As String
      ActiveWorkbook.RefreshAll
      hora = Time
      MsgBox ("ACTUALIZACION COMPLETA " & hora)
      End Sub

      asi al menos funciona pero tengo una duda en el cierre del archivo debo detener el Application.OnTime o se ejecuta en cada horario y se detiene solo.

      Eliminar
    9. Hola,
      revisa lo expuesto en este post... no es necesario marcar las horas concretas en las que lanzar el procedimiento RECARGAR.
      En todo caso, si lo haces así no hace falta hacer nada más.. se jecuta en esas horas y ya está, puesto que no existe bucle.
      Si estás atento el día 20 de octubre, publicaré el caso resuelto.
      Slds

      Eliminar
    10. GRACIAS ISMAEL POR LA ASISTENCIA ESTARE ATENTO. SALUDOS

      Eliminar
  3. Hola, tengo una macro para hacer que un archivo en Excel se abra a una hora específica y que ejecute otra macro que contiene ese archivo. Utilizo el siguiente código y me funciona perfectamente. La duda me surge porque no quiero se active siempre a la misma hora y cada vez que quiero cambiar la hora tengo que editar el código e introducirlo manualmente. ¿Existe alguna manera de hacerlo mediante un cuadro de dialogo tipo InputBox?
    He hecho una prueba con un InputBox (abajo del todo podéis verlo) pero no me funciona. No me da ningún tipo de error pero cuando llega la hora indicada no hace nada, sé que algo está mal pero no sé el que.
    Gracias!
    -------------------------------
    Sub Cronometro()
    'Orden para iniciar un cronómetro y cuando llegue a la hora indicada, abrir un archivo Excel, la orden Call sirve para llamar a otra macro
    EarlTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarlTime, " Cronometro "
    If Time = CDate("8:25:00") Then Workbooks.Open "C:\Documents\PDH\macros_V4.xlsm": Call Ejecutar_Macro_Excel
    End Sub
    --------------------------------------
    Sub Ejecutar_Macro_Excel()
    'Orden para ejecutar una macro en un archivo Excel abierto
    Windows("macros_V4.xlsm").Activate
    Application.Run "'macros_V4.xlsm'!FECHA_HORA"
    End Sub

    Prueba fallida con InputBox
    Sub Dialogo()
    Dim Val As String
    Val = InputBox("A qué hora te vas", "Ejemplo", "", 1200, 1400): Call CronometroII
    End Sub
    -----------------------------------
    Sub CronometroII ()
    EarlTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime EarlTime, " CronometroII "
    If Time = "& Val" Then Workbooks.Open " C:\Documents\PDH\macros_V4.xlsm": Call Ejecutar_Macro_Excel
    End Sub

    ResponderEliminar
    Respuestas
    1. DIFICILMENTE ACTUALIZE LAS VARIABLES, ya que el excel no modifica el codigo de visual luego de la apertura, cualquier cambio que hagas en el codigo pos ejecucion del archivo, deberas cerrar el archivo de excel para que recargue el codigo nuevamente. de echo en mis prubeas me pasaba que modificaba el codigo con el archivo abierto y no corria el nuevo codigo hasta tanto no cerraba el archivo y lo volvia a abrir.

      Eliminar
    2. Hola Daniel,
      si pretendes abrir un fichero Excel tienes varias alternativas...
      obviamente desde algún fichero Excel ya abierto, o bien incluso desde las tareas de windows:
      http://excelforo.blogspot.com.es/2013/06/vba-ejecutar-una-macro-de-excel-de-un.html
      No existe, como indica el lector anterior, la forma de modificar un código de una macro desde otra macro...
      la alternativa del inputbox requeriría, obviamente la acción humana sobre ella.

      En cuanto a tu cuestión del inputbox, tendrías que declara como publica la variable, para que se pueda emplear en cualquier otro procedimiento (hazlo en las primera líneas de la ventana de código NO dentro de los procedimientos!

      Saludos

      Eliminar
  4. por otra parte la idea de crear variables que se modifiquen no estan mal solo que deberias asignarle el valor para trabajar al iniciar el archivo en el evento de carga del archivo...creo que ahi si lo asignara y correra luego.

    ResponderEliminar
  5. Tengo una macro que me elimina las filas en donde hay datos repetidos de un columna especifica, pero deseo que se ejecute cada un segundo automáticamente sin que yo oprima un botón ni un comando nada, simplemente que después de que yo abra el archivo este me ejecute dicha macro por si sola cada 1 segundo.

    este es el código de la macro:

    Sub repetidos()

    Range("A2").Select
    Do While Not IsEmpty(ActiveCell)
    x = WorksheetFunction.CountIf(Range("A:A"), ActiveCell)
    If x > 1 Then
    ActiveCell.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop
    Range("A1").Select
    End Sub

    ResponderEliminar
    Respuestas
    1. Hola,
      en principio solo deberías seguir los pasos indicados en este mismo post, empleando el método ontime..
      Sub IniciaOnTime()
      tiempo = Now + TimeSerial(0, 0, 1)
      Application.OnTime tiempo, "IniciaOnTime"
      Run "repetidos"
      End Sub

      No olvides cerrar el proceso OnTime!

      saludos

      Eliminar
  6. Esta muy bueno , pero no se si se puede aplicar a mi caso.

    En realidad lo que quiero hacer es que el archivos actualice todas las tablas (incluyendo los orígenes de datos externos) cada 20 mins, sin necesidad que el archivo este abierto.

    Alguien me podría ayudar con este caso o un ejemplo parecido.

    Mil gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      ¿sin el archivo abierto?, pues salvo que sobre ese libro tengas vínculos de terceros libros, no comprendo el sentido...
      En todo caso, si quieres forzar la actualización tendrás que abrir el fichero, actualizar, y si es el caso, luego cerrarlo...

      Para esto tendrás que tener un libro de Excel abierto o bien lanzar el proceso desde las tareas de windows:
      http://excelforo.blogspot.com.es/2013/06/vba-ejecutar-una-macro-de-excel-de-un.html

      Saludos

      Eliminar
  7. hola tengo un problema hago correr la macro y funciona de maravilla el problema es que si abro otro libro esta falla y me bota error 9 :(

    ResponderEliminar
    Respuestas
    1. Hola,
      al abrir varios libros y activar el 'otro' puede ser que entre en confusión e intente lanzar y ejecutar acciones concretas que no tengan sentido en ese segundo libro (y sí lo tenían en el primero).
      La solución es añadir a todas las líneas que lo necesiten la especificación del Workbook donde trabajar
      Workbook("Libro1").Range(...
      tambiénn en el Run
      etc...

      Saludos

      Eliminar
    2. muy buena camarada funciona de maravilla muchas gracias por la atención :)

      Eliminar
  8. perdón pero me equivoque la solución fue usando la aplicación de window().active ya con eso jalo nomas lo dejo trabajando en segundo plano y ya no afecta a los oros libros

    ResponderEliminar