vba - sheet - thisworkbook name

Espere hasta que ActiveWorkbook.RefreshTodas las terminaciones-VBA

Question

Tengo un sub que llama a ActiveWorkbook.RefreshAll para traer nuevos datos desde un origen XML y luego realiza múltiples modificaciones en él. El problema es que no se da suficiente tiempo para que termine el comando RefreshAll, por lo que los subs y funciones siguientes no se ejecutan correctamente, lo que hace que las filas repetidas no se borren correctamente.

He intentado usar Application.Wait y la función Sleep , pero parecen pausar el proceso de actualización también. Simplemente quiero que el resto del código espere hasta que finalice el proceso de actualización antes de ejecutar el resto del código.

¿Alguna idea sobre cómo implementar esto? En este momento, solo pude solucionarlo al no invocar RefreshAll, lo que me da la idea de implementar un segundo flujo para que se ejecute posteriormente, pero esa no es una buena solución.

Por favor, avíseme si algo de esto no estaba claro. Gracias

EDITAR Así que intenté algunas sugerencias de las publicaciones a continuación, y esto es lo que pude idear. Hacer una "macro de registro" y luego DESACTIVAR el "Habilitar actualización de fondo" en las propiedades de la tabla no resultó en nada. Hice una actualización también después. Este fue el resultado de la macro grabada:

With ActiveWorkbook.Connections("XMLTable")
        .Name = "XMLTable"
        .Description = ""
End With
ActiveWorkbook.Connections("XMLTable").refresh

La clase ActiveWorkbook.Connections NO tiene una opción BackgroundQuery para que pueda establecerlo en False. ¿Algunas ideas?

Solo para aclarar. Este es un archivo XML alojado en un sitio web que Excel va e importa en una tabla. Luego llamo a esos datos en un pivote y otras cosas. El objetivo aquí es permitir que el proceso de importación desde el sitio web hasta la mesa finalice ANTES de ejecutar cualquier otro comando. Gracias

EDIT2: Después de un poco más de investigación, he encontrado esta página: http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html Parece que un tipo de conexión XML no tiene un booleano BackgroundQuery . Esa opción solo está disponible para las conexiones ODBC y OLEDB, que son los tipos xlConnectionTypeODBC y xlConnectionTypeOLEDB, respectivamente. La conexión XML que estoy usando es del tipo xlConnectionTypeXMLMAP que no tiene una opción BackgroundQuery . ¿Alguien tiene alguna idea de a dónde ir desde aquí? La única solución que tengo en mente ahora es hacer dos botones macro separados en la hoja de Excel, uno para actualizar y otro para la modificación de datos, pero prefiero mantener esa opción hasta el final.

Answer 1

Aquí hay una solución que se encuentra en http://www.mrexcel.com/forum/excel-questions/510011-fails-activeworkbook-refreshall-backgroundquery-%3Dfalse.html :

Ya sea que todas las propiedades de búsqueda en segundo plano de los pivotcaches estén configuradas en False o que pasen por todos los pivotcaches del libro de trabajo:

Code:
    For Each pc In ActiveWorkbook.PivotCaches
       pc.BackgroundQuery = False
       pc.Refresh
    Next 

esto dejará todas las propiedades backgroundquery de los pivotcaches como falsas. Puede conservar la configuración de cada uno con:

Código:

For Each pc In ActiveWorkbook.PivotCaches
  originalBGStatus = pc.BackgroundQuery
  pc.BackgroundQuery = False
  pc.Refresh
  pc.BackgroundQuery = originalBGStatus
Next

Answer 2

Aunque @Wayne G. Dunn ha dado el código. Aquí está el lugar cuando no quieres codificar. Y desmarque para deshabilitar la actualización de fondo.

Answer 3

Como comentamos:

''~~> your code here before refresh

Activeworkbook.RefreshAll
DoEvents

''~~> rest of your code here after refresh

Lo que hace DoEvents es permitir momentáneamente que Windows se tome un descanso de Macro para procesar todos los eventos pendientes antes de regresar a la macro.
Entonces, cuando actualice su libro de trabajo y ponga DoEvents después, no reanudará la macro hasta que termine la actualización.

