Translate

domingo, 13 de mayo de 2012

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!

16 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Hola Francisco

    Seguire publicando en la medida del tiempo disponible, en particular tengo como objetivo diseñar este blog a 2 años, asi es que recien esta naciendo.

    Por lo pronto acabo de publicar algo rápido que adapte de la red: Registro masivo de tareas en Outlook desde Excel con VBA

    Espero y sirva a quien intente usarlo

    Suerte!

    ResponderEliminar
  3. Estimado Abraham, no tendras algun ejemplo con macros para exportar informacion del excel al concar, tengo informacion de un sistema contable que quisiera pasar al concar, agradeceria mucho tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Francisco,

      Disculpa la demora en responder, hoy regrese de vacaciones.

      Yo utilizo dos formas para migrar registros al Concar:

      1. Creo una plantilla dbf y la cargo a través de: Utilitarios / Movimientos de Datos / Carga de Comprobantes

      2. Mediante la sentencia Insert de SQL, luego Utilitarios / Reindexar Movimientos (Para Dbase)

      De una u otra manera para poder darte mayor detalle escribeme en privado a: abraham_ramirez_v@yahoo.es

      Saludos,
      Abraham

      Eliminar
  4. Te saluda Gustavo A. Sebastiani Cépeda del portal de Excel para Contadores: www.excelnegocios.com

    Excelente web! felicitaciones, siempre revisare este espacio, y compartire tus trabajos.

    Si deseas puedes hacer lo mismo y si necesitas posicionarte más podriamos intercambiar enlaces.

    Cualquier contacto puedes hacerlo a: excelparacontadores@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Gustavo!

      Ya no hemos comunicado por mensaje privado.

      Solo para formalizar, excelente tu web!

      Más adelante publicaré un formulario que utiliza la consulta de RUC desde MS Excel publicado en tu web: http://excelnegocios.com/tag/buscar-ruc-sunat-desde-excel/

      Saludos,
      Abraham

      Eliminar
  5. Hola Abraham,muy agradecido por tu ejemplo que sirve de mucha ayuda para los que usamos el concar, segun el link

    http://interfacescontablesvbaexcel.blogspot.com/2012/05/ejemplo-libro-mayor-en-excel-desde-el.html


    Aunque al excel le faltaria la descripcion de la razon social de acuerdo a cada RUC que eso se encuentra en el archivo CAN*. con ruta C:\Real\Concar80.
    Por favor podrias ayudarme en agregar esa columna falante.
    Gracias de antemano.

    ResponderEliminar
  6. Hola Ricardo!

    Me comentas como te fue...ahi va la respuesta.

    Suerte!
    Abraham

    En atención a tu consulta, sigue estos pasos:

    1. En la Hoja Excel agrega la columna para el Nombre / Razón Social (Asegurate que sea la columna H)

    2. Efectivamente, la tabla CAN almacena los anexos, sin embargo el correlativo CAN01, CAN02, etc depende de la configuración den el CONCAR menú Utilitarios / Mantenimiento Compañias (Tabla CT_CIAS)

    3. Por último, la consulta SQL ahora debera tener 3 relaciones, El Diario con el Plan de Cuentas, El diario con los Anexos y el Plan de Cuentas con los Anexos

    Todo lo mencionado se encuentra en estas sentencias modificadas (Evento Click del botón Ejecutar)

    Private Sub cmd_Mayor_Click()
    If Sheets("MAYOR").cbo_Empresa <> "" And Sheets("MAYOR").cbo_Año <> "" And Sheets("MAYOR").xls_Ctas.Cells(Sheets("MAYOR").xls_Ctas.ActiveCell.Row, 1) <> "" And Sheets("MAYOR").xls_Ctas.ActiveCell.Row > 1 Then
    Range("A13: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=W:\REAL\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)
    Anexo = 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, CAN" & Anexo & ".ADESANE, 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 & ", CAN" & Anexo & " WHERE CCD" & Correlativo & ".DCUENTA = CPL" & Plan_de_Cuentas & ".PCUENTA AND CCD" & Correlativo & ".DCODANE = CAN" & Anexo & ".ACODANE AND CAN" & Anexo & ".AVANEXO = CPL" & Plan_de_Cuentas & ".PVANEXO AND DCUENTA = '" & Sheets("MAYOR").xls_Ctas.Cells(Sheets("MAYOR").xls_Ctas.ActiveCell.Row, 1) & "'", con
    Range("A13").CopyFromRecordset rs
    Set rs = Nothing
    Set con = Nothing
    Cells.Columns.AutoFit
    Range("F1").ColumnWidth = 28
    Range("A13").Select
    Else
    MsgBox "Complete los datos y Ejecute", vbInformation
    End If
    End Sub

    ResponderEliminar
  7. Hola Abrahan, muy agradecido por tu aporte,pero otra consulta, en el concar agregue unas cuentas contables, pero cuando habro el excel no me aparece lo reciente del plan de cuentas.

    ResponderEliminar
  8. hola abraham no has ingresado el codigo del combo donde va las cuentas a filtrar ,COMBO_CTA falta que lo agregues sino como lo usamos

    ResponderEliminar
  9. Hola! Por favor, lee bien la entrada (De principio a fin)

    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

    Saludos,
    Abraham

    ResponderEliminar
  10. hola abraham, termina el aplicativo de detracciones, porque es uno de los aplicativos que nos servira a todos

    saludos

    ResponderEliminar
  11. Hola abraham una consulta tu sabes como hacer el interface a programa de almacen del concar "SISALM"

    ResponderEliminar
    Respuestas
    1. Hola Ricardo, si se

      Las tablas principales son:

      ALMOVC
      ALMOVD
      ALSTOC

      Para la versión CodeBase

      Que versión tienes tú? Igual también migro a la versión SQL

      Saludos,
      Abraham

      Eliminar
  12. Este comentario ha sido eliminado por un administrador del blog.

    ResponderEliminar