Mi primer reporte en excel explotando base Firebird

La reportería en lo sistemas actuales es parte casi tan esencial como el programa mismo. Los sistemas Aspel no son la excepción, solo que para consultar cualquier informe necesitamos forzosamente entrar al sistema y ocupar una licencia.

La opción que les propongo es completamente distinta y funcional. Tener todo un conjunto de reportes en archivos de excel donde puedan consultar información de forma rápida sin necesidad de entrar al sistema y recordar accesos (Muy útil para los gerentes y directores), además de no consumir licencias del sistema lo que nos ahorrará bastante dinero en este rubro.

 Yo por ejemplo, suelo crear en el disco compartido del servidor, una carpeta llamada "Reportes" y dentro de esta creo carpetas como "Contabilidad", "Finanzas", "RH", "Operaciones", "Inventarios", "Catálogos", "Ventas", etc. Los usuarios solo deben entrar a la carpeta, dirigirse a su área correspondiente, seleccionar el reporte de su elección, poner los datos de entrada correctos (Número de cliente, Rango de fechas, Empresa, etc), presionan un botón de "Generar Reporte" y listo, todos los datos aparecerán tal como ellos lo quieren en un formato que están acostumbrados a manejar, 100% manipulables y sin problemas con el manejo de las impresoras al momento de imprimir (Clásico problema con sistemas Aspel), así mismo, se pueden controlar los permisos de acceso por simples permisos de carpetas en el recurso compartido del servidor.

Asumiremos que tenemos la base de datos tal cual como está después de instalado el sistema que ya desde hace varios años es Firebird; si yo también cuando lo ocupé por primera vez me pregunté ¿Qué rayos es esto?, pero después le encontré las ventajas y me supongo que por eso lo ocupó aspel (Es una base de datos relacional, accesible a través de TCP, soporte de transacciones y lo mejor del caso es una base abierta que no cobra regalías por su instalación).

Lo primero que necesitaremos, será instalar el ODBC de Firebird (Ver artículo Instalación y Configuración ODBC Firebird). Para este primer propósito lo que haremos será un reporte muy sencillo, solo mostrará el catálogo de clientes de la empresa 1; más adelante iremos haciendo reportes más complejos y de cualquier empresa.

Abriremos Microsoft Excel (A partir de Excel 2007). Necesitaremos la pestaña "Programador" ya que necesitaremos insertar un botón de comandos. (Si no sabes cómo hacerlo ve a https://support.office.com/es-hn/article/Mostrar-la-pesta%C3%B1a-Programador-e1192344-5e56-4d45-931b-e5fd9bea2d45?ui=es-ES&rs=es-HN&ad=HN). Te debe aparecer la ficha como se muestra.

Seleccionamos la ficha, seleccionamos el menú gráfico "Insertar" y seleccionamos un "botón (Control de formulario)". Una vez hecho eso, dibujamos el botón en cualquier parte de la hoja 1 de excel. Nos preguntará el nombre de la macro y escribiremos "MostrarReporteCatalogoClientes" y daremos click en Nuevo, les mostrará algo parecido a la siguiente figura.

Ahora si viene lo interesante, para los no programadores se pueden perder un poco, pero trataré de explicar lo mejor posible.

Con esto creamos la función vacía "MostrarReporteCatalogoClientes", quiere decir que cada que presionen el botón que acabamos de crear, se ejecutará el código dentro de esta función. Dentro de estas 2 líneas, pegaremos el siguiente código:

 Sub MostrarReporteCatalogoClientes()
    Sheets(2).Select
    Cells.Select
    On Error Resume Next
    Selection.ListObject.QueryTable.Delete
    On Error GoTo 0
    Selection.ClearContents
      
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("ODBC;DSN=SAE01"), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array("select * from clie01")       
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

Le daremos guardar, cerramos esa ventana y regresamos a nuestro excel. Le damos click derecho al botón que creamos, ahora click izquierdo y editamos el nombre predeterminado por "Generar Reporte", le damos Enter y ya está listo nuestro primer reporte. Solo es cuestión de darle un click para que se abra la hoja 2 con todo el catálogo de clientes.  Guardamos el excel, al tener macros forzosamente deberemos guardarlo con extensión xlsx (Libro de excel habilitado para macros); Así mismo, cuando alguien lo abra deberá habilitar las macros de lo contrario no funcionará el reporte. Bien, veamos paso a paso que significa cada porción de código



Primero le decimos que seleccione la hoja 2 ya que ahíi es donde pondremos nuestro reporte.

Sheets(2).Select


Seleccionamos todas las columnas

    Cells.Select


Si omitimos las siguientes 2 líneas, el reporte solo funcionará una vez, ya que cuando quiera nuevamente actualizar los datos, intentará crear una tabla encima de otra tabla lo cual tirará error. Lo que hacemos es eliminar todas las tablas que existan en la hoja activa

    On Error Resume Next
    Selection.ListObject.QueryTable.Delete


Quitamos el control de errores, ya que en caso de que algo falle no nos dariamos cuenta del error.

    On Error GoTo 0

Eliminamos cualquier dato que el usuario haya dejado en la hoja 2

    Selection.ClearContents



Todo esto es un solo comando, le decimos que en la hoja activa, agregue una tabla dinámica obtenida de un origen de datos, en este caso de Aspel SAE (nuestra ODBC que guardamos como SAE01). Esta tabla la pondrá empezando en la celda A1 y lo que abarque. El comando que le mandaremos es un "Muestrame todo el contenido de la tabla clie01" que es donde se guarda la información del catálogo de clientes. Finalmente el refresh es muy importante ya que si lo omitimos la tabla solo estará en memoria y nunca se mostrarán los datos.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("ODBC;DSN=SAE01"), Destination:=Range("$A$1")).QueryTable
.CommandText = Array("select * from clie01")       
.Refresh BackgroundQuery:=False
End With
 