Answer 4

Debe desactivar la "actualización de fondo" para todas las consultas. Si la actualización de fondo está activada, Excel funciona mientras se realiza la actualización y usted tiene problemas.

Datos> Conexiones> Propiedades> (desmarcar) habilitar la actualización de fondo

Answer 5

Estaba teniendo el mismo problema e intenté todas las soluciones anteriores sin éxito. Finalmente resolvió el problema eliminando toda la consulta y creando una nueva. Tenía exactamente la misma configuración que la que no funcionaba (literalmente, la misma definición de consulta que simplemente copié la anterior). No tengo idea de por qué esto resolvió el problema, pero lo hizo. Espero que esto ayude a alguien.

Answer 6

Esto puede no ser ideal, pero intente usar "Application.OnTime" para pausar la ejecución del código restante hasta que haya transcurrido el tiempo suficiente para asegurar que todos los procesos de actualización hayan finalizado.

¿Qué ocurre si la última tabla de la lista de actualización es una tabla falsa que solo consta de un indicador que indica que la actualización se ha completado? Esta tabla se eliminará al comienzo del procedimiento, y luego, con "Application.OnTime", se ejecutará un Sub cada 15 segundos o más para verificar si la tabla falsa se ha rellenado. Si se completa, cese el verificador "Application.OnTime" y continúe con el resto del procedimiento.

Un poco inestable, pero debería funcionar.

Answer 7

Prueba esto:

ActiveSheet.Calculate

Lo uso en una hoja de trabajo en la que el botón de control cambia los valores de un conjunto de datos. En cada clic, Excel ejecuta este comando y el gráfico se actualiza inmediatamente.

Answer 8

Si no está casado con el uso de Excel Web Query, puede intentar abrir la URL como un Libro de trabajo por separado. Seguir esa ruta le permite trabajar en los datos resultantes una vez que se completa la solicitud web, como si desactivara "Activar actualización de fondo".

Sin embargo, lo bueno es que Excel muestra una barra de progreso durante la solicitud, en lugar de simplemente congelar / mostrar un mensaje de carga en la celda de destino.

Vea mi respuesta a esta pregunta: ¿cómo puedo procesar los datos de una consulta web de Excel una vez que la consulta está completa?

La desventaja de este enfoque es que debe gestionar el procesamiento de los datos que recibe usted mismo: Excel no lo colocará en un destino determinado para usted.

Terminamos yendo por esta ruta después de que probamos algo muy similar a lo que parece haber estado haciendo.

Answer 9

Tuve el mismo problema, sin embargo DoEvents no me ayudó porque mis conexiones de datos tenían activada la actualización de fondo. En cambio, usando la respuesta de Wayne G. Dunn como punto de partida, creé la siguiente solución, que funciona muy bien para mí;

Sub Refresh_All_Data_Connections()

    For Each objConnection In ThisWorkbook.Connections
        ''Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        ''Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False

        ''Refresh this connection
        objConnection.Refresh

        ''Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next

    MsgBox "Finished refreshing all data connections"

End Sub

El MsgBox es solo para pruebas y se puede eliminar una vez que esté contento de que el código espere.

Además, prefiero ThisWorkbook a ActiveWorkbook porque sé que se orientará al libro de trabajo donde reside el código, en caso de que cambie el foco. Nueve de cada diez veces esto no importará, pero me gusta pecar de cauteloso.

EDITAR: Acabo de ver su edición sobre el uso de una conexión xlConnectionTypeXMLMAP que no tiene una opción BackgroundQuery, lo siento. Dejaré lo anterior para que cualquiera (como yo) busque una forma de actualizar los tipos de conexión OLEDB.

Answer 10

DESCARGO DE RESPONSABILIDAD: ¡Según se informa, el código de abajo causó algunos bloqueos! Use con cuidado.

de acuerdo con ESTA respuesta en Excel 2010 y versiones posteriores CalculateUntilAsyncQueriesDone detiene las macros hasta que se complete la actualización
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone