Jump to content

Archived

This topic is now archived and is closed to further replies.

Manuel Gómez

Ralentización durante la actualización de datos desde SQL

Recommended Posts

Buenas.

Tengo una conexión a un SQL que descarga unas 150.000 filas a una tabla de Excel. Al actualizar datos los descarga y coloca en menos de un segundo. 

Después he añadido una segunda hoja en el mismo libro para dar formato y colocar los datos como necesita el departamento. Para hacerlo he utilizado indice-coincidir en cada celda (unas 75.000 celdas).

El problema es que cuando inicio la actualización de datos desde SQL continuamente se estan actualizando todas las casillas indice-coincidir en lugar de esperar a que finalice la descarga de datos.

He probado:

Application.Calculation = False
Application.EnableEvents = False
Application.ScreenUpdating = False
 
Antes de actualizar los datos de la conexión,  pero las fórmulas con indice-coincidir siguen actualizándose durante la descarga, lo que ralentiza enormemente la actualización de datos. 
¿Cómo podría evitar que se actualizaran las celdas indice-coincidir durante la actualización de la conexión?
 
Gracias  y saludos 

Share this post


Link to post
Share on other sites
Guest Cacho R

Hola! Manuel. Sube al Foro una muestra reducida de tu libro. Por ejemplo:

- Una primer hoja con no más de 100 filas de datos (en lugar de las 150 mil). Por cierto: en sí los datos no interesan.
- Y una segunda hoja con no más de 20 filas con fórmulas (en lugar de las 75 mil).

Lo que más interesa es ver TODAS las columnas que empleas, las fórmulas y la disposición de las fórmulas en tu segunda hoja.

Saludos, Cacho R.

Share this post


Link to post
Share on other sites

Buenos días Cacho R.

Gracias por tu respuesta. Tienes razón, una imagen vale más que mil palabras. Adjunto un excel reducido.

El problema lo tengo al actualizar los datos que importo desde la conexión al SQL ya sea utilizando el menú de Excel como una subrutina de VBA.

Si elimino la hoja "Informe" donde están todas las fórmulas "Indice-Coincidir" la actualización de datos se hace inmediatamente.

 

Datos 2018 v0.9.xlsm

Share this post


Link to post
Share on other sites

Buenos días sailepaty.

Muchas gracias por tu respuesta.

La conexión la tengo definida en el libro de Excel (no la creo por VBA) y tengo desactivadas las opciones:

  • Habilitar actualización en segundo plano.
  • Actualizar cada ..
  • Actualizar al abrir el archivo.

He intentado añadir la opción que me has dicho en la cadena de conexión, pero no me lo guarda.

He puesto un breakpoint antes de realizar la conexión a la base de datos y en las inspecciones he revisado el parámetro

ThisWorkbook.Connections("NombreDeLaConexion").ODBCConnection.BackgroundQuery
Que estaba en False.

Se te ocurre que más podría probar?

Share this post


Link to post
Share on other sites
Guest Cacho R
Hace 1 hora, Manuel Gómez dijo:

Si elimino la hoja "Informe" donde están todas las fórmulas "Indice-Coincidir" la actualización de datos se hace inmediatamente.

¡Ésa es la idea!... O sea:

Reemplaza:

    ActiveWorkbook.Connections("Conexion Infor").Refresh

por:

    With Sheets("Informe")
      .Range(.[a4].End(xlToRight), .[a2].End(xlDown)).ClearContents
      ActiveWorkbook.Connections("Conexion Infor").Refresh
      .Range(.[a3].End(xlToRight), .[a2].End(xlDown)).FillDown
    End With

En resumen: al momento de actualizar la conexión -solamente- dejas las fórmulas de la fila 3. Tras la actualización vuelves a poner las fórmulas tomando a las de la fila 3 como modelo.

Share this post


Link to post
Share on other sites
Hace 18 horas, Cacho R dijo:

¡Ésa es la idea!... O sea:

