Proteger nuestro código de vba mediante el uso de una contraseña

Hoy vamos a ver ¿cómo proteger nuestras macros mediante el uso de una contraseña?. Muy útil para evitar que alguien altere nuestro código, o que tenga acceso a cadenas de conexión que incluyen nombres de usuario y claves de bases de datos o de otros sistemas.

Para hacerlo, abrimos un archivo que tenga una macro y llamamos el editor de vba: Alt + F11:

Damos clic derecho en el módulo que contiene la macro y del menú desplegable seleccionamos la opción: Propiedades de VBAProject...

Ejecutar una macro al momento en que abrimos nuestro archivo de excel - vba

Hola, hoy vamos a escribir un poco de código en vba. Lo que vamos a hacer es construir una macro que se ejecute en el mismo instante en que damos doble clic en un archivo de excel y este se abre. Esta funcionalidad puede ser muy útil para limpiar información de nuestro archivo, para iniciar algunas variables en el mismo, para guardar una copia del archivo etc. 

Todo depende del problema que estemos abordando. En mi caso, la voy a utilizar para que el usuario vea un msgbox.

Iniciemos entonces, llamando nuestro editor de vba con Alt + F11 e insertemos un nuevo módulo:

Vamos a dar doble clic en ThisWorkbook:

Trabajando con los argumentos del método GetOpenFilename en vba

En el post anterior tuvimos nuestro primer contacto con el método GetOpenFilename. Hoy vamos a revisar los argumentos opcionales que podemos incluir cuando nuestra macro realice la llamada al método.

La siguiente es la estructura completa del método con sus argumentos:

Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

Donde:

FileFilter: Es una cadena de texto que nos sirve para aplicar un criterio de filtrado.

FilterIndex: Es un número que funciona como indice para indicar cuál es el criterio de filtrado a usar.

Title: Sirve para personalizar el titulo del cuadro de diálogo. Si se deja en blanco, por defecto aparecera: Abrir.

ButtonText: Solo aplica para Mac.

Multiselect: Acepta el valor True (verdadero) o False (falso). True nos permite seleccionar más de un archivo a la vez. False solo nos permite seleccionar un archivo.

Recordemos que cuando llamamos el método sin ninguno de sus argumentos lo que tenemos es el siguiente cuadro:

Vamos a hacer el llamado pasando un valor para el argumento FileFilter: 

Usando el método GetOpenFilename de vba en nuestras Macros

Hoy vamos a trabajar con el método GetOpenFilename del objeto Application en Excel. 

Dicho método, lo podemos utilizar en las macros que escribamos en vba, cuando necesitemos que el usuario tenga que interactuar con la aplicación y deba suministrar un nombre de archivo y una ruta para que la macro realice alguna operación con el mismo.

Ademas, al hacerlo de esta forma le vamos a dar un toque mucho más profesional a nuestra macro.

Cuando invocamos el método GetOpenFilename abre el siguiente cuadro de diálogo:

Este cuadro es familiar para todos los que trabajamos con Excel o con cualquier aplicación de Office. Basta con navegar en las distintas carpetas hasta encontrar el archivo que queremos, seleccionarlo y dar clic en el botón Abrir.

 Lo que vamos a hacer es escribir una sencilla macro como ven a continuación:

La primera linea, llama el cuadro de diálogo y asigna el valor de retorno del mismo a la variable arch. Este método nos devuelve una cadena de texto que contiene el nombre del archivo y la ruta en la que este se encuentra.

Trabajando con Transacciones en Microsoft Access - vba

Hoy vamos a trabajar con transacciones en Microsoft Access. Así es, esa pequeña base de datos, que en mi opinión es un gran caballito de guerra, también trabaja con transacciones. 

Vamos a empezar por definir que es una transacción, en la página de Microsoft nos dicen que una transacción es: 

"Una transacción es una secuencia de operaciones realizadas como una sola unidad lógica de trabajo. Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como propiedades de atomicidad, coherencia, aislamiento y durabilidad (ACID), para ser calificada como transacción."

