Ediciones ENI Editorial | líder en informática
Técnicas avanzadas de automatización
Introducción
En este último capítulo, veremos la automatización de la recopilación, procesamiento y visualización de datos. Nos basaremos en las macros de comandos de Excel y el lenguaje VBA (Visual Basic for Applications) asociado.
Por lo tanto, comenzaremos descubriendo la implementación de macros usando el grabador, aplicaremos su uso a la adición de datos de un formulario a una base de datos completa, como se explicó en la sección La entrada de datos - Uso de un formulario de entrada del capítulo Los datos.
Continuaremos descubriendo el lenguaje mediante la creación de macros de organización, como la recuperación de la lista de archivos presentes en un directorio, la lista de los nombres de las pestañas presentes en un libro de trabajo y la lista de los nombres utilizados en un libro de trabajo.
Automatizaremos la conversión de datos supuestamente recuperados de software de terceros que no respetan los formatos de fecha habituales, así como la conversión de datos fusionados erróneamente en una celda (nombre y apellidos).
Nos ocuparemos de la clasificación sistematizada en un rango variable de datos y terminaremos con una macro de envío de una hoja de un libro de trabajo por correo electrónico, tan pronto como un indicador supere un umbral previsto.
Usar macros
Anteriormente en este libro, hemos habilitado el acceso a la pestaña Programador, que aún debería estar operativa. Ahora nos centraremos en el grupo Código.
Contiene los siguientes botones:
 |
Grabar una macro (que se convierte en macro entre el inicio y el final de la grabación). |
 |
Mostrar las macros: para llamar a una macro, siempre y cuando no esté vinculada a un botón. |
 |
