Actualizar consultas Excel mediante código VBA

Actualizar una consulta generada en Excel por Power Query (Obtener y transformar datos) es sencillo, sea cual sea el método que queramos utilizar: interfaz de usuario o VBA.

Si recopilamos los métodos más habituales son:

1. Actualizar mediante interfaz de usuario, manualmente:
• Desde el botón desplegable "Actualizar todo" de la ficha Datos

• Opción Actualizar todo (se actualizan todas las consultas y tablas dinámicas del libro)
• Opción Actualizar (se actualiza solo la tabla activa)
• Desde la lista de consultas (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Actualizar)

1. Actualizar automáticamente la consulta configurando la actualización automática desde sus propiedades (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Propiedades/Actualizar al abrir el archivo o Actualizar cada....x minutos)
2. Actualizar mediante una macro con código VBA:
• Para una consulta sería:

•  ActiveWorkbook.Connections("Consulta - nombreConsulta").Refresh

• Para todas las consultas sería:

• ActiveWorkbook.RefreshAll

Como comentario para usuarios avanzados, tenemos que decir que en este último caso, a veces, ocurre un problema de sincronización entre el tiempo de actualización de la consulta y el tiempo de ejecución del código VBA.

Problema
Esto es, si una consulta se ejecuta mediante código VBA e inmediatamente después del código VBA de actualización de la consulta hay otro código VBA, este último código NO SE EJECUTA DESPUÉS DE FINALIZADA LA ACTUALIZACIÓN de la consulta, sino que se ejecuta inmediatamente después de INICIADA la actualización de la consulta. En otras palabras:

Cuando se ejecuta la consulta, esta comienza a ejecutarse y tarda un cierto tiempo, pero el código siguiente se ejecuta inmediatamente después y podría ocurrir que la consulta terminara después de que el código siguiente termine. Un ejemplo sería la macro:

Sub miMacro()
    ActiveWorkbook.Connections("Consulta - REV").Refresh

    ActiveSheet.ListObjects("tbl_REV").Range.AutoFilter Field:=5, Criteria1:="="

End Sub

Con el código anterior:

1. La primera línea de código inicia la actualización de la consulta REV
2. La segunda línea de código aplica un filtro a la tabla donde se carga la consulta REV
3. La consulta termina de actualizarse y se carga sobre la tabla REV, eliminando el filtro aplicado en la línea anterior

Con todo lo anterior, se evidencia el problema: la segunda línea de código queda inutilizada.

Solución
Es simple. Configurar la consulta de forma distinta a como está configurada por defecto:  (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Propiedades/Desactivar la opción Habilitar actualización en segundo plano)

 José Manuel Pomares Medrano

 

Novedades en Office 2019 y 365 (Capítulo 2: PowerPoint)

Continuamos con la serie de artículos dedicados a las novedades de la versiones 2019 y 365 de la suite ofimática de Microsoft. Aunque este es un blog sobre Excel, remitimos al usuario a nuestro blog de presentaciones para ver el dossier completo de novedades. Podemos afirmar que casi cualquier usuario o usuaria que utiliza Excel, alguna vez (o quizá muchas), utiliza también PowerPoint.

Al igual que en el caso de Excel, lo primero que debemos decir es que no todas las novedades están disponibles para usuarios de PowerPoint 2019 (la versión de pago único). Sin embargo, los suscriptores de Office 365 si podrán disfrutar de un PowerPoint con todas las nuevas funcionalidades.

Aquí os dejo el enlace para que veáis todas las novedades de PowerPoint:


Novedades en PowerPoint 2019 y 365

Si queréis ver más sobre novedades de Office, podéis hacer clic en el siguiente enlace y tenéis todo un curso gratuito grabado por mí para LinkedIn Learning:

Novedades Office 2019

 José Manuel Pomares Medrano

 

Novedades en Office 2019 y 365 (Capítulo 1: Excel )

Comenzamos una serie de artículos que describen de una forma más o menos exhaustiva las novedades aparecidas en Septiembre de 2018 en la versión 2019 de Office (versión de pago único), así como las novedades que podemos encontrar en la versión de suscripción, Office 365. Este primer artículo lo dedicamos a Microsoft Excel.

Excel es una de las aplicaciones estrella de la suite ofimática de Microsoft y no ha decepcionado en cuanto a novedades. Destacan las relativas al análisis de datos en sus distintas formas: funciones, gráficos y la nueva última utilidad incorporada para los suscriptores de Office 365, Ideas.

Lo primero que hay que comentar es que no todas las novedades están disponibles para usuarios de Excel 2019 (la versión de pago único). Sin embargo, los suscriptores de Office 365 si podrán disfrutar en su Excel de todas las nuevas funcionalidades.

NOVEDADES EN LA INTERFAZ

Nuevo backstage (para Office 2019 y Office 365)

Desde la ficha Archivo se accede a lo que llamamos backstage. Este es un espacio dedicado a todas las acciones referidas a los archivos de Excel (abrir, nuevo, guardar, guardar como, exportar, cerrar, etc.). Ahora, tiene un nuevo aspecto. También se ha renovado la pantalla de bienvenida de Excel (la que se obtiene nada más arrancar la aplicación).

Ausencia de pestañas (solo para Office 365)

Los suscriptores de Office 365 dicen adiós a las pestañas que veíamos en la parte superior de la cinta de opciones. Mediante estas pestañas activábamos cada una de las fichas de la cinta de opciones. Ahora, tan solo sabemos que tenemos una ficha activa mediante un subrayado que aparece en el nombre de la ficha. En cualquier caso, los usuarios de Office 2019 seguirán disponiendo de estas pestañas.

Tema Negro (para Office 2019 y Office 365)

Desde la ficha Archivo > Cuenta, disponemos de un nuevo tema con el que configurar nuestro Office para que tenga un aspecto oscuro y de esta forma conseguir un doble propósito: ahorrar batería en ordenadores portátiles y hacer que nuestra vista trabaje más relajada.

NOVEDADES EN GRÁFICOS, ICONOS Y DISEÑO

Gráficos

Desde la ficha Insertar > Grupo gráficos, podemos encontrar nuevos tipos de gráficos: gráficos de embudo y gráficos de mapas 2D.

Iconos y elementos 3D (para Office 2019 y Office 365)

Desde la ficha Insertar > Grupo ilustraciones es posible ahora insertar nuevos elementos:

• Iconos, desde una galería gratuita que incluye multitud de iconos clasificados por categorías.
• Imágenes 3D, desde una galería que también clasifica todo por categorías

NOVEDADES EN HERRAMIENTAS DE TEXTO

Accesibilidad mejorada (para Office 2019 y Office 365)

Ahora, Office ha mejorado la accesibilidad con un comprobador que encontramos en la ficha Revisar

y también con la posibilidad de generar automáticamente texto alternativo si lo configuramos desde la ficha Archivo > Opciones > Accesibilidad.

Color de hipervínculos (para Office 2019 y Office 365)

Si bien antes los hipervínculos siempre los veíamos de color azul, ahora podemos cambiar el color desde la herramienta Color de fuente (la misma que el texto o los números).

ENTRADAS DE LÁPIZ MEJORADAS

Ahora, podemos activar voluntariamente la ficha llamada Dibujar desde Archivo > Opciones > Personalizar la cinta de opciones > Activar casilla de verificación Dibujar. Una vez hecho esto, disponemos de una ficha con comandos mejorados:

Reproducción de entrada de lápiz (solo para Office 365)

Esta herramienta hace posible visualizar una animación de cómo hemos ido escribiendo un texto.

Entrada de lápiz a forma (para Office 2019 y Office 365)

Esta utilidad permite dibujar a mano alzada cualquier figura geométrica con ciertas imperfecciones y posteriormente convierte nuestro dibujo en una figura geométrica perfecta.

Entrada de lápiz a matemáticas (para Office 2019 y Office 365)

Esta herramienta ya estaba disponible desde la ficha Insertar > Ecuación, pero ahora está incorporada también a esta renovada ficha dedicada al dibujo a mano alzada. Lo que hace es convertir números y ecuaciones escritas a mano alzada con un lápiz digital en números y ecuaciones perfectas.

Selección de lazo (para Office 2019 y Office 365)

Es una útil herramienta para seleccionar todos aquellos elementos que vamos dibujando a mano alzada desde la ficha Dibujar. Solo tenemos que "arrastrar" el cursor con el ratón... como intentando rodear el elemento que será seleccionado.

INTELIGENCIA Y ANÁLISIS DE DATOS

Publicar (para Office 2019 y Office 365)

Desde la ficha Archivo > Publicar es posible, desde Excel, cargar y exportar datos a Power BI.

Ideas de Excel (solo para Office 365)

Esta es una de las novedades incorporadas en Octubre de 2018 a Excel. Se trata de un "analizador" de datos automático que proporciona estadísticas inteligentes sobre una tabla cualquiera sin que el usuario necesite tener conocimientos estadísticos y sin que deba realizar ninguna acción (tan solo hacer clic en un botón).

Nuevas funciones de Excel (para Office 2019 y Office 365)

Excel ha incorporado una buena batería de nuevas funciones. Algunas, son funciones lógicas que evitan tener que utilizar largas y complicadas funciones anidadas. También hay algunas funciones de texto. La lista de las funciones nuevas es: SI.CONJUNTO, MAX.SI.CONJUNTO, MIN.SICONJUNTO, CAMBIAR, CONCAT Y UNIRCADENAS (las dos últimas son funciones de texto que perfeccionan el funcionamiento de la antigua función CONCATENAR).

TRABAJO COLABORATIVO

Coautoría (solo para Office 365)

Los suscriptores a Office 365 podrán disfrutar de un trabajo con Excel compartido en tiempo real. Es decir, los cambios se producen instantáneamente, cuando cada uno de los usuarios que trabajan sobre un mismo libro de Excel, los realizan. El libro debe estar alojado en OneDrive o en SharePoint.

Chat para trabajo en coautoría (solo para Office 365)

Para complementar el propio trabajo colaborativo, Excel añade un chat para poder intercambiar mensajes entre los usuarios que están compartiendo un mismo libro.

Si queréis ver más sobre novedades de Office, podéis hacer clic en el siguiente enlace y tenéis todo un curso gratuito grabado por mí para LinkedIn Learning:

Novedades Office 2019

 José Manuel Pomares Medrano

 

Extraer la última palabra o parte de un texto de una celda de Excel

Muchos usuarios conocen algunas funciones de Excel como IZQUIERDA, DERECHA, EXTRAE, ENCONTRAR, LARGO, etc. Estas funciones, ya sea en solitario o actuando coordinadamente entre ellas, pueden conseguir con más o menos dificultad extraer cualquier palabra o cadena de texto desde la parte izquierda, central o derecha de un texto que pueda haber en una celda.

La cuestión se complica si lo que queremos extraer no tiene un ancho fijo (un número fijo de caracteres). Pero tiene también solución si, como hemos comentado antes, ponemos a trabajar juntas a varias funciones anidadas. Por ejemplo, para extraer la palabra de la izquierda (la primera palabra) de un texto que tiene varias palabras, podríamos utilizar la siguiente fórmula:

=IZQUIERDA(A1;ENCONTRAR(" ";A1))

Donde A1 es la celda donde se encuentra el texto

Pero, ¿y si necesitamos extraer la última palabra de una frase, el último apellido de una persona, la última parte de un código o el dominio de un email? Esto sería algo más difícil y deberíamos anidar también varias funciones, dando como resultado una función relativamente compleja.

Una cómoda y elegante solución podría ser la creación de un complemento de Excel (addin) que contuviera una UDF (User Defined Function) o Función Definida por el Usuario. La creación de una UDF se realiza mediante unas líneas de código VBA. Esas líneas de código las vamos a alojar en un libro de Excel que convertiremos a complemento mediante Guardar como.... Una vez completado el proceso, tendremos a nuestra disposición, en nuestro Excel, una función más dentro de todas las funciones que ya teníamos propias de Excel. Además, tendríamos la ventaja añadida de poder elegir el carácter que separa lo que queremos extraer del resto de la cadena.

Para crear una UDF dentro de un addin que esté listo para ser usado, debemos:

PASO 1)

Crear un libro de Excel nuevo, presionar ALT+F11 e insertar un módulo estándar en el proyecto Libro1 (si el libro nuevo se llama Libro1). Así pues, una vez ya hemos presionado ALT+F11, tenemos que hacer...

 

PASO 2)
Pegar en la ventana que se ha abierto a la derecha el código siguiente:

Function ULTIMA_PALABRA(strTexto As String, Optional strSeparador As String) As String

    'UTILIDAD:  extrae de un texto los caracteres que hay después del último separador
    'SINTAXIS:  =ULTIMA_PALABRA(texto;[separador])
   
    'Texto:         un texto, número o ref. a la celda de donde tenemos que extraer
    '[separador]:   uno o varios caracteres de texto (debe ir entre comillas)
   
    'Si no se especifica, [separador] es un espacio
   
    If strSeparador = "" Then strSeparador = " "
    Dim bytLargo As String:         bytLargo = Len(strTexto)
    Dim bytEncontrar As Integer:    bytEncontrar = InStr(strTexto, strSeparador)
   
    ULTIMA_PALABRA = Right(strTexto, bytLargo - bytEncontrar)

End Function



Una vez pegado el código, debe verse así en la ventana de código:

PASO 3)

Guardar el Libro1 como... Tipo de archivo: Complemento de Excel (tan solo con elegir la opción Complemento de Excel, la ruta de guardado para el archivo cambia automáticamente y es conveniente respetarla). En cuanto al nombre del archivo, escribimos el que deseemos. Por ejemplo, MisComplementos.

PASO 4)

Ahora, debemos asegurarnos de que Excel cargue el complemento cada vez que se inicie. Para ello, hacemos clic en... ficha Archivo / Opciones / Complementos / Complementos de Excel / Ir...

PASO 5)
Activar el complemento (lo veremos en la lista con el nombre que dimos en el paso 3) y Aceptar.

PASO 6)

Una vez hecho lo anterior, cerramos Excel SIN GUARDAR Libro1 (puesto que ya lo guardamos antes convirtiéndolo a complemento de Excel) y lo abrimos de nuevo.

Ya podemos utilizar la función ULTIMA_PALABRA como si fuera otra función de Excel:

 

 Podemos comprobar que la sintaxis de nuestra nueva función es:

=ULTIMA_PALABRA(texto;[separador])

texto:               un texto, número o ref. a la celda de donde tenemos que extraer
[separador]:    uno o varios caracteres de texto (debe ir entre comillas). Si no se especifica, [separador] es un espacio

Por lo tanto, podemos deducir que también podemos extraer la última parte de una cadena de texto que tenga cualquier separador. Por ejemplo, en la imagen de abajo tenemos unos códigos que tienen una última parte separada siempre por un guion. Pues con nuestra nueva función ULTIMA_PALABRA podemos extraer esa parte final del código utilizando como segundo argumento un guion (siempre debemos poner nuestro separador entre comillas, al ser una cadena de texto):

En Mac, el proceso es prácticamente idéntico y solo cambian algunas imágenes y algunos detalles. Lo que exponemos a continuación son los cambios que podemos apreciar, respecto del trabajo en Windows:

PASO 1)

PASO 3)

En Mac, Excel no establece una ruta conveniente de guardado de forma automática, sino que debemos elegir una (la que queramos) para saber dónde tenemos alojado nuestro addin.

PASOS 4) y 5)
En Mac, para activar un complemento debemos ir al menú Excel / Preferencias... / Barra de herramientas y cinta de opciones

 ... y activar la casilla de Ficha Programador

 ...salimos aceptando cambios y ya tenemos una nueva Ficha o Pestaña llamada Programador. En ella, debemos hacer clic en Complementos de Excel.

 José Manuel Pomares Medrano
 

PDF con 62 mini lecciones de VBA para Excel. Serie #SabíasQueEnVBA

Al igual que hiciera con los 62 trucos de Excel de la serie gratuita #SabíasQueEnExcel, también con la serie #SabíasQueEnVBA voy a ofrecer a mis lectores y seguidores, en este mismo artículo, un eBook con 62 mini-lecciones de VBA para Excel:

Este trabajo es la recopilación, en PDF, de la serie que he ido publicando a diario en Facebook, Twitter y LinkdeIn, bajo el hashtag #SabíasQueEnVBA.

Según he podido comprobar con los comentarios, puedo decir que he quedado muy satisfecho de la utilidad ofrecida por este curso gratuito. Ha sido un placer compartir con la comunidad de usuarios y usuarias de Excel estos conocimientos.

Sin más dilación, ahí va el enlace para la descarga:

eBook PDF #SabíasQueEnVBA

Gracias por la acogida y preparaos para el próximo #SabíasQue...


José Manuel Pomares Medrano

La nueva función SI.CONJUNTO en Excel

Cuando un usuario traspasa cierta línea y nivel en su utilización de Excel, descubre que la función SI es sumamente práctica para obtener un valor en una celda. Ese valor que obtenemos está condicionado a que se cumpla una prueba lógica del tipo A1 = 5 (por ejemplo). Pero también es posible obtener un valor condicionado a que se cumplan dos pruebas o tres pruebas... y así sucesivamente. De hecho, hay posibilidad de anidar 64 funciones SI para condicionar con 64 pruebas lógicas.

El problema es que anidar demasiadas funciones requiere una cuidada planificación para no errar en la lógica. En el caso de que algo haya ido mal en esa planificación (o en el caso de que necesitemos modificar a posteriori la fórmula), cuando tenemos más de 5 o 6 funciones SI anidadas, todo se complica de forma extrema.

Microsoft ha sido sensible a ello y ha creado una función que soluciona este problema con una función que realiza interiormente esos anidamientos. Tan solo tenemos que ordenar convenientemente las pruebas lógicas y los resultados requeridos.

Por ejemplo, si queríamos anidar 7 funciones SI para determinar el día de la semana en función del número que hay en la celda A1 (del 1 al 7), hasta ahora podíamos hacerlo así:

=SI(A1 =1;"Lunes";SI(A1=2;"Martes";SI(A1=3;"Miércoles";SI(A1=4;"Jueves";SI(A1=5;"Viernes";SI(A1=6;"Sábado";SI(A1=7;"Domingo";"")))))))

(Este es un ejemplo relativamente sencillo, pero la fórmula puede complicarse muchísimo más)

La cuestión es que, ahora, con la nueva función SI.CONJUNTO de Excel, la fórmula puede quedar así:

=SI.CONJUNTO(A1=1;"Lunes";A1=2;"Martes";A1=3;"Miércoles";A1=4;"Jueves";A1=5;"Viernes";A1=6;"Sábado";A1=7;"Domingo")

Además de que es algo más corta la fórmula, hay algo que determina de forma importantísima la mayor facilidad de manejo: solo hay una función con sus dos únicos paréntesis de apertura y cierre.

Por lo tanto, la sintaxis de la función SI.CONJUNTO es:

SI.CONJUNTO (prueba_lógica_1; valor_si_verdad_1, prueba_lógica_2;valor_si_verdad_2....)

Donde:

prueba_lógica_1 es la primera comparación utilizada (con operadores del tipo >, <, =, >=, <=, <>, etc.)
valor_si_verdad_1 es el primer valor que exigimos si prueba_lógica_1 resulta VERDADERO
....
prueba_lógica_127 es la primera comparación utilizada (con operadores del tipo >, <, =, >=, <=, <>, etc.)
valor_si_verdad_127 es el primer valor que exigimos si prueba_lógica_1 resulta VERDADERO

Podemos incluir 127 pruebas lógicas con sus respectivas 127 opciones de valor_si_verdad.

También es necesario comentar que si la función SI.CONJUNTO no encuentra ninguno de los valores que se le proponen, devuelve un error del tipo #N/A.

La función SI.CONJUNTO está disponible también en Excel Online, como podemos observar a continuación:

En Office 365 para Macintosh la función SI.CONJUNTO también está disponible, como podemos ver:

José Manuel Pomares Medrano

PDF con 62 trucos de Excel. Serie #SabíasQueEnExcel

Durante varios meses he estado lanzando todos los días laborales un truco de Excel, de forma gratuita y simultánea en tres plataformas distintas: Facebook, Twitter y LinkedIn. Lo hacía bajo una etiqueta o "hashtag" llamada #SabíasQueEnExcel y, lo que en un principio se convirtió en una prueba para ver la aceptación, la verdad es que ha ido calando en mis seguidores hasta el punto de que, muchos de ellos, cuando me veían personalmente me felicitaban y me animaban a continuar con la serie declarándose "fans" de la misma.


Así las cosas, darle continuidad a algo que satisfacía a tantos usuarios de Excel no ha sido difícil, puesto que sabía que cada día, lo que escribía no caería en saco roto.

Una vez terminada la serie, me planteé hacer una recopilación. El motivo es claro: muchos seguidores no habrán podido guardar todos los trucos en algún lugar apto para ser consultado frecuentemente. El resultado es un ebook en formato PDF que recoge todos los trucos con algunos consejos extra que no aparecían en cada truco diario.

El enlace para descargar el ebook es:

eBook PDF #SabíasQueEnExcel

Gracias por la buenísima acogida y por haber compartido el PDF de forma masiva en las RRSS.
¿Estáis preparados para el próximo #SabíasQueEnVBA ?


José Manuel Pomares Medrano