Lo que quiere decir que una transacción se compone de mas de una operación en una base de datos y que esta tiene validez únicamente si se completan todas o ninguna.

El ejemplo clásico de esto, son las dos operaciones que se realizarían en el aplicativo de un banco al momento de debitar una cuenta por un monto y acreditar otra por dicho valor. 

Dicha operación solo tiene validez si ambas operaciones se ejecutan. ¿Pensemos que pasaría si solo se debita la primera cuenta? Tendríamos una disminución en el saldo de la misma y la cuenta de destino no registraría ningún incremento, es decir: el dinero quedaría en una especie de limbo.

Para evitar esto, las dos operaciones se deben empaquetar en una transacción, así, esta solo tendrá validez si se ejecutan las dos o ninguna.

Pasemos de la teoría al ejemplo. Vamos a trabajar con una sencilla base de datos que solo tiene una tabla con la siguiente información:

Nuestra misión es debitar (disminuir) la cuenta de Eduardo en $100 y acreditar (incrementar) la cuenta de Diana en ese mismo valor utilizando una transacción.

Invertir cadenas de texto en excel creando una función personalizada con vba

Dentro de las muchas funciones para el tratamiento de textos que trae excel incluidas no hay una que nos permita invertir cadenas. Es cierto, no la hay. Si tenemos en una celda: "Hola" y queremos invertirla para tener: "aloH" no lo podemos hacer con las funciones disponibles.

Sin embargo, la librería de funciones que incluye vba si tiene una para cumplir con dicho propósito. Se llama StrReverse( ) y lo que nos pide como argumento, es una cadena de texto para poder invertirla.

Como la función esta disponible en vba, tendremos que escribir un poco de código y crear una función personalizada. Así es, en excel también podemos crear nuestras propias funciones y llamarlas en nuestras hojas de cálculo tal y como lo hacemos con BUSCARV, BUSCARH, IZQUIERDA, DERECHA o cualquier otra.

Vamos entonces a construir una función con la que podamos invertir la cadena de texto que tenemos en la celda B2:

Digitamos la combinación de teclas Alt + F11 para llamar el editor de vba y creamos un nuevo módulo:

Truco Excel: Eliminar todos los comentarios de las distintas hojas de un archivo de excel

Hoy vamos a revisar un sencillo pero muy útil truco. En muchas oportunidades, como medio para documentar nuestros archivos de trabajo en excel, utilizamos comentarios en algunas o varias de las celdas. Llega el momento en que debemos compartir o enviar nuestro archivo a otra persona y dado que los comentarios contienen información confidencial, debemos borrarlos. 

Tarea que no es fácil y que se nos puede complicar si nuestro modelo es muy grande, lo que nos puede arriesgar a dejar alguno y enviarlo al destinatario.

Para resolver este problema, vamos a escribir una sencilla macro en vba, que solo va a tener de tres líneas de código.

Supongamos entonces que tenemos un archivo con tres hojas, y vamos a colocar en cada una de las tres hojas, comentarios al azar:

Como ya saben, las celdas con comentarios son aquella que en su esquina superior derecha tienen un pequeño triangulo rojo y que al pasar con el mouse sobre ellas, nos muestran un rectángulo amarillo como el de la imagen.

Enseguida vamos a digitar Alt + F11 para llamar nuestro editor de visual basic y seleccionamos la opción de agregar un nuevo módulo:

Post Mejorado: Ejecutar la instrucción DELETE de SQL desde excel usando solo el objeto ADODB.connection de vba

En el post Ejecutar la instrucción DELETE de SQL desde Excel usando VBA vimos como realizar operaciones con bases de datos desde excel. Para esto, utilizamos una conexión y después mediante el uso de un objeto comando, enviamos la instrucción a la base de datos. Hoy vamos a mejorar este código, utilizando solamente la conexión y no vamos a hacer uso del comando. Ya quedará al gusto de cada uno de ustedes, seguir escribiendo sus propios programas como más les guste.

Entonces, vamos a recordar el código inicial:

Como pueden ver, las lineas finales, donde se inicia la instrucción with, es donde esta nuestro comando. Vamos entonces a eliminarlas y reemplazarlas de la siguiente manera:

De esta forma, ya no necesitamos declarar el objeto command ni el código asociado al mismo.

Crear una macro para ordenar las hojas de un archivo de excel en orden ascendente o descendente

Hoy vamos a escribir un poco de código en vba. El objetivo que debe cumplir nuestra macro es el de ordenar todas las hojas (o pestañas o tabs) de un archivo de excel, bien sea en orden ascendente o descendente.

Vamos a trabajar con el siguiente archivo de ejemplo:

Como pueden ver, tenemos 10 hojas son los siguientes nombres de ejemplo: X, A, M, W, Q, N, C, I, K, H. Entonces, si todo sale bien con nuestra macro, las hojas deberían quedar en el siguiente orde: A, C, H, I, K, M, N, Q, W, X o al reves, según la opción que seleccionemos.

Vamos a empezar por abrir el editor de vba, para esto utilizamos nuestro teclado digitando: Alt + F11:

Ejecutar la instrucción UPDATE de SQL desde Excel usando VBA

En este segundo post, vamos a ejecutar la instrucción UPDATE que básicamente es para actualizar registros en una base de datos. Sencilla pero poderosa y de mucho cuidado, pues después de ejecutada no la podremos deshacer. Nuevamente, vamos a construir una macro que va a combinar vba y sql.

Vamos a iniciar abriendo un archivo de excel, y vamos a dar la combinación de teclas Alt + F11 para abrir el editor de visual basic. Nos  deberá aparecer la siguiente ventana:

Seleccionamos la opción para insertar un módulo:

Ejecutar la instrucción DELETE de SQL desde Excel usando VBA

Vamos a iniciar una serie de posts en los que vamos a utilizar las instrucciones básicas de SQL para borrar, actualizar e insertar datos desde excel en una tabla de una base de datos. Para nuestros ejemplos, vamos a utilizar una base de datos construida en Ms Access, pero podemos reutilizar el código para hacer las operaciones con una base de datos de Oracle, SQL Server, My SQL y otras más.

Este primer post, es sobre el uso de la instrucción DELETE. Sencilla pero poderosa y de mucho cuidado, pues después de ejecutada no la podremos deshacer. En este ejemplo, vamos a construir una macro que va a combinar vba y sql. En mi opinión una combinación poderosa que nos va a servir para solucionar muchas de las necesidades que tengamos en nuestro trabajo.

Vamos a iniciar abriendo un archivo de excel, y vamos a dar la combinación de teclas Alt + F11 para abrir el editor de visual basic. Nos  deberá aparecer la siguiente ventana:

Seleccionamos la opción para insertar un módulo:

Acta de reuniones en excel y vba, con la que podemos asignar tareas al calendario de outlook o gmail de los responsables.

Uno de los puntos débiles de muchas de las reuniones en las que participamos a diario es que suelen estar llenas de buenas intenciones, palabras y promesas que no generan acciones tangibles para poder dar cumplimiento a los acuerdos y tareas que surgen en el desarrollo de las mismas.

En algunas ocasiones, esto se suele presentar porque no hay tiempo para hacer seguimiento y porque los responsables suelen perder esas tareas de vista en su día a día.

Con el objetivo de ayudar a que esto no pase, y que los responsables mantengan una permanente visual de los compromisos adquiridos, hoy vamos a trabajar en la construcción de una muy sencilla acta de reuniones en excel, con la que al finalizar la reunión vamos a generar con una macro en vba los recordatorios de tareas a cada uno de los responsables. 

En este ejemplo vamos a trabajar con un mail de gmail que es donde van a quedar los recordatorios para que el responsable se asegure de no llegar a la próxima reunión sin las tareas hechas.

Este desarrollo es un muy buen complemento para la macro que diseñamos y desarrollamos en este otro post.

Vamos a iniciar entonces creando en excel una plantilla como la siguiente:

En esta plantilla, del rango A1 a F11 pueden colocar lo que quieran. Ya saben, lo usual de cualquier formato de acta de reuniones. La fecha, el objetivo de la reunión, los participantes, el logo de la empresa etc.

Lo que más nos interesa es lo que va a ir en el rango que va de A12 a F21. Ya que en ese rango vamos a registrar las tareas que se van a asignar en la reunión y es de ahí de donde nuestra macro va a extraer la información necesaria para generarlas y asignarlas a los responsables.

En este rango, yo he asignado una columna para asignar un id a la tarea, una descripción corta de de la tarea, un detalle completo de la misma, el nombre del responsable, el email y la fecha de vencimiento de la tarea. Siéntanse libres de modificar este rango como lo consideren, de acuerdo con sus propias necesidades, teniendo en cuenta que los campos: email, fecha de vencimiento, descripción y detalle los debemos mantener dada su importancia. Lo importante es que ajustemos nuestra macro, mapeando correctamente los campos que vamos a usar para generar la tarea.

Macro para conectar Excel y SAP y extraer la información de Costos ABC: Ciclos, Segmentos y Procesos.

Después de haber visto como construir una macro para conectar Excel y SAP y tener claro los objetos requeridos y los pasos necesarios para poder lograrlo, hoy vamos a ver una macro un poco mas avanzada.

La misión es construir una macro que nos permita ingresar a la transacción CPV2, ingresar a cada uno de los ciclos de reparto, extraer la información de los segmentos y finalmente obtener los procesos que componen el segmento, las clases de costos, los cecos que se utilizan y los porcentajes utilizados para distribuir los costos.

Cuál es el beneficio de esta macro? Generación de eficiencias en recursos, ya que hacer este proceso de forma manual, requiere de la dedicación de una persona a tiempo completo por unas 2 o 3 horas aproximadamente y hay que tener en cuenta que por tratarse de un proceso manual de copiar y pegar en el que hay que navegar por distintas pestañas y ventanas, la probabilidad de cometer errores es alta. 

Empecemos entonces, por construir una tabla en excel con los siguientes encabezados:

Esos campos, son los que vamos a extraer de SAP mapeandolos en nuestra macro. 

Como hacer web scraping utilizando excel y vba? - Parte 3

Y llegamos a la tercera y última entrega de como hacer web scraping utilizando excel y nuestra poderosa herramienta el vba. Recuerden que para consultar las dos primeras partes de este pequeño tutorial, pueden hacer clic en: Parte 1 y en Parte 2.

El objetivo entonces en este post, es que diseñemos una macro que nos sirva para extraer la información de las páginas web. Lo primero que vamos a hacer es abrir excel y llamar el editor de vba (recuerden utilizar el atajo de teclado: Alt + F11).

Y estando en el editor vamos a dar clic en la opcíon del menú tools (herramientas en español) y seleccionamos la opción: referencias o reference (según el idioma que tengamos en la configuración):

Al hacerlo, se va a desplegar un cuadro de dialogo que contiene las librerías que estan cargadas actualmente y las que están disponibles y que podemos activar haciendo clic en la casilla de selección que esta adelante de cada una.

Para nuestro propósito, necesitamos seleccionar las siguientes (Microsoft HTML Object Library y Microsoft Internet Controls):

Como construir una macro para conectar Excel y SAP y automatizar tareas - Ejemplo 1

Lo prometido es deuda y el día de hoy es el momento de cumplir con lo dicho. Hace tiempo había prometido un post con los pasos básicos para poder aprovechar nuestro ERP SAP, el poder del vba y la flexibilidad y funcionalidad del excel. Como? Por medio de la construcción de una macro que nos permita conectar SAP con excel, aprovechando un API que trae el primero de los dos. Esta macro, nos puede servir para administrar datos maestros, para ingresar información transaccional a nuestro sistema o para generar un reporte que no tengamos a la mano. Lo más importante de todo esto, es que trabajemos inicialmente en nuestro sistema de pruebas (calidad), construyamos, probemos y ajustemos hasta obtener los resultados deseados. 

