Hay varias formas de hacer que Excel actualice automáticamente las conexiones de datos y recalcule una hoja de trabajo. Estos trucos son más importantes con el tipo de datos Stock en Excel para clientes de Microsoft 365.
Normalmente, Excel se actualizará solo cuando cambie un valor de celda. En estos días hay situaciones en las que las celdas cambian de valor, pero Excel NO actualiza la hoja de trabajo. En otras palabras, el Excel moderno ha cambiado de una manera a la que Microsoft aún no se ha adaptado completamente.
Idealmente, Excel tendría una configuración general para actualizar la hoja de trabajo cada n segundos o minutos. Tal como está, necesitamos soluciones para que esto suceda.
Aquí hay algunas situaciones en las que es necesario forzar la actualización o el recálculo de datos o prudente.
Tipo de datos de acciones
Con el Tipo de datos de acciones, la capacidad de actualizar automáticamente es más importante. Los usuarios querrán que sus hojas de trabajo tomen el lat est precios automáticamente, algo que las versiones de vista previa actuales no pueden hacer. En lugar de tener un buen «ticker» automático, se espera que hagamos clic en «Actualizar» para obtener los precios más recientes.
Los tipos de datos de valores y datos geográficos son animales curiosos. Son conexiones de datos a fuentes externas pero NO aparecen como conexiones de datos de Excel. Eso significa que no puede configurar una actualización automática de datos, como lo haría con las conexiones de datos normales. De hecho, no hay controles expuestos para los tipos de datos Stock o Geo.
NOW () y otras funciones volátiles
La función NOW () se actualiza a la última fecha y hora siempre que Excel recalcula la hoja de trabajo. Pero si no hay nada que haga que eso suceda, Now () no cambia el valor. Se necesita algún factor externo para hacer que Excel se actualice ahora () y el resto de la hoja de trabajo. En otras palabras, debería poder echar un vistazo a una hoja de trabajo y saber que está al día, pero eso no es posible con Excel «listo para usar».
Las funciones personalizadas de VBA también se pueden etiquetar como volátiles usando esta línea en el código de la función:
Application.Volatile
Esa línea hace que la función se ejecute cada vez que Excel actualiza / recalca la hoja de trabajo.
Precaución adicional
¿Quizás desea que su hoja de trabajo se actualice automáticamente como medida de precaución? Muchos expertos en Excel recuerdan situaciones en las que Excel no se ha actualizado correctamente, por lo que les gusta el enfoque de cinturón y tirantes (al menos ocasionalmente).
Código
El método estándar de forzar automático La actualización de Excel es un breve fragmento de código VBA. Esto es lo que usamos, hay muchas variaciones sobre el mismo tema. El código completo está al final del artículo.
Hay tres funciones.
RefreshAllDataConn
realiza la actualización real de las conexiones de datos (Workbooks (ThisWorkbook.Name) .RefreshAll) y agregamos dos líneas opcionales para mostrar la última vez que se actualizó en la barra de estado inferior.
Si desea tener más cuidado, agregue una línea para forzar explícitamente el recálculo. ActiveSheet.Calculate o el extremo Application.CalculateFull (esto ralentizaría una hoja de trabajo grande, úsela con moderación).
AutoRefresh
ejecute el sub RefreshAllDataConn cada minuto o cualquier valor que establezca en la línea Application.OnTime Now + TimeValue («00:01:00»), «AutoRefresh»
Workbook_Open
una función de Excel incorporada que se ejecuta automáticamente cuando se abre la hoja de trabajo . En este caso, inicia AutoRefresh.
Solución de conexión de datos
La desventaja del enfoque VBA es que se necesita una hoja de trabajo .xlsm (hoja de trabajo de Excel habilitada para macros). Puede haber problemas para compartir archivos habilitados para macros debido a problemas de seguridad.
La llegada de PowerQuery / Get y Transform significa que hay otra forma de forzar un nuevo cálculo de la hoja de trabajo. Es una solución alternativa y no es perfecta, pero es posible y no necesita una hoja de trabajo habilitada para macros.
En resumen, asegúrese de que haya una configuración de consulta de datos con actualización automática. Si no hay una conexión de datos, agregue una pequeña a la hoja de trabajo.
Una vez que tenga una consulta de actualización automática, la hoja de trabajo, incluidas las funciones volátiles, también debería actualizarse.
Idealmente, los tipos de datos Stock y Geo también deberían actualizarse.
Cualquier consulta de datos de Excel viene con algunas opciones de actualización en las propiedades de la consulta. La mayoría de ellos están desactivados de forma predeterminada.
Actualiza cada nnn minutos; el valor predeterminado está desactivado con 60 minutos sugeridos.
Actualizar los datos al abrir el archivo
Habilitar la actualización en segundo plano
Actualizar esta conexión en Actualizar todo
La solución de actualización automática es crear una pequeña y prácticamente insignificante Conección de datos. Luego configure esa conexión de datos para actualizar cada minuto o cuando lo desee. Eso debería obligar a la hoja de trabajo a actualizarse, incluidas las funciones volátiles mencionadas anteriormente.
Algunas versiones de esta solución agregan un enlace a un pequeño archivo csv en la misma computadora. Obtenemos el mismo resultado usando una conexión de datos de una tabla en la hoja de trabajo.
Cree una tabla pequeña con una sola celda. La celda puede tener cualquier cosa, pero creamos una celda con NOW () en ella, por razones que explicaremos más adelante.
Seleccione la tabla y luego elija Datos | Obtener datos | De otras fuentes | de Tabla / Rango. El elemento exacto del menú puede ser diferente según su versión de Excel.
Cuando se abra el Editor de consultas, simplemente ciérrelo y cárguelo . En el panel de conexiones de datos, verá una consulta.
Haga clic con el botón derecho en la consulta, elija propiedades para ver la la configuración que necesitamos.
Establezca la frecuencia de actualización que más le convenga.
Para estar ordenado, mueva la tabla de origen (derecha) a la misma hoja en la consulta cargada (izquierda). Debido a que usamos NOW () en la celda de la tabla de origen, será fácil ver cuándo / si la hoja de trabajo se ha actualizado.
Ejemplo de código VBA
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Cree una lista automática de hojas de cálculo de Excel o una tabla de contenido
Solución completa de Excel NetworkDays () con días festivos & vacaciones