PDF de programación - Como implementar tus reports en excel usando macros, automatizar reports

Reporting con Macros

Como implementar tus reports en excel usando macros, automatizar reports

1.­ Índice
2.­ Introducción
2.1.­ Funcionalidades
3.­ Implementación
3.1.­ Formato
3.1.1.­ Hoja menu
3.1.2.­ Hoja definicion_cuentas
3.1.3.­ Hoja definicion_reports
3.1.4.­ Hoja saldos
3.1.5.­ Hoja resultado
3.2.­ Programación
3.2.1.­ Lectura de saldos
3.2.2.­ Ejecución de reports
4.­ Bibliografía
2.­ Introducción
Por  lo  general,  las  empresas  disponen  de  una  herramienta  contable  (léase  contaplus,  SAP  R3,...)  que  les  provee  de  las  operaciones  standard  contables  (apuntes  contables,  cuentas  de  mayor,
acreedores...). Estas herramientas poseen la habilidad de generar reports pero se quedan 'cojos' en la manejabilidad de los mismos.

Al final, el departamento contable realiza descargas de los datos y manualmente realiza macros para suplir las carencias del reporting.

En este manual vamos a realizar una pequeña macro que resuelva las necesidades globales de reporting mensual de una pequeña/mediana empresa.
2.1.­ Funcionalidades
Con esta solución no pretendemos dar cobertura a todas las necesidades de reporting de todas las empresas, si no ser una macro a partir de la cual y mediante ampliaciones, cada empresa pueda añadir
sus necesidades específicas.

Funcionalidades:

Lectura de los saldos por fichero, en este fichero se encontrará la información de la sociedad (solución multisociedad), cuenta contable, año, mes y saldo,
dispondrá de los siguientes campos:

        1.­ Sociedad
        2.­ Año
        3.­ Mes
        4.­ Cuenta Contable
        5.­ Saldo
        6.­ Indicador de fin de fichero
Ejemplo de fichero:

Layout del fichero:

PPSL20080200190000010000000000012,00X
PPSL20080200190000020000000004000,00X
PPSL20080200190000030000000500000,00X
PPSL2008020019000004­000000200000,00X

Tamaño
Campo
4
Sociedad
4
Año
Mes
2
Cuenta Contable 10
Saldo
16
Indicador de fin 1

Ejecución de los reports indicando:

        1.­ Sociedad
        2.­ Año
        3.­ Mes
        4.­ Tipo report (solución con varios reports).
3.­ Implementación
Para realizar la implementación dividiremos las tareas en 2:

Formato: esta tarea engloba crear todas las hojas necesarias para la solución, incluyendo los textos y los botones
Programación: desarrollar las macros para ejecutar las funcionalidades que requieran programación, como:

        1.­ Lectura de saldos
        2.­ Ejecución de reports
3.1.­ Formato
Inicialmente creamos 5 hojas:
m enu: desde donde se ejecutarán las funcionalidades
definicion_cuentas: donde estarán definidas las cuentas contables y a que epígrafes de los diferentes reports se mapean.
definicion_reports: donde se indican el orden de los epígrafes y los literales de los diferentes reports
saldos: donde se graban los saldos leídos por fichero.
resultado: donde se muestra el report.
3.1.1.­ Hoja menu
En esta hoja se definen los siguientes nombres (Insertar>Nombre>Definir):

fichero: celda B3, donde se debe indicar el nombre del fichero que contiene los saldos.
sociedad: celda B9, donde el usuario debe indicar la sociedad.
anno: celda B10, donde se indica el año de los datos del report.
mes: celda B11, donde se indica el mes del report.
report: celda B12, donde se indica el report a ejecutar.

3.1.2.­ Hoja definicion_cuentas
En la primera columna se indicara las cuentas, y en las columnas siguientes se indicarán los epígrafes de cada report
Es muy importante que en la fila 2 se marque el nombre del report ya que nos servirá para identificarlos y ejecutarlo.