Reemplaza:


    ActiveWorkbook.Connections("Conexion Infor").Refresh

por:


    With Sheets("Informe")
      .Range(.[a4].End(xlToRight), .[a2].End(xlDown)).ClearContents
      ActiveWorkbook.Connections("Conexion Infor").Refresh
      .Range(.[a3].End(xlToRight), .[a2].End(xlDown)).FillDown
    End With

En resumen: al momento de actualizar la conexión -solamente- dejas las fórmulas de la fila 3. Tras la actualización vuelves a poner las fórmulas tomando a las de la fila 3 como modelo.

Buenas Cacho R

Muchas gracias.

Lo he probado y, aunque funciona bien durante el refresh de la conexión, al hacer el FillDown tarda 5 minutos en actualizarse (además de lanzar un fallo de memoria).

¿Son 75.000 celdas con la fórmula INDICE-COINCIDIR cada una de ellas. Es posible que sean demasiadas celdas?

Muchas gracias por tu tiempo.

Saludos.

Share this post


Link to post
Share on other sites
Hace 14 horas, sailepaty dijo:

Otra opción es Power Query <<revisa el adjunto>>. Podrías incluso conectar tu consulta de SQL directamente a PQ y solo mostrar el informe en tu hoja de Excel.

 

Saludos

 

Datos 2018.xlsm

Buenos días Sailepaty.

Me encanta la solución. No había tenido Office 2016 hasta ahora y ni si quiera me había planteado Power Query.

Viendo su potencial tengo mucho que hacer para revisar trabajos anteriores cuya solución no era eficiente.

¿Hay alguna manera de marcar una respuesta como correcta a parte de los iconos de reacción?

Muchas gracias por tu tiempo.

Saludos.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

Responsable: Sergio Andrés Celemín

Finalidad: Moderar y responder comentarios de usuarios. Recuerda que la información que facilites es pública, y los datos que incluyas los leerá cualquier visitante de esta web, así como el avatar que poseas.

Legitimación: Consentimiento del interesado.

Destinatarios: Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso,
rectificación, supresión, oposición y demás derechos legalmente establecidos a
través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.




  • Posts

    • Hola Silvia Es exactamente eso lo que deseo realizar, desde ya agradezco tu tiempo, ya lo podemos dar por solucionado. Abrazos 
    • Hola ramonvar (y Luis) Ramón si de todas maneras vas a digitar los 15 números,  y solo deseas evitar ingresar los guiones, lo puedes hacer con formato personalizado, en tipo colocas 000-000-0000000 Pero como te podrás dar cuenta viendo en fx(cuadro de fórmula), el numero en sí no tendrá los guiones, solo será en apariencia. (formato)   o también puedes utilizar una formula en otra celda       Espero te sirva... saludos, Silvia  
    • Buen Día Haplox Excelente respuesta y muchas gracias por la respuesta, tan sencillo y eficaz, el procedimiento, pero la ignorancia no te mata pero te tiene mal, es un dicho popular, acá... demos por solucionado Abrazo
    • No te hace falta macro ni fórmulas, solo dale a la columna un formato personalizado tal que "000-000-0000000"  
    • Buenos Día a todos Si alguien me pudiera dar una mano en esto, les agradecería, tengo columna en una planilla en la cual quiero ingresar números, solo que con este formato xxx-xxx-xxxxxxx este sería ese formato.  Ej. 001-001-0012536, entonces, lo que quiero es que cuando ingreso el primer 001, automáticamente me posicione el cursor en el próximo 001 y así después, ya posicionarme después del carácter "-". Sintetizando, automatizar con una macro, la aparición de los guiones, darme la opción de introducir los tres primeros números y que aparezca el primer guión, luego introducir los otros tres y aparezca el segundo guión "-", para luego completar. Anexo planilla con ejemplos de números Desde ya gracias    FormatoCelda.xlsm
  • Recently Browsing

    No registered users viewing this page.

×
×
  • Create New...

Important Information

Privacy Policy