Cómo automatizar informes en Excel

Una de las muchas características de Microsoft Excel es su capacidad para automatizar informes. Puedes crear hojas de cálculo interactivas para simplificar las habilidades de los demás para ingresar datos en sus libros, y también puedes automatizar la generación de sus informes. Ambas características requieren algo de conocimiento en Visual Basic. A continuación se describen los pasos para llevar a cabo estas dos tareas.

editarPasos

editarCrea una hoja de cálculo interactiva

1. Decide cuál será la disposición de la hoja de cálculo. Debes diseñar tu hoja de cálculo de modo que las otras personas que la utilicen, puedan encontrar rápidamente los campos que necesitan para ingresar datos.
   
   • Puedes hacer que la disposición de tu hoja de cálculo sea vertical u horizontal. Para la mayoría de los usuarios, es más fácil trabajar con una disposición vertical, particularmente si planeas hacer que impriman la hoja.
2. Crea etiquetas de texto para tu hoja de cálculo. Sería mejor tener una etiqueta arriba de cada columna, así como también una etiqueta en la celda que esté a la izquierda de cada una de las celdas de la columna en donde planeas colocar campos de entrada.
   
3. Presiona simultáneamente las teclas Alt y F11. Esto hará que se abra el editor de Microsoft Visual Basic.
   
4. Haz doble clic en “ThisWorkbook” en el panel llamado “Proyecto-VBA Project” que está en la parte superior del lado izquierdo. Al hacerlo se abrirá una ventana de codificación en la sección principal del editor.
   
5. Selecciona la opción “Procedimiento” en el menú Insertar. Esto te mostrará el cuadro de diálogo “Agregar procedimiento”.
   
6. Ingresa en el campo “Nombre”, un nombre para tu procedimiento. Ponle a tu procedimiento un nombre que sea representativo, como por ejemplo, “SumarGastos” si utilizarás tu hoja de cálculo interactiva para hacer un informe de los gastos asociados a viajes. Haz clic en “Aceptar” para cerrar el diálogo.
   
   • El nombre de tu procedimiento no puede incluir espacios, pero puedes utilizar el guion bajo (_) en su reemplazo.
   • Una vez que se cierre el cuadro de diálogo “Agregar procedimiento”, verás una línea etiquetada como “Public Sub” seguida por el nombre de tu procedimiento. Por debajo de esta línea habrá un espacio y luego verás las palabras “End Sub”.
7. Ingresa el código para cada campo que deba ingresarse en la hoja de cálculo. Deberás escribir dos líneas de código para cada campo.
   • La primera línea de código tiene la forma “Range(“nombreDeLaCelda”).Select”, en donde “nombreDeLaCelda” representa la celda en la cual irá el campo en donde se ingresarán datos. Ésta debería ser la celda que esté inmediatamente a la derecha de una etiqueta de texto. Si tienes una etiqueta de texto en la celda A2, entonces deberías colocar un campo de entrada en la celda B2 (Range(“B2”).Select). Incluye las comillas que están al lado del nombre de la celda, no las que están alrededor de la sentencia de código completa.
     
   • La segunda línea de código tiene la forma “ActiveCell.Value = InputBox(“SolicitudDeEntrada”)”, en donde “SolicitudDeEntrada” representa el texto que aparecerá para indicarle al usuario qué tipo de datos debe ingresar en la celda de entrada. Por ejemplo, si la celda de entrada sirve para ingresar gastos en comida, deberás reemplazar “SolicitudDeEntrada” por el texto “Ingresa el total de todas las comidas, incluyendo las propinas” (incluye las comillas que están alrededor del texto de la solicitud de entrada, pero no aquellas que encierran a todo el comando).
     
8. Ingresa el código de cada campo de cálculo. Nuevamente debes utilizar las dos mismas líneas descriptas arriba, pero esta vez tu ActiveCell.Value es una función numérica o de cálculo, como SUMA, en vez de la función InputBox que utilizaste para mostrar una solicitud de entrada.
   
9. Agrega una línea de código para guardar tu hoja de cálculo interactiva. El formato es “ActiveWorkbook.SaveAs Filename:=”NombreDelArchivo.xls””, en donde “NombreDelArchivo” representa el nombre de tu hoja de cálculo interactiva (incluye las comillas que están alrededor de “NombreDelArchivo”, pero no aquellas que rodean por completo al ejemplo).
   
   • Si tienes Excel 2007 o una versión posterior, puedes sustituir el sufijo “.xls” por “.xlsx”, pero si alguna de las personas que utilizarán la hoja de cálculo tiene Excel 2003 o una versión anterior, no podrán utilizar la hoja de cálculo a menos que tengan un programa con un complemento para su lectura.
10. Presiona las teclas Alt y Q simultáneamente. Esto hará que se cierre el editor de Visual Basic.
11. Presiona las teclas Alt y F8 simultáneamente. Esto hará que se abra el cuadro de diálogo de Macros.
    
12. Haz clic en el nombre de tu procedimiento en la lista de Macros. Si tu procedimiento es el único que está en la lista, se seleccionará automáticamente.
    
13. Haz clic en el botón Opciones. Te solicitarán que ingreses un carácter del teclado para utilizar como atajo del teclado con la tecla Ctrl. Elige una letra que sea representativa y que no se haya utilizado como carácter de atajo, como por ejemplo “e” de “entrada”.
    
14. Haz clic en “Aceptar” para cerrar el cuadro de diálogo de Opciones de Macros. Ahora puedes distribuir tu hoja de cálculo interactiva a aquellos que la utilizarán. Una vez que la abran, pueden utilizar la tecla de atajo para habilitar el ingreso de datos y seguir las instrucciones que creaste para que la completen con sus datos.
    

editarAutomatiza la generación de informes

1. Convierte tu reporte en una tabla dinámica. Las tablas dinámicas están diseñadas para resumir la información de modo que puedas comparar los números e identificar tendencias. Tu tabla dinámica debería conectarse con los datos que estén en cualquier lugar de tu hoja de cálculo o con aquellos que se importaron desde una base de datos.
2. Escribe un código de Visual Basic para abrir y cerrar el informe. Tu código debe ejecutar las funciones que están listadas abajo. Cada función se describirá seguida del código encerrado entre corchetes que sirve para implementarla. Cuando escribas el código, escríbelo en un solo bloque, sustituye los nombres del ejemplo por los tuyos propios, y no incluyas los corchetes que encierran el código de ejemplo.
   • Abre la hoja de cálculo en modo de sólo lectura. [DIM XLAppSet XLApp = CreateObject(“Excel.App”)xlapp.visible=falsexlapp.workbooks.open excellocnombredelarchivo.xls,3,]
   • Actualiza los datos y guarda el reporte, en este ejemplo como PDF con la información de fecha. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, pdflocreportname_ & DatePart(“yyyy,Now()) & “-” & Right(“0” & DatePart(“m”,Now()),2) & “-” Right(“0” & DatePart(“d”,Now()),2) & “.pdf”]. Si el documento que se crea debe tener otro formato diferente, sustituye el formato “.pdf” por la extensión que corresponda.
   • Cierra la hoja de cálculo sin guardarla, luego cierra Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
   • Utiliza “.xlsx” en lugar de “.xls” en el sufijo de la hoja de cálculo si la has guardado en Excel 2007 y formatos posteriores basados en XML.
3. Escribe un código de procesamiento por lotes (batch) para iniciar el código de Visual Basic. Esto es necesario para que el código de Visual Basic se ejecute automáticamente. Si no utilizas un código de procesamiento por lotes, deberás ejecutar tu código VB en forma manual.
   • Tu código debe tener este formato, sustituyendo los nombres de la carpeta y del archivo por los tuyos propios, y omitiendo los corchetes: [cscript /nologo filelocscript.vbs].
4. Escribe un código de procesamiento por lotes para comprobar que el archivo de salida existe tal como lo creaste. Tu código debe ejecutar las funciones descriptas más abajo. Cada función tendrá a su lado el código entre corchetes que sirve para implementarla. . Cuando escribas el código, escríbelo en un solo bloque, sustituye los nombres del ejemplo por los tuyos propios, y no incluyas los corchetes que encierran el código de ejemplo.
   • Comprueba que el archivo de salida exista. [For /f “tokens=2-4 delims=/ “ %%a in (‘date /t’) do set rreport=reportname_%%c-%%a-%%b.pdf)]. Si el formato del archive de salida no es PDF, sustituye el formato de archivo “.pdf” por el que corresponda.
   • Si el archivo o informe de salida existe, envíalo por correo electrónico a las personas que lo necesiten. [If exist pdfloc%rreport% ( sendemail -f remitente@dominiodelremitente.com -t destinatario@dominiodeldestinatario.com -u Scheduled Report -m Report %%report% is attached. -a pdfloc%rreport% -s tuservidor:puerto -xu nombredeusuario -xp contraseña)]
   • Si el archivo o informe de salida no existe en la ubicación especificada, haz que el procedimiento te envíe un mensaje indicándote que ha fallado el envío. [ Else ( sendemail -f remitente@dominiodelremitente.com -t remitente@dominiodelremitente.com -u Report did not run -m file %rreport% does not exist in pdfloc -s tuservidor:puerto -xu nombredeusuario -xp contraseña)]
5. Comprueba que la carpeta “Escritorio” exista en la computadora. Debes comprobar la existencia de la carpeta “Escritorio” tanto para los sistemas de 32 bits como para los de 64 bits. Si no haces esto, deberás abrir Excel y tu hoja de cálculo manualmente.
   • Ubicación en sistemas de 32 bits: c:windowssystem32configsystemprofile
   • Ubicación en sistemas de 64 bits: c:windowssyswow64configsystemprofile
6. Programa una tarea para que ejecute tus códigos si es necesario. Los códigos de procesamiento por lotes deberían ejecutarse en forma secuencial y permanente, sin importar si alguien utiliza o no la computadora. Deberías utilizar la configuración que conceda la mayor cantidad posible de privilegios.

editarConsejos

• Las tareas programadas se ejecutan mejor desde un servidor, por lo general desde una cuenta de sistema con privilegios de administrador. El único inconveniente al ejecutar tareas desde una cuenta del sistema es que la interfaz de usuario no se encuentra disponible. Sin embargo, las tareas automatizadas normalmente están destinadas a ejecutarse en forma oculta, sin que el usuario pueda notarlas.

editarReferencias

• http://www.techrepublic.com/blog/msoffice/save-time-in-excel-with-automated-reports/294
• http://scitechcommentary.blogspot.com/2012/08/guide-to-automating-excel-reports.html#!/2012/08/guide-to-automating-excel-reports.html
• http://msdn.microsoft.com/en-us/library/bb905050(v=vs.80).aspx
• “Overview of PivotTable and PivotChart Reports”, archivo de ayuda de Microsoft 2007, Microsoft Corporation

Source: Wiki News