Como verán es muy sencillo. Toda la magia es en seleccionar correctamente nuestra ODBC y en poner una consulta válida. Si les da error general de ODBC, lo más seguro es que

1. No está correctamente instalado el ODBC (Abre tu ODBC y presiona "Probar conexión", si no te funciona corrige lo necesario).

2. La consulta es errónea (Prueba con el IBExpert que tu consulta funcione)

3. No se escribio correctamente el ODBC (Para mi ejemplo escribí SAE01, pero tu le debes poner tal cual como creaste tu propia ODBC)

 

La creación de más reportes es cuestión de jugar con las consultas, les recomiendo que para consultas complejas siempre prueben primero en el IBExpert antes de pasarlas al reporte de excel, así se ahorrarán muchos dolores de cabeza. Pues bien, yo los dejo esperando que esto haya sido de utilidad.

 

 

 

Comentarios   

+1 #7 Super User 12-01-2017 02:45
Cito a Fernando:
Me pregunto cómo serie para los casos de update o delete desde excel para firebird



Lo que debes de hacer es crear tu conexión a la base de datos y tirar comandos como quieras. No te olvides de insetar la referencia de Microsoft ActiveX Data Objects 2.1 Library o superior. Además, deberás tener la ruta de tu dll de firebird.

Dim Record As ADODB.Recordset
Dim Cnx As ADODB.Connection
Set Cnx = New ADODB.Connection
Cnx.Provider = "MSDASQL"
Cnx.ConnectionString = "DRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=masterkey;
DBNAME=C:\database\EMPRE01.fdb;"

On Error Resume Next
Cnx.Open
If Err.Description "" Then
MsgBox "Error, no se puede continuar " & Err.Description
End If

Set Record = New ADODB.Recordset
Record.ActiveConnection = Cnx
Record.Open "UPDATE PROV01 set nombre = 'Prueba' WHERE CLAVE = 'CV01'"


Por supuesto estos valores los puedes tomar de celdas previamente establecidas
0 #6 Gabriel L 12-01-2017 01:11
Estoy haciendo la consulta a traves de una vista que genere en la BD de SAE, pero no muestra nada y manda error
0 #5 Super User 05-04-2016 22:29
Cito a gabriel:
Como se podria poner una variable por ejemplo de rango de clientes?


Hola Gabriel.

Deberás crear 2 variables de Cliente Inicial y Final y el lugar para que el usuario las manipule. En la hoja 1 del exel poner en la celda A1 "Cliente inicial", celda B1 "A" Celda A2 "Cliente final, celda B2 "ZZZZZZ"

Dentro de la macro, al inicio agregas las 2 variables

dim sClienteInicial as string
dim sClienteFinal as string

Ahora alimentas las variables (mismas que puede manipular tu usuario desde la hoja 1)

sClienteInicial = sheets(1).cells(1,2)
sClienteInicial = sheets(1).cells(2,2)

Finalmente modificas tu consulta cuando llenas el command agregando el filtro de clientes.

.CommandText = Array("select * from clie01 WHERE cve_clie BETWEEN '" & sClienteInicial & "' AND '" & sClienteFinal & "'")

Un ejemplo completo de algo muy parecido lo tienes en [spam][spam][spam].cyberadmin.com.mx/portal/8-repo rtes/5-reporte-movimientos-de-cuentas-x-cobrar-sae -firebird
0 #4 gabriel 05-04-2016 21:06
Como se podria poner una variable por ejemplo de rango de clientes?
0 #3 Fernando 18-09-2015 17:36
Genial lo estare probando en estos dias, gracias por el retorno...
0 #2 Super User 18-09-2015 17:31
Cito a Fernando:
Me pregunto cómo serie para los casos de update o delete desde excel para firebird


Desde el editor de Visual BAsic, debes abrir Herramientas --> Referencias y agregar Microsoft ActiveX Data Objects 2.1 Library o posterior. Con esto creas tu conexión (Objeto ADODB.connection, tu objeto Recordset (ADODB.Recordset), creas tu cadena de conexión, te conectas y tiras cualquier operación INSERT, UPDATE y/o delete sin mayor problema. Recuerda que toda operación será transaccionable de inmediato por lo que habrá que tener cuidado.
0 #1 Fernando 18-09-2015 17:14
Me pregunto como seria para las demás sentencias Update,Delete entre otros pero a la inversa de excel para firebird

No tienes derecho suficientes para publicar contenido