En la primera columna está la cuenta.
No todas las cuentas deben aparecer en todos los reports
3.1.3.­ Hoja definicion_reports

Los nombres de la fila 2 deben ser iguales a los de la fila 2 de la hoja de definicion_cuentas, ya que son los nombres de los reports.
3.1.4.­ Hoja saldos
En esta hoja estará la información de las cuentas con sus dimensiones (léase sociedad, año, mes) y el saldo.

3.1.5.­ Hoja resultado
El resultado será el siguiente:

3.2.­ Programación
Antes de empezar a programar se aconseja crear 2 módulos de programación:
El primer modulo (MCONSTANTES) dispone de constantes y de definición de tipos. Se definen las siguientes constantes, con el fin de hacer más legible el código:

'Definicion de hojas
Global Const CONST_HOJA_MENU = "menu"
Global Const CONST_HOJA_DEF_CUENTAS = "definicion_cuentas"
Global Const CONST_HOJA_DEF_REPORTS = "definicion_reports"
Global Const CONST_HOJA_SALDOS = "saldos"
Global Const CONST_HOJA_RESULTADO = "resultado"

Adicionalmente se crean las constantes de error que seguro utilizaremos:

'Definicion de errores
Global Const CONST_ERROR_FALTA_FICHERO = 1
Global Const CONST_ERROR_FALTA_SOCIEDAD = 2
Global Const CONST_ERROR_FALTA_ANNO = 3
Global Const CONST_ERROR_FALTA_MES = 4
Global Const CONST_ERROR_FALTA_REPORT = 5
Global Const CONST_ERROR_FICHERO_NOEXISTE = 6
Global Const CONST_ERROR_REPORT_NOEXISTE = 7

Las constantes de mensajes:

'Definicion de mensajes
Global Const CONST_MENSAJE_FIN_LECTURA = 1
Global Const CONST_MENSAJE_FIN_REPORT = 2

Se indican las constantes de filas:

'Definicion de filas y columnas
Global Const CONST_FILA_INICIO_SALDO = 3
Global Const CONST_FILA_INICIO_DEF_CUENTAS = 3
Global Const CONST_FILA_INICIO_DEF_REPORTS = 3

Y por último se define un tipo que nos servirá para leer el fichero:

'Definición del fichero
Public Type TFICHERO
    sociedad As String * 4
    anno As String * 4
    mes As String * 2
    ctas As String * 10
    saldo As String * 16
    fin As String * 1
    intro As String * 2
End Type

Fíjate que se ha creado un campo que se llama intro que ocupa 2 posiciones; equivale al intro de cambio de línea del fichero.

Una vez definido un Tipo se puede utilizar en la definición de variables de la siguiente manera:

    Dim nombre_variable as tipo_creado

El segundo módulo (MMOSTRAR) dispone de las funciones que muestran información por pantalla, como puede ser mensajes de error o mensajes de fin de proceso:

1. 'Muestra mensaje de error
2. Public Sub mostrar_error(codigo As Long, Optional demas As Variant)
3. Dim mensaje As String
4.   
5.   Select Case codigo
6.     Case CONST_ERROR_FALTA_FICHERO
7.       mensaje = "Para leer el fichero debe indicar el nombre"
8.     Case CONST_ERROR_FALTA_SOCIEDAD
9.       mensaje = "Para ejecutar el report debes indicar la sociedad"
10.    Case CONST_ERROR_FALTA_ANNO
11.      mensaje = "Para ejecutar el report debes indicar el año"
12.    Case CONST_ERROR_FALTA_MES
13.      mensaje = "Para ejecutar el report debes indicar el mes"
14.    Case CONST_ERROR_FALTA_REPORT
15.      mensaje = "Para ejecutar el report debes indicar el tipo de report"
16.    Case CONST_ERROR_FICHERO_NOEXISTE
17.      mensaje = "El fichero " & CStr(demas) & " no existe"
18.        Case  CONST_ERROR_REPORT_NOEXISTE19.            mensaje  =  "El  tipo  de  report  "  &
CStr(demas) & " no existe"
20.  End Select
21.  MsgBox mensaje, vbCritical, "Mensaje de error!!"

