Ejemplo: Libro Mayor en Excel desde el CONCAR mediante SQL y Macros

Antes de exponer los aplicativos mencionados en la parte introductoria, la idea es que el usuario contable o aquellos que deseen iniciarse en programación de software contables mediante VBA de Excel, observen de modo práctico lo potente y útil que puede resultar obtener conocimientos sobre manejo de base de datos y aplicarlo a su trabajo diario.

Es conocido que en el actual mercado laboral uno de los softwares contables más utilizados es el CONCAR de la empresa Real System. Por ello, este primer ejemplo estará relacionado a este software.

Más adelante en este mismo blog publicare conexiones al SAP, Siscont, Starsoft, etc. Todo en ello fines didácticos y en base a la experiencia acumulada en las diferentes empresas en donde he laborado. Obviamente estas empresas cuentan con las licencias originales correspondientes y el código de programación expuesto en este blog es creación particular de quien suscribe.

Para quienes tienen experiencia utilizando CONCAR saben que los reportes que genera en formato Excel contiene los datos columnados pero muchas veces con espacios adicionales, por ejemplo: Si el número de la cuenta de caja chica chica es "10201" el reporte en excel muestra "10201         ".

Para quienes desean analizar cuentas, efectuar consistencias antes de declaraciones de impuestos o revisar los reportes antes de presentarlos, tratar de cruzar los datos usando fórmulas de búsqueda, tablas dinámicas, etc;  y llegar al formato correcto, puede significar un tiempo adicional.

Además para generar el libro Mayor de 2 ó más empresas y/o para generar reportes de 2 ó más periodos es necesario abrir 2 ó más instancias (Ventanas) del CONCAR.

Por otro lado, el CONCAR muestra el plan de cuentas sin los saldos, es decir cuentas con movimientos y sin movimientos, resulta más útil para el usuario mostrar sólo las cuentas que tienen registros y luego proceder a generar el libro Mayor.

También es conocido si Ud. cuenta con la versión DBase del CONCAR primero tiene que Consolidar, tal vez primero haya tenido que Reindexar, etc.

Pues con cuatro Controles Active X y una conexión SQL puede obtener los movimientos de cuenta bien columnados sin la necesidad de formatear, reindexar, consolidar, abrir varias instancias del CONCAR y todo ello ¡En segundos!

Para lograr esto sigue el siguiente procedimiento:

1.      Ejecuta Excel e inserta un libro vacio, guarda el archivo con el nombre MAYOR (Aplicación) [Si trabajas con Excel 2007 o superior debes de guardar el archivo como Libro de Excel habilitado para Macros]

2.      Cambia el nombre de la hoja a: MAYOR

3.      Digita los encabezados desde las celda A8 hasta la celda O8, tal como se muestra en la siguiente figura:

4.      Desde la ficha Programador (para la versión Excel 2007 o superior) o desde la barra de herramientas Cuadro de Controles (para la versión Excel 2003 o inferior) inserta 3 cuadros combinados y 1 botón de comando en la hoja de cálculo MAYOR

5.      Selecciona el 1er. cuadro combinado, Presiona el botón derecho del mouse, Seleccione Propiedades, Cambie el valor de la propiedad (Name) ComboBox1 por cbo_Empresa

6.      Selecciona el 2do. cuadro combinado, Presiona el botón derecho del mouse, Selecciona Propiedades, Cambia el valor de la propiedad (Name) ComboBox2 por cbo_Año

7.      Selecciona el 3er. cuadro combinado, Presiona el botón derecho del mouse, Selecciona Propiedades, Cambia el valor de la propiedad (Name) ComboBox3 por cbo_Cta

8.      Seleccione el botón de comando, Presiona el botón derecho del mouse, Selecciona Propiedades, Cambia el valor de la propiedad (Name) CommandButton1 por cmd_Mayor y el valor de la propiedad Caption CommandButton1 por EJECUTAR

9.       En la ficha programador presiona Modo Diseño para desactivarlo

10.   Presiona Alt + F11 para ejecutar el Editor de Visual Basic

11.   Presiona Ctrl + R para ejecutar el explorador de proyectos

12.  Presiona Doble Click sobre ThisWorkbook (Este libro), asegúrese de presionar doble click sobre el objeto ThisWorkbook del libro MAYOR (Aplicación) [Cada libro de Excel contiene un objeto ThisWorkbook]. Copia el siguiente código y pegalo en el módulo que acaba de abrir al presionar doble click sobre ThisWorkbook:

Private Sub Workbook_Open()

    Sheets("MAYOR").cbo_Empresa.Clear

    Sheets("MAYOR").cbo_Año.Clear

    Sheets("MAYOR").cbo_Cta.Clear
    Sheets("MAYOR").cbo_Empresa = ""
    Sheets("MAYOR").cbo_Año = ""
    Sheets("MAYOR").cbo_Cta = ""

    Set con = CreateObject("adodb.connection")

    Set rs = CreateObject("adodb.recordset")

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Concar80;Extended Properties = dBase 5.0;"

    rs.Open "SELECT CT_CIA, CT_NOMBRE FROM CTCIAS ORDER BY CT_CIA", con

    Do While Not rs.EOF

        Sheets("MAYOR").cbo_Empresa.AddItem Right(rs![CT_CIA].Value, 2) & " " & rs![CT_NOMBRE].Value

        rs.MoveNext

    Loop

    Set rs = Nothing

    Set con = Nothing

End Sub

13.   Presiona Doble Click sobre Hoja1 (MAYOR). Copia el siguiente código y pegalo en el módulo que acaba de abrir al presionar doble click sobre Sheet1 (MAYOR):

Private Sub cbo_Empresa_Change()

    If Sheets("MAYOR").cbo_Empresa <> "" Then

        Sheets("MAYOR").cbo_Año.Clear

        Dim Archivo As String

        Archivo = Dir("C:\Concar80\CCO*.dbf")

        Do Until Archivo = ""

            If Left(Archivo, 5) = "CCO" & Left(Sheets("MAYOR").cbo_Empresa, 2) Then

                Sheets("MAYOR").cbo_Año.AddItem Left(Right(Archivo, 6), 2)

            End If

            Archivo = Dir

        Loop

    End If

End Sub

Private Sub cbo_Año_Change()

    If Sheets("MAYOR").cbo_Año <> "" Then

        Sheets("MAYOR").cbo_Cta.Clear

        Set con = CreateObject("adodb.connection")

        Set rs = CreateObject("adodb.recordset")

        con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Concar80;Extended Properties = dBase 5.0;"

        rs.Open "SELECT CT_FILE FROM CTCIAS WHERE CT_NOMBRE = '" & Right(Sheets("MAYOR").cbo_Empresa, Len(Sheets("MAYOR").cbo_Empresa) - 3) & "'", con

        Plan_de_Cuentas = Right(rs![CT_FILE], 2)

        rs.Close

        Correlativo = Left(Sheets("MAYOR").cbo_Empresa, 2) & Sheets("MAYOR").cbo_Año

        rs.Open "SELECT DCUENTA, CPL" & Plan_de_Cuentas & ".PDESCRI, SUM(IIF(CCD" & Correlativo & ".DDH='D',CCD" & Correlativo & ".DMNIMPOR,-CCD" & Correlativo & ".DMNIMPOR)) AS 'SALDO MN', SUM(IIF(CCD" & Correlativo & ".DDH='D',CCD" & Correlativo & ".DUSIMPOR,-CCD" & Correlativo & ".DUSIMPOR)) AS 'SALDO US'  FROM CCD" & Correlativo & ", CPL" & Plan_de_Cuentas & " WHERE DSUBDIA <> '99' AND CCD" & Correlativo & ".DCUENTA = CPL" & Plan_de_Cuentas & ".PCUENTA GROUP BY DCUENTA, CPL" & Plan_de_Cuentas & ".PDESCRI", con
        Do While Not rs.EOF

            Soles = Format(rs!['SALDO MN'].Value, "#,##0.00")

            Dolares = Format(rs!['SALDO US'].Value, "#,##0.00")

            Sheets("MAYOR").cbo_Cta.AddItem rs![DCUENTA].Value & " " & rs![PDESCRI].Value & " S/. " & Soles & " US$ " & Dolares

            rs.MoveNext

        Loop

        Set rs = Nothing

        Set con = Nothing

    End If

End Sub

Private Sub cmd_Mayor_Click()

    If Sheets("MAYOR").cbo_Empresa <> "" And Sheets("MAYOR").cbo_Año <> "" And Sheets("MAYOR").cbo_Cta <> "" Then

        Range("A9:IV65536").ClearContents

        If ActiveSheet.AutoFilterMode = True Then

            Range("A4").AutoFilter

        End If

        Set con = CreateObject("adodb.connection")

        Set rs = CreateObject("adodb.recordset")

        con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Concar80;Extended Properties = dBase 5.0;"

        rs.Open "SELECT CT_FILE FROM CTCIAS WHERE CT_NOMBRE = '" & Right(Sheets("MAYOR").cbo_Empresa, Len(Sheets("MAYOR").cbo_Empresa) - 3) & "'", con

        Plan_de_Cuentas = Right(rs![CT_FILE], 2)

        rs.Close

        Correlativo = Left(Sheets("MAYOR").cbo_Empresa, 2) & Sheets("MAYOR").cbo_Año

        rs.Open "SELECT DSUBDIA, DCOMPRO, DSECUE, DFECCOM2, DCUENTA, CPL" & Plan_de_Cuentas & ".PDESCRI, DCODANE, DCENCOS, DCODMON, DTIPDOC, DNUMDOC, DXGLOSA, IIF(CCD" & Correlativo & ".DDH='D',CCD" & Correlativo & ".DMNIMPOR,-CCD" & Correlativo & ".DMNIMPOR) AS 'SALDO MN', IIF(CCD" & Correlativo & ".DDH='D',CCD" & Correlativo & ".DUSIMPOR,-CCD" & Correlativo & ".DUSIMPOR) AS 'SALDO US', (YEAR(CCD" & Correlativo & ".DFECCOM2) & '-' & FORMAT(MONTH(CCD" & Correlativo & ".DFECCOM2),'00')) AS 'PERIODO'  FROM CCD" & Correlativo & ", CPL" & Plan_de_Cuentas & " WHERE CCD" & Correlativo & ".DCUENTA = CPL" & Plan_de_Cuentas & ".PCUENTA AND DCUENTA = '" & Left(Sheets("MAYOR").cbo_Cta, Application.WorksheetFunction.Search(" ", Sheets("MAYOR").cbo_Cta) - 1) & "'", con

        Range("A9").CopyFromRecordset rs

        Set rs = Nothing

        Set con = Nothing

        Cells.Columns.AutoFit

        Range("F1").ColumnWidth = 28

        Range("A9").Select

    Else

        MsgBox "Complete los datos y Ejecute", vbInformation

    End If

End Sub

14.   Importante:

Desde el Editor de Visual Basic presiona Ctrl + H y reemplaza la ruta C:\Concar80 por la ubicación de tu sistema contable (Por ejemplo W:\Concar80\REAL), en todo el Proyecto actual, algo similar a la siguiente figura:

15.   Presiona Alt + F4 para cerrar el editor de Visual Basic

16.   Guarde los cambios

17.   Cierre el libro y vuelva a abrirlo para probar su funcionamiento

Si Ud. procedió con orden en todos los pasos anteriores y logro funcionar correctamente el aplicativo: ¡FELICITACIONES! ¡Eres ordenado!

Sin embargo para quienes nunca han visto el editor de Visual Basic o han grabado una macro en su vida, esto puede parecer demasiado abrumador.

La idea es que sigas los pasos mecánicamente para luego interesarte en el manejo de base de datos y la programación aplicada al entorno contable.

Hasta aquí, ¡y de golpe! Has podido conocer lo potente que resultaba Excel y el Editor de Visual Basic. Has interactuado con módulos, el explorador de proyectos, Controles Active X, copiado y pegado sentencias que utilizan Manejadores de Eventos, Declaración de variables Dim, Estructuras: If End If, Bucles o Ciclos: Do Until Loop y Do While Not Loop, Función Dir, programación sobre la biblioteca de objetos de Excel, Conexiones ADO y para colmo Consultas SQL.

Si te ha resultado interesante, deseas desarrollarte es este campo y adquirir una ventaja competitiva frente a tus colegas, te recomiendo participes del curso de Excel Avanzado, Macros y VBA que dictan varias Universidades e Institutos del país. Asimismo, la lectura de bibliografía relacionada como por ejemplo: Microsoft Excel Visual Basic para Aplicaciones Paso a Paso de Reed Jacobson Microsoft Press.

La siguiente entrada del blog tratará de exponer un aplicativo para:

·         Calcular Detracciones

·         Imprimir la constancia de cálculo

·         Migrar de manera masiva los lotes de detracciones hacia el portal web de SUNAT

·         Luego de pagar las detracciones, registrarlas masivamente hacia el sistema contable

Ello en concordancia con las normas tributarias, política contable, evitando errores en el cálculo, minimizando costos (El pago de la detracción individual es de S/. 3.50 por cada una) (El pago por “n” detracciones, a través del pago masivo, es de S/. 10.50) y mejorando el proceso de registro contable: Sin errores y más rápido!