Visual Basic (el atajo de teclado es AltF11): la ventana del editor de VBA está compuesto por las siguientes secciones. |
En 1, el área Explorador de proyectos (visible por Ctrl R, si se ha cerrado por error), el libro, las hojas y los módulos que pueden contener código.
En 2, el área del código, que se puede editar pulsando dos veces seguidas en la zona 1 o después de elegir el objeto con un solo clic.
En 3, en la ventana Propiedades, las propiedades del objeto seleccionado en 1.
En 4, la ventana Ejecución.
En la barra de herramientas Estándar se encuentran los botones clásicos Cortar, Copiar, Pegar, Buscar, Cancelar, Repetir, complementados por los botones Ejecutar, Detener y Restablecer.
Macros imprescindibles para el panel de control
Historización
Abra el libro llamado Agregar cliente.xlsm, pestaña Agregar.
Copie los datos de las celdas O9 a O35.
Péguelos en D9.
Los datos presentes en el rango D9 a D35 no cambian. Lo único que falta por hacer es transferirlos a la pestaña lista_cliente. Hay un "búfer" en la pestaña Agregar en las celdas de Y97 a An101, al que se puede acceder mediante la flecha en L3. Esta zona muestra los valores de las celdas D9 a D35, en el sentido y el orden de la tabla en la pestaña lista_cliente.
En la pestaña Programador - grupo Código, haga clic en Grabar macro. En la ventana que aparece, escriba Agregar en el cuadro Nombre de la macro. Lo almacenaremos en este libro porque no necesitaremos usarlo en otro libro (está en formato .xlsm, el formato de libro de Excel que tiene en cuenta las macros).
Confirme pulsando en Aceptar.
No haga acciones innecesarias como desplazarse hacia abajo o hacia la derecha porque todo lo que haga ahora será grabado y convertido en código VBA.
Haga clic en la pestaña lista_cliente, seleccione la fila 9, inserte una fila y marque el mismo formato que se muestra a continuación.
Haga clic en la pestaña Agregar, seleccione las celdas Y100 a AM100, y pulse en Copiar.
Haga clic en la pestaña lista_cliente, en B9, haga Pegar - Pegar especial - Valores.
Pulse en la tecla Esc para desactivar la función de copia.
Regrese a la pestaña Agregar, seleccione la celda D9.
Detengamos la macro aquí:
En la pestaña Programador - grupo Código, haga clic en Detener grabación.
El botón para detener la grabación también está presente en la barra de tareas.
Vamos a crear una macro desde cero.
En la pestaña Programador - grupo Código, haga clic en Grabar macro. En la ventana que aparece, escriba RAZ en el cuadro Nombre de macro y confirme.
Seleccione las celdas D9 a D35, borre el contenido. A continuación, vuelva a hacer clic en D9.
Haga clic en Detener grabación.
Vamos a comprobar el código generado.
Abra el editor de VBA con AltF11 y, a continuación, haga doble clic en Módulo 1. En el cuadro de código, aparece el siguiente código:
Contiene cada una de las acciones realizadas paso a paso, traducidas a código ejecutable. La transcripción es bastante fácil para los anglófonos: Sheets("lista_cliente").Select significa seleccionar la hoja lista_cliente, Range("B9").Select significa seleccionar la celda B9, Selection.ClearContents significa borrar el contenido de la selección, por ejemplo.
Ahora vamos a asignar las dos macros a los botones provistos para este propósito.
Cierre el editor de VBA. Una vez en el libro, haga clic con el botón derecho del ratón en el botón del triángulo verde y haga clic en Asignar macro:
En la ventana Asignar macro, haga clic en Agregar y, a continuación, en Aceptar.
Haga lo mismo con el botón del triángulo rojo para asignarle la macro RAZ.
Ahora puede usar la macro para insertar tantos clientes nuevos como desee, el número se incrementará automáticamente. En caso...
Las macros de reprocesamiento
En las fechas
Supongamos que ha importado datos de su aplicación de gestión. Las fechas de esta lista tienen el formato AAAAMMDD y Excel no puede procesarlas directamente como fechas.
Vamos a crear una función personalizada para transformar estas fechas en DD/MM/AAAA. Esta característica puede ser útil en otros casos, por lo que se debe colocar en su libro de macros personales.
Abra el libro CorreccionFechas.xlsm.
Abra el editor Visual Basic VBA en el módulo GestionFechas (también puede copiar código del archivo Sub Fechas.txt):
Function TDate(VDate As String) As Variant
If Len(VDate) <> 0 Then
TDate = CDate(Right(VDate, 2) & "/" & Mid(VDate, 5, 2) & "/" & Left(VDate, 4))
Else
TDate = ""
End If
End Function Una vez creada la función, se puede utilizar directamente en los cálculos:
Escriba en B2 =tdate (A2).
Sobre los nombres/apellidos
Entre las importaciones realizadas, algunas veces tendrá que corregir cierta información. En el siguiente ejemplo, los nombres y apellidos de nuestros empleados se han introducido en la misma columna. Además, no se respetaron las reglas de entrada para letras minúsculas/mayúsculas.
Excel...
Clasificar sus datos
Al procesar datos, a menudo tendrá que clasificarlos. Puede ser interesante utilizar una macro para hacer clasificaciones rápidamente.
Para simplificar futuras clasificaciones, nuestro objetivo realizar únicamente las siguientes acciones:
-
Hacer clic en la columna que se desea ordenar.
-
Ejecutar un atajo de teclado (por ejemplo, CtrlMayúsJ).
Por lo tanto, nuestra macro tendrá que seleccionar el rango, identificar la columna a ordenar y luego ordenar los datos.
Abra el libro ListEmpleados.xlsx.
Abra el editor de VBA.
Acceda al libro de macros personal: en la pestaña Proyecto, despliega VBAProject (PERSONAL. XLSB) y pulse dos veces seguidas en el módulo.
Inserte el siguiente código en un módulo del libro de macros personales (ubicado en el archivo Sub Ordenacion.txt).
Sub ordCol()
Dim NumCol As Integer, RangoActivo As Range
Set RangoActivo = ActiveCell.CurrentRegion
NumCol = ActiveCell.Column
With ThisWorkbook.ActiveSheet.Sort
.SortFields.Clear
.SetRange RangoActivo
.Header = xlYes
.MatchCase = False
.SortFields.Add Key:=Cells(, NumCol), SortOn:=xlSortOnValues, Order:=xlAscending
.Orientation = xlTopToBottom
.Apply
End With
End Sub Salga del editor de VBA y guarde el libro de macros personales.
Si el libro...
Enviar una hoja del cuadro de mando con Outlook
A veces puede ser interesante enviar automáticamente una hoja representativa de su cuadro de mando por correo electrónico a un destinatario específico, en función del valor de un indicador en particular.
Abra el libro MiniTab.xlsm.
Es probable que la hoja que va a enviar tenga fórmulas que dependan de otras hojas o incluso de otros libros de trabajo. Si esta hoja de cálculo se envía de manera aislada, las fórmulas ya no funcionarán. Por lo tanto, será necesario enviar esta hoja con los resultados de los cálculos y no las fórmulas.
Aquí, consideraremos que si nuestro indicador de tasa de entrega, ubicado en la celda B1 de nuestra hoja de tablero, supera el 10%, será necesario enviar una alerta por correo electrónico al destinatario especificado previamente.
Para que este envío se pueda automatizar, debe estar asociado a un evento que se active en función del valor de nuestra celda de referencia (B1). Outlook debe estar abierto y configurado correctamente.
Haga clic con el botón derecho del ratón en la pestaña CuadMando que contiene el valor de referencia.
En el menú emergente que aparece, seleccione la opción Ver código.
Escriba el siguiente código directamente en el módulo o use el archivo Sub EnvioMel.txt :
Private...En conclusión
Para terminar este libro, estos son los últimos consejos que puede aplicar a sus cuadros de mando:
-
No introduzca datos innecesarios.
-
Evite la información redundante
-
No mezcle el contenido de las celdas.
-
Coloque cálculos resumidos en la parte superior de sus listas de datos.
-
Utilice los vínculos entre las hojas y las carpetas.
-
Use los nombres de las celdas.
-
Aplique formato condicional pero sin sobrecargar las tablas con varios colores.
-
Y, por supuesto, revise siempre sus cálculos.