22.End Sub

1. 'Muestra el mensaje de finalizacion de proceso
2. Public Sub mostrar_mensaje(codigo As Long, Optional demas As Variant)
3. Dim mensaje As String
4.
5.   Select Case codigo
6.     Case CONST_MENSAJE_FIN_LECTURA
7.       mensaje = "Se realizo la lectura del fichero"
8.     Case CONST_MENSAJE_FIN_REPORT
9.       mensaje = "Se realizo la ejecución del report"
10.  End Select
11.  MsgBox mensaje, vbInformation, "Mensaje de fin proceso"
12.End Sub

3.2.1.­ Lectura de saldos
En el menú se han creado dos botones asociado a la lectura de saldos:

Leer saldos borrando saldos antiguos: borra toda la información que hay en la pestaña saldos y procede a la lectura del fichero.
Leer saldos: sólo lee el fichero de saldos y los acumula a los ya existentes en la pestaña de saldos.

El código asociado a estos dos botones es el siguiente:

1. 'Leer fichero sin borrar datos actuales
2. Private Sub CommandButton1_Click()
3.    Call leer_fichero(False)
4. End Sub

5. 'Leer fichero borrando datos actuales
6. Private Sub CommandButton2_Click()
7.     Call leer_fichero(True)
8. End Sub

3.2.1.1.­ Código principal
El procedimiento leer_fichero tiene un parámetro que le indica si se deben borrar los datos actuales de la hoja de saldos o no.

El algoritmo general de lectura es el siguiente:
si validacion_fichero() entonces
  si borrar_saldos entonces
    borrar_informacion_saldos()
    fila=inicio_fila()
  si no
    fila=buscar_ultima_fila()
  finsi
  fd=abrir_fichero()
  mientras no EOF(fd)
    leer_fichero()
    escribir_informacion(fila)
  finmientras
  cerrar_fichero(fd)
  mostrar_mensaje_fin_lectura()
finsi

El código es el siguiente:
1. Public Sub leer_fichero(borrar_info As Boolean)
2. Dim fila As Long, fd As Long
3. Dim informacion As TFICHERO
4. 
5.   If validar_fichero Then
6.     If borrar_info Then
7.       Call borrar_informacion_saldos
8.       fila = CONST_FILA_INICIO_SALDO
9.     Else
10.      fila = buscar_ultima_fila
11.    End If
12.    fd = abrir_fichero()
13. 
14.    While EOF(fd) = False
15.      Get #fd, , informacion
16.      Sheets(CONST_HOJA_SALDOS).Cells(fila, 1) = informacion.sociedad
17.      Sheets(CONST_HOJA_SALDOS).Cells(fila, 2) = informacion.anno
18.      Sheets(CONST_HOJA_SALDOS).Cells(fila, 3) = informacion.mes
19.      Sheets(CONST_HOJA_SALDOS).Cells(fila, 4) = informacion.ctas
20.      Sheets(CONST_HOJA_SALDOS).Cells(fila, 5) = CDbl(informacion.saldo)
21.      fila = fila + 1
22.    Wend
23.    Call cerrar_fichero (fd)
24.    Call mostrar_mensaje(CONST_MENSAJE_FIN_LECTURA)
25.  End If
26.End Sub

En la línea 14 se ve el uso de la función EOF (End Of File), que nos indica si hemos acabado de leer el fichero.

La función Get sirve para leer un fichero abierto en modo lectura y random.
En la variable fila indicamos en qué fila se va a ir escribiendo la información leída en el fichero, por eso en la fila 21 se incrementa en uno una vez insertada la información.

3.2.1.2.­ Función validar_fichero

El código de la validación del fichero es:
1. Public Function validar_fichero() As