Todo esto es necesario, porque al igual que como lo mencioné en el post sobre LSMW, lo que haga nuestra macro en el sistema, en especial si es utilizando las transacciones terminadas en 01 o 02 que por lo general son para crear y modificar.

Si creamos una macro para crear proveedores y esta queda mal, el dato maestro en el sistema va a quedar también mal y al final va a generar re-procesos, costos innecesarios y fricciones internas en la empresa y con terceros.

A nadie le gusta escuchar que el pago que esta esperando no se pudo realizar por un error en la configuración de los datos con los que fue creado en el sistema.

Vamos a seguir entonces, una serie de pasos, que después vamos a revisar estén grabados en el script de SAP y de ahí vamos a extraer la información, pegarla en un módulo de macros y realizar unos pequeños ajustes.

Paso 1: Estando en SAP, vamos a activar el grabador de scripts de SAP. Esto lo hacemos dando clic en el último botón de nuestra barra de herramientas (el que esta resaltado por la flecha azul):

Paso 2: Seleccionamos la opción Grabar y reproducir script:

Como hacer web scraping utilizando excel y vba? - Parte 2

Continuando con lo visto en el último post, hoy vamos a revisar el proceso de inspección de las páginas web. En que consiste esto? La idea es revisar la página que vemos en nuestro navegador y que es la vista de usuario final y revisar el código HTML que se encuentra detrás de la misma y así poder identificar las partes mas importantes del mismo y que son las que nos van a servir para construir la macro en la última entrega.

Recuerdan la página que vimos en el último post? vamos a tomarla como base y vamos a iniciar el proceso de inspección de la misma. Para poder hacerlo, debemos dar un clic derecho en la página y seleccionar: inspeccionar:

Al hacerlo nos va a aparecer la página con unas divisiones adicionales en el lado derecho de la misma, tal y como se ve en la siguiente imagen:

Como hacer web scraping utilizando excel y vba? - Parte 1

Bueno, como siempre suele suceder con el excel, aquí va una de esas cosas que jamas me hubiera esperado se pudieran hacer con esta hoja de cálculo y una macro. 

El tema es hacer scraping a una página utilizando el poderoso vba para crear una macro que nos permita obtener información desde una página web. El post lo voy a dividir en tres entregas.

En esta primera parte, vamos a revisar un poco de teoría sobre como funciona internet, para que podamos saber e identificar que esta haciendo nuestra macro. 

En la segunda parte, revisaremos el proceso de inspeccionar las páginas web y en la tercera y última vamos a construir la macro que nos va a permitir realizar el scraping. 

Prometo mas adelante dedicarle un post completo al uso y consumo de servicios web desde excel.

Empecemos por revisar lo que ocurre cuando desde un navegador común y corriente, como internet explorer, chrome o mozilla ingresamos la dirección de una página web en internet. Lo primero es que cuando damos enter en la barra de navegación, se envía una petición al servidor en el cuál se encuentra alojada la página web que queremos consultar, por ejemplo:

Lo que acaba de ocurrir, es que se envió una solicitud utilizando una petición GET o una petición POST. La primera es la que realmente nos interesa, pues es la que nos va a permitir realizar el scraping.

Como automatizar el envío de correos con una macro en vba y outlook?

Hace algunas semanas en el trabajo, alguién me preguntaba por la forma de enviar varios emails desde excel con diferentes asuntos y cuerpos de texto de los correos electrónicos. La idea, es hacerlo utilizando outlook. La pregunta concreta fue: Si existía o no la posibilidad de automatizar la tarea con una macro, de forma tal que esta se pudiera realizar con un simple clic?

A continuación vamos a ver como podemos realizar esta tarea, y como tomando como base el código en vba que adjunto en el post, podemos personalizarlo y crear una solución que se adapte a las necesidades que tengamos.

Veamos, tengo una hoja excel. En una de las columnas tengo varias direcciones de correo electrónico (cada una en una celda), tengo el asunto a tratar en cada correo y tengo el cuerpo del mismo:

Vamos a llamar el editor de vba (Alt+F11), y vamos a ingresar el siguiente código: