Ejemplo: Registro de Ventas (PLE) desde CONCAR hacia Excel (TXT de migración)

Llevo un poco más de 20 años en la profesión contable y algo de 13 años programando en VBA de Excel. Ahora percibo a la SUNAT más cerca que nunca.

Como PRICO en Febrero, tenemos:

• Declaración Jurada Mensual con Registro de Ventas y Compras Electrónicos (Por los impuestos vencidos en Enero, aunque han aplazado para Mayo la presentación, igual ya lo hice y ya los presente.)

• DAOT

• Declaración Jurada Anual de Renta (Tengo el sano habito de presentar en Febrero, un mes antes)

• Cálculo de la repartición de utilidades

• ITF

• El día a día, los reportes a la casa matriz, la encuesta del BCR, sólo falta el MTPE y el INEI.

En fin, no es una queja, es una realidad!

Sin embargo, la SUNAT ha cursado aproximadamente a 1,500 PRICOS una carta en donde se solicita: Flujogramas, Memorias, Informes de Auditoria, Cuestionarios de Auditoria, los ahora los famosos Libros Electrónicos por el ejercicio 2011. etc. Todo ello en 30 días hábiles!!!

La presente entrada tiene como propósito (nuevamente), destacar lo importante que resulta para la profesión contable saber sobre manejo de Base de datos, Programación y Excel avanzado.

Ah! Olvidaba! Luego de 4 llamadas postergadas, la empresa Real System me ofreció actualizar el CONCAR para el 20/Febrero…mi declaración vencía el 19!

No sé en que acabo el tema, ya que tuve obtener el requerimiento de SUNAT por mi cuenta. En SUNAT nos comentaron que los fabricantes de software contable no les queda otra alternativa: Tienen que adaptarse a sus requerimientos si desean continuar operando en el mercado.

El presente caso es muy particular a la empresa en donde laboro, tal vez usted intente recrearlo y no consiga éxito, tal vez usted no tenga la configuración correcta en su sistema contable, tal vez su Registro de Ventas acumule por rangos los comprobantes (por lo que habría que variar “un poco” el código de programación que se expone), etc.

Ahí vamos…

Desde Excel abra un libro en blanco y guárdelo con el nombre de: PROGRAMA DE LIBROS ELECTRONICOS - PLE (Aplicación), por ejemplo.

Presione ALT + F11 para ejecutar el Editor de Visual Basic para Aplicaciones

Desde el editor de VB, inserte un formulario

Con el formulario seleccionado presione F4 para mostrar la ventana de propiedades y asigne a la propiedad Name el siguiente valor frm_PLE

De manera similar, asigne a la propiedad Caption el siguiente valor PROGRAMA DE LIBROS ELECTRONICOS [PLE]

Desde la esquina inferior derecha del formulario arrastre hacia arriba (cuando el puntero del mouse tome forma de flecha diagonal con dos puntas  ) hasta que logre un tamaño similar al de la siguiente imagen:

 Muestre el cuadro de controles y presione en Toolbar

Si no tiene el control Toolbar, agréguelo de la siguiente forma:

Botón derecho del mouse sobre cualquier control, seleccione controles adicionales

Ubique Microsoft Toolbar Control, seleccione la casilla de verificación con un aspa (x) y presione Aceptar.

Ahora, presione con el mouse el control Toolbar (desde el cuadro de controles) y arrastre el puntero dentro del formulario.

Trate que los controles queden de forma similar a la siguiente imagen:

Presione el botón derecho del Mouse sobre el control Toolbar que acaba de agregar en el formulario y seleccione propiedades.

En la ventana de propiedades asigne a la propiedad Name el siguiente valor Barra_PLE

Ahora agregue botones de menús a su barra de herramientas, presione el botón personalizado

En la ventana de propiedades seleccione la ficha Buttons y presione Insertar Botón

Asigne a la propiedad Caption el siguiente valor REGISTROS

Asigne a la propiedad Style el siguiente valor 5 - tbrDropdown

Repita la operación para el siguiente menú, Presione Insertar Botón, Asigne a la propiedad Caption el siguiente valor LIBROS y Asigne a la propiedad Style el siguiente valor 5 – tbrDropdown

Note como la propiedad Index identifica los menús.

Vuelva al índice 1, es decir al primer botón que inserto, llamado REGISTROS e inserte un submenú, Asigne a la propiedad Text el siguiente valor REGISTRO DE VENTAS... y Asigne a la propiedad Key el siguiente valor REGISTRO DE VENTAS...

Para el menú REGISTROS

Repita la operación agregando los submenús:

REGISTRO DE COMPRAS...

SALIR

Para el menú LIBROS

Repita la operación agregando los submenús:

LIBRO DIARIO...

LIBRO DIARIO FORMATO SIMPLIFICADO...

LIBRO MAYOR...

No olvide asignar la propiedad Text y la propiedad Key para cada submenú

La propiedad Key es muy importante, le va  otorgar funcionamiento con código de programación.

Cuando haya terminado ejecute el formulario con el botón ejecutar o presione F5

Su formulario deberá verse similar a la siguiente imagen:

Cierre el formulario para salir de tiempo de ejecución y vuelva al editor de VB para continuar con la interfase de diseño.

La idea es crear una barra de herramientas que contenga varios submenús, cada submenú llama a un subformulario que solicita el mes y el año, datos mínimos para poder ejecutar nuestros registros y libros electrónicos, en este ejemplo el Registro de Ventas.

El subformulario deberá quedar similar a la siguiente imagen:

Para ello, desde el editor de VB, inserte un formulario y agregue los siguientes controles (trate de seguir el mismo orden)

ORDEN	CANTIDAD	CONTROL	PROPIEDAD	VALOR
1er	1	Userform (Formulario)	Name	frm_REG_VTA_PLE
1er	1	Userform (Formulario)	Caption	REGISTRO DE VENTAS [PLE]
1er	1	Frame (Grupo)	Caption	SELECCIONE EL PERIODO TRIBUTARIO
1er	1	Label (Etiqueta)	Caption	MES
1er	1	Label (Etiqueta)	SpecialEffect	2 - fmSpecialEffcetSunken
2do	1	Label (Etiqueta)	Caption	AÑO
2do	1	Label (Etiqueta)	SpecialEffect	2 - fmSpecialEffcetSunken
1er	1	Combobox (Lista desplegable)	Name	cbo_Mes
1er	1	Textbox (Cuadro de texto)	Name	txt_Año
1er	1	SpinButon (Botón de número)	Name	spn_Año
1er	1	ComandButton (Botón de comando)	Name	cmd_Ejecutar
1er	1	ComandButton (Botón de comando)	Caption	EJECUTAR
2do	1	ComandButton (Botón de comando)	Name	cmd_Cancelar
2do	1	ComandButton (Botón de comando)	Caption	CANCELAR

Se obvia las propiedades Picture y PicturePosition de los botones de comando EJECUTAR y CANCELAR

Ahora agreguemos código de programación que funcione la barra de herramientas.

Seleccione el control Toolbar, presione el botón derecho del mouse y seleccione Ver Código

El evento predeterminado para un control Toolbar es ButtonClick

Desde la ventana de procedimientos cambie el evento a ButtonMenuClick

Y digite el siguiente código:

Ucase es una función de Visual Basic que convierte el texto a Mayúscula. Para minúsculas la función es LCase.

La instrucción Select Case se usa cuando las alternativas en una estructura If Then son muchas. En tal caso conviene evitar If Then y hacer uso de Select Case, que permite ejecutar uno de varios grupos de instrucciones, dependiendo del valor de una expresión.

Sintaxis:

Select Case expresión_prueba

            Case expresión 1

                        Instrucciones 1

Case expresión 2

                        Instrucciones 2

            ……….

            ……….

            Case Else

                        Instrucciones Else

End Select

Líneas atrás, para el submenú REGISTRO DE VENTAS, usted asigno a la propiedad Key el valor REGISTRO DE VENTAS …

Esa propiedad se utiliza como expresión_prueba, luego Select Case ejecuta sólo el bloque de instrucciones que cumplen con la expresión.

Los bloques cuya expresión no coincida con expresión_prueba no se ejecutan, en lenguaje coloquial las sentencias que no cumplen con la expresión_prueba son ignoradas y el cursor en Visual Basic salta de un bloque a otro.

Case Else ejecuta el procedimiento contenido en Instruciones Else en caso expresión_prueba no coincida con los expresiones anteriores.

Para programar la acción en el botón Salir, modifique el procedimiento de la siguiente manera:

Pruebe lo avanzado hasta el momento, ejecute el formulario frm_PLE

Cierre ambos formularios.

Antes de continuar con la programación, es necesario que usted desde MS Excel, nombre una hoja de cálculo como REGISTRO DE VENTAS y agregue los 27 campos o columnas con la estructura publicada para el registro de ventas en la página de SUNAT http://orientacion.sunat.gob.pe/images/imagenes/anexos/8_14Ventas.xls

Estos son los 27 campos + 1 campo adicional que he agregado y al que he llamado TXT

1	Número correlativo del registro o el código único de la operación
2	Fecha de emisión del Comprobante de Pago
3	Fecha de Vencimiento o Fecha de Pago
4	Tipo de Comprobante de Pago o Documento
5	Número serie del comprobante de pago o documento o número de serie de la maquina registradora
6	Número del comprobante de pago o documento. En caso de optar por anotar el importe total de las operaciones realizadas diariamente, registrar el número inicial
7	En caso de optar por anotar el importe total de las operaciones realizadas diariamente, registrar el número final.
8	Número de Documento de Identidad del cliente
9	Apellidos y nombres, denominación o razón social  del cliente
10	Valor facturado de la exportación
11	Base imponible de la operación gravada (3)
12	Importe total de la operación inafecta
13	ISC, de ser el caso
14	IGV y/o IPM (4)
15	Otros tributos y cargos que no forman parte de la base imponible
16	Importe total del comprobante de pago
17	Tipo de cambio
18	Fecha de emisión del comprobante de pago o documento original que se modifica
19	Tipo del comprobante de pago o documento original que se modifica
20	Número de serie del comprobante de pago o documento original que se modifica
21	Número del comprobante de pago o documento original que se modifica.
22	Tipo de Documento de Identidad del cliente
23	Periodo
24	Importe total de la operación exonerada
25	Base imponible de la operación gravada con el IVAP
26	IVAP (5)
27	Indica el estado del comprobante de pago y a la incidencia en la base imponible  en relación al periodo tributario correspondiente
28	TXT

  

La hoja de cálculo deberá de tener la siguiente apariencia:

Ahora vuelva al editor de VB para programar el subformulario frm_REG_VTAS_PLE

Vamos a programar las acciones de los controles cbo_Mes, txt_Año, spn_Año y cmd_Cancelar.

Seleccione el formulario, presione el botón derecho del Mouse y seleccione Ver Código

El evento predeterminado para un formulario es Userform_Click

Desde la ventana de procedimientos cambie el evento a Initialize

Y digite el siguiente código

La primera línea de código selecciona la hoja llamada REGISTRO DE VENTAS

La segunda línea de código declara la variable Mes como Byte

Byte es un tipo de dato que puede contener números que van desde el 0 hasta el 255

Dim es una declaración que reserva un espacio de la memoria para almacenar una variable (en este caso Mes), esta variable permanece almacenada durante la ejecución de la rutina. Cuando termina la rutina, la variable desaparece. Su ámbito de aplicación fue dentro de la rutina, procedimiento, macro o manejador de evento que la contuvo.

Si desea que la variable se mantenga almacenada después de que la rutina termine, entonces debe de declararla como Public, en la sección general del módulo. Las variables declaradas como Public mantiene su valor en memoria y estos valores pueden ser usados en otra rutina, procedimiento, macro o manejador de evento diferente.

Volvamos al tema, la tercera, cuarta y quinta línea de código se conoce como estructura For… Next, esta estructura, comúnmente llamada ciclo o bucle, repite un grupo de instrucciones un número especificado de veces. En este caso en particular la variable Mes toma el valor de 1 hasta 12, es decir el bucle se repite 12 veces.

La instrucción que se a va a repetir 12 veces es: cbo_Mes.AddItem Format(Mes, "00")

Esta instrucción contiene el método AddItem del control combobox llamado Cbo_Mes, el método AddItem agrega Items al combobox, la función Format de VBA le da formato a la variable, para 1 retorna “01”, para 2 retorna “02”… para 12 retorna “12”

Finalmente, la sexta sentencia asigna el valor del año actual para el cuadro de texto txt_Año, para ello se ayuda de la hora de sistema mediante la función Date, luego obtiene el año con la función Year. Ambas funciones de VBA.

Pruebe a digitar VBA.Year, VBA.Date o VBA.Format para que compruebe la jerarquía si lo desea.

Ubique el cursor dentro del procedimiento Private Sub UserForm_Initialize y presione F5 para ejecutar el formulario.

Cierre el formulario, vuelva al editor de VB. Ahora programemos el control de número Spn_Año y el botón de comnado cmd_Cancelar

Seleccione el control Spn_Año, presione el botón derecho del Mouse y elija Ver código.

El evento predeterminado para un control SpinButton es Change

Desde la ventana de procedimientos cambie el evento a SpinDown

Y digite el siguiente código:

Recuerde, cuando el formulario inicia, el valor para el cuadro de texto txt_Año es el año actual, cuando usted presione el botón de número hacia abajo el año actual se reducirá en 1

txt_Año = txt_Año – 1 se lee: El valor actual de txt_Año es igual al valor anterior de txt_Año – 1

De la misma forma proceda para aumentar el año actual en 1, agregando código cuando se presione el botón de número hacia arriba.

Seleccione desde la lista de procedimiento el evento SpinUp y digite txt_Año = txt_Año + 1

Agreguemos código al botón cmd_Cancelar, presione CTRL + R para mostrar el explorador de proyectos, seleccione el formulario frm_REG_VTAS_PLE, presione el botón derecho del Mouse y elija Ver Objeto.

Seleccione el botón de comando cmd_Cancelar, presione el botón derecho del Mouse y elija Ver Código

El evento predeterminado para un CommandButton es Click

Digite Unload Me

Esta sentencia descarga el formulario de la memoria, “lo cierra”

Finalmente, agreguemos código al corazón del aplicativo, el botón de comando cmd_Ejecutar

Intente digitar el siguiente código, para evitar ello, le sugiero: imprimir la imagen, escanearla y convertirla a Word mediante ABBY, OCX o similar.

El primer bloque de sentencias utiliza la estructura If…Then y la función Dir para saber si existen las tablas T_COMP.dbf, T_DOC.dbf, REG_VTA1.dbf y REG_VTA2.dbf

Estas tablas no son propias del CONCAR, son tablas temporales que se eliminan mediante la sentencia Kill, al principio y al final del procedimiento, como forma de mantener la base de datos original.

El uso de las tablas temporales se explicara en breve.

Sheets("REGISTRO DE VENTAS").Range("A2:IV65536").ClearContents borra el contenido de las celdas con excepción de la fila 1, la que contiene el encabezado.

Set con = CreateObject("adodb.connection") asigna la referencia CreateObject("adodb.connection") a la variable con, con es la abreviatura de conexión

Las variables de Objeto deben de asignarse mediante Set, a diferencia de otros tipos de variables como Byte, Long, etc.

ADO es la sigla para Active Data Object, ADO es una biblioteca de objeto, se utiliza como intermediario entre el programa y la base de datos, usando ADO podemos traer registros desde una base de datos externa (dbf, sql, access, etc; dependiendo del proveedor) hacia Excel, inclusive desde un libro Excel a otro libro de Excel, o al mismo libro de Excel (Esto lo veríamos en otra ocasión)…o viceversa para llevar registros desde Excel hacia una base de datos externa. Con ADO se puede crear bases datos, tablas, definir campos, registros etc.

Set rs = CreateObject("adodb.recordset") asigna la referencia CreateObject("adodb.recordset") a la variable rs, rs es la abreviatura de recordset

Recordset es el conjunto de registros que vamos a traer desde CONCAR hacia Excel.

ADO tiene tres componentes principales:

• Connection (Permite establecer una conexión con la base de datos)
• Recordset (Maneja un conjunto de registros de la base de datos)
• Command (Permite enviar órdenes SQL para ser ejecutados por la base de datos)

Connection

con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=W:\REAL\CONCAR80; Extended Properties = dBase 5.0;"

con.Open abre el objeto connection, el método Open va seguido de una cadena de conexión.

La cadena de conexión contiene:

Proveedor, en este caso para un archivo dbf utilizamos Microsoft Jet así: Provider=Microsoft.Jet.OLEDB.4.0

Fuente de datos (Data Source), según la ubicación de la base de datos en su pc, red o servidor, en este caso en esta empresa CONCAR se encuentra ubicado en W:\REAL\CONCAR80

Propiedades extendidas (Extended Properties) en este caso dBase 5.0, es decir el tipo de formato de la base de datos.

Recordset

En la página de SUNAT pueden encontrar la Tabla 10: Tipo de comprobante de pago o documento

http://orientacion.sunat.gob.pe/images/imagenes/contenido/libroyregistros/tablas/9_T10_CompPago.xls

En Concar, la tabla CTA02.dbf contiene los tipos de comprobantes SUNAT, clave 06 (Archivo / Tabla General / Mantenimiento Tabla General)

Sin embargo, existe un detalle: El código de comprobante SUNAT no se encuentra almacenado en un campo exclusivo. El código de comprobante SUNAT es un texto de 2 caracteres empezando desde la derecha, con la particularidad que algunos registros no tienen código!!!

Por ello, la necesidad de crear una tabla temporal, que facilite “traducir” los registros contables y los muestre en el Registro de Ventas con el formato tributario.

Por ejemplo, mientras en la base de datos CONCAR, las transacciones de ventas almacena tipos de documento con código BA, en el Registro de Ventas debe de aparece con código 05 para los boletos aéreos.

' CREA TABLA TEMPORAL T_COMP = TIPO DE COMPROBANTE [TABLA 10 SUNAT]

rs.Open "SELECT TCLAVE, VAL(RIGHT(TDESCRI,2)) AS 'TIP_COMP' INTO T_COMP FROM CTA02 WHERE TCOD ='06' AND VAL(RIGHT(TDESCRI,2)) <> 0.0", con

Esta consulta SQL, selecciona los campos TCLAVE y los 2 caracteres derechos del campo TDESCRI de la tabla CTA02 y con un INTO almacena estos registros en una nueva tabla llamada T_COMP, en paralelo aplica un filtro mediante WHERE cuyos criterios son TCOD = 06, es decir, solo los tipos de documentos de la tabla CTA02 y aquellos registros que resulten diferente de 0.0

A continuación la imagen de la tabla temporal T_COMP

Observe como se ha creado el campo XTIP_COMP_ a pesar de que en nuestra consulta SQL se definió como: AS 'TIP_COMP'

Para la tabla 2 de SUNAT el tipo de documento, si es RUC = 6, si es DNI = 1, etc.

http://orientacion.sunat.gob.pe/images/imagenes/contenido/libroyregistros/tablas/9_T2_Identidad.xls

En Concar, la tabla CAO02.dbf contiene los tipos de documentos SUNAT

La tabla CAO02.dbf es el detalle de la tabla CAN02.dbf (Archivo / Código de Anexos / Mantenimiento de Anexos)

' CREA TABLA TEMPORAL T_DCTO = TIPO DE DOCUMENTO [TABLA 2 SUNAT]

rs.Open "SELECT DISTINCT AVANEXO, ADOCIDE, ANUMIDE INTO T_DCTO FROM CAO02 WHERE AVANEXO ='C' AND ADOCIDE > '0'", con

Esta consulta SQL selecciona los campos AVANEXO, ADOCIDE y ANUMIDE de la tabla CAO02 y con un INTO almacena, estos registros, en una nueva tabla llamada T_DCTO, en paralelo aplica filtros mediante WHERE cuyos criterios son AVANEXO = C, es decir, sólo los clientes y ADOCIDE  <> 0, es decir, aquellos registros que resulten diferente de 0

A continuación la imagen de la tabla temporal T_DCTO

Bien, hasta el momento usted ha creado 2 tablas temporales: T_COMP y T_DCTO

Ahora necesita generar la tabla para el Registro de Ventas, esto lo consigue desde CONCAR (Reportes / Registro de Ventas / Generar Archivo Mensual Ventas)

Para esta empresa, la tabla se crea con el nombre CV031301, donde:

CV = Constante

03 = Empresa

13 = Año

01 = Mes

Para esta empresa, esta tabla contiene los campos necesarios para crear el archivo TXT para SUNAT. A excepción de:

• Periodo
• Importe total de la operación exonerada
• Base imponible de la operación gravada con el IVAP
• IVAP (5)
• Indica el estado del comprobante de pago y a la incidencia en la base imponible en relación al periodo tributario correspondiente

El Periodo se obtiene del formulario que hemos creado líneas atrás.

El Importe total de la operación exonerada, la Base imponible de la operación gravada con el IVAP y el IVAP (5), se obtiene luego de configurar CONCAR, esta empresa no necesita estos campos, por lo que se rellenan con el valor por default 0.00

Para el indicador de estado, se rellena con:

1 = la operación corresponde al periodo

2 = anuladas

Si usted desea registrar la opción 8 ó 9 y rectificar la declaración anterior y pagar la multa e intereses, de ser el caso, edite directamente sobre la hoja de cálculo (columna AA) cuando haya terminado de ejecutar el formulario. Yo no se lo aconsejo.

Volvamos a la programación, el siguiente código crea una nueva tabla temporal llamada REG_VTA1, para ello utiliza INTO luego de relacionar la tabla CV031301 con T_COMP

' CREA TABLA TEMPORAL REG_VTA1 = REGISTRO DE VENTA RELACIONADO CON TABLA 10 SUNAT PARA TIPO DE DOCUMENTO EMITIDO

rs.Open "SELECT VT_SUBDIA + VT_COMPRO AS 'ID', VT_FECDOC, VT_FECDOC + 30 AS 'F_VCTO', FORMAT(T_COMP.XTIP_COMP_,'00') AS 'TCOMP'," & _

" VT_SERDOC, VT_NUMDOC, 0 AS 'NUM_FIN', VT_RUC, VT_NOMBRE, VT_MNEXPO, VT_MNAFEC, VT_MNINAF, VT_MNISC, VT_MNIGV, VT_MNOTR, VT_MNTOTAL," & _

"VT_TIPCAM, VT_REFFEC, FORMAT(IIF(VT_REFTD IS NULL, 'NA',VT_REFTD),'00') AS 'TD_REF', VT_REFSER, VT_REFERE INTO REG_VTA1" & _

" FROM CV03" & Right(txt_Año, 2) & cbo_Mes & " INNER JOIN T_COMP ON CV03" & Right(txt_Año, 2) & cbo_Mes & ".VT_TIPDOC = T_COMP.TCLAVE", con

Note que el nombre de la tabla CV031301 se ha generalizado a CV03" & Right(txt_Año, 2) & cbo_Mes

Se podría generalizar también el código de la empresa, bastaría con adaptarlo con una anterior entrada de este blog.

Right es una función de Visual Basic que devuelve un texto que contiene un número especificado de caracteres desde el lado derecho de una cadena. En este caso la cadena es el año actual 2013, por lo tanto la función devuelve los 2 caracteres derechos, es decir 13.

Cbo_Mes devuelve lo elegido desde el “01” hasta el “12”, visto líneas atrás.

La unión o relación se crea con INNER JOIN los campos relacionados son TIPDOC = TCLAVE

En la figura se muestra como se relaciona 01 para FT, 07 para NC, etc.

También observe que para dividir una línea larga de programación en varias líneas cortas he cerrado el final de cada sentencia con comilla doble (“) seguido de un ampersand (&) y un guión bajo (_). Luego cada linea empieza con una comilla doble (“). De esta forma para fines didácticos usted puede ver todo el código.

Si usted es minucioso, verá que existen otros detalles como el uso de la función Format o la función IFF propia de lenguaje SQL para colocar “NA” para los campos que no cuenten con las referencias propias de una Nota de Crédito o Nota de Debito.

Vea en la siguiente figura, lo que se acaba de mencionar.

Este registro “NA” aún no existe en nuestra tabla temporal tipos de comprobante (TIP_COMP), debemos de agregar este registro mediante un INSERT INTO para que luego pueda ser relacionada correctamente con el Registro de Ventas

TCLAVE = "NA"

XTIP_COMP_ = "00"

Registros_Comprobante = "'" & TCLAVE & "', '" & XTIP_COMP_ & "'"

rs.Open "INSERT INTO T_COMP(TCLAVE, XTIP_COMP_) VALUES(" & Registros_Comprobante & ")", con

TCLAVE y XTIP_COMP_ son los dos campos que contiene la tabla temporal T_COMP (Asegúrese de no haber activado Option Explicit para que el compilador no exija la declaración de variables de manera previa)

Registros_Comprobante es la variable que concatenan los valores y finalmente INSERT INTO es la sentencia que agrega los nuevos registros a la tabla.

En la siguiente imagen, se aprecia el resultado.

Ya tenemos creada la tabla temporal REG_VTA1 ahora creamos la última tabla temporal llamada REG_VTA2

' CREA TABLA TEMPORAL REG_VTA2 = REGISTRO DE VENTA RELACIONADO CON TABLA 10 SUNAT PARA TIPO DE DOCUMENTO REFERENCIA

rs.Open "SELECT XID_, VT_FECDOC, XF_VCTO_, XTCOMP_, VT_SERDOC, VT_NUMDOC, XNUM_FIN_, VT_RUC, VT_NOMBRE, VT_MNEXPO, VT_MNAFEC, VT_MNINAF," & _

" VT_MNISC, VT_MNIGV, VT_MNOTR, VT_MNTOTAL, VT_TIPCAM, VT_REFFEC, XTIP_COMP_, VT_REFSER, VT_REFERE INTO REG_VTA2" & _

" FROM REG_VTA1 INNER JOIN T_COMP ON REG_VTA1.XTD_REF_ = T_COMP.TCLAVE", con

REG_VTA2 se crea a partir de REG_VTA1 mediante un INTO, luego de relacionar REG_VTA1 con T_COMP, esta vez para cambiar FT por 01, NC por 07. ND por 08 para los campos de referencia, es decir cuando se emiten Notas de Crédito y/o Notas de Débito, es exigencia que el Registro de Ventas identifique el comprobante que dio origen a los ajustes.

Finalmente, a la tabla temporal REG_VTA2 se le relaciona con la tabla temporal T_DCTO para obtener le Registro de Ventas definitivo

' AL REGISTRO DE VENTA TEMPORAL LE AGREGA EL CAMPO TIPO DE DOCUMENTO [TABLA 2 SUNAT]

rs.Open "SELECT REG_VTA2.*, T_DCTO.ADOCIDE FROM REG_VTA2 INNER JOIN T_DCTO ON REG_VTA2.VT_RUC = T_DCTO.ANUMIDE", con

La relación se crea uniendo los campos VT_RUC = ANUMIDE, es decir el número de RUC.

Esta selección hace posible reemplazar los códigos que CONCAR para los tipos de documentos, y mostrar su equivalente según SUNAT, ejemplo: DNI = 01, RUC = 06, etc.

Sheets("REGISTRO DE VENTAS").Range("A2").CopyFromRecordset rs

Con esta sentencia los datos obteniendos mediante SQL se vuelcan sobre la celda A2 de la hoja de cálculo llamada REGISTRO DE VENTAS.

Set rs = Nothing

Set con = Nothing

Estas sentencias liberan de la memoria las variables rs y con

Sheets("REGISTRO DE VENTAS").Range("W2:W" & Range("A65536").End(xlUp).Row & "").Formula = "'" & txt_Año & cbo_Mes & "00"

Esta sentencia rellena la columna W con el Periodo en el formato solicitado por SUNAT, por ejemplo: 20130100

Como el número de filas o registros es indeterminado o variable cada mes o en cada empresa, hacemos referencia absoluta a A65536 (Excel 2003 o inferior), para luego mover el cursor hacia arriba y obtener el número de la última fila o registro.

Sheets("REGISTRO DE VENTAS").Range("X2:Z" & Range("A65536").End(xlUp).Row & "").Formula = 0

Esta sentencia rellena desde la columna X hasta la columna Z con “0” para la columna de Importe total de la operación exonerada, Base imponible de la operación gravada con el IVAP e IVAP (5), correspondientemente.

    Dim Estado As Long

    For Estado = 2 To Sheets("REGISTRO DE VENTAS").Range("A65536").End(xlUp).Row

        If Cells(Estado, 18) = "" Then

            Cells(Estado, 18) = "'" & "01/01/0001"

        End If

        If Cells(Estado, 19) = "" Then

            Cells(Estado, 19) = "'" & "00"

        End If

        If Cells(Estado, 20) = "" Then

            Cells(Estado, 20) = "'" & "-"

        End If

        If Cells(Estado, 21) = "" Then

            Cells(Estado, 21) = "'" & "-"

        End If

        If Cells(Estado, 9) = "ANULADA" Then

            Cells(Estado, 22) = "'" & 0

            Cells(Estado, 27) = "2"

        Else

            Cells(Estado, 27) = "1"

        End If

    Next Estado

La primera línea, de este grupo de sentencias, declara la variable Estado como Long.

Long es un tipo de datos que representa un número entero que puede ir desde: -2,147,483,648 hasta 2,147,483,647, es decir un número mayor al número de filas que puede contener una hoja de cálculo actualmente.

Luego viene un bucle For…Next, que va desde la fila número 2 hasta la última fila que contenga el Registro de Ventas. Nuevamente hacemos referencia absoluta a A65536 (Excel 2003 o inferior), para luego mover el cursor hacia arriba y obtener el número de la última fila o registro.

Comparamos el valor de la columna 18 (Fecha de emisión del comprobante de pago o documento original que se modifica) si es igual a vacío entonces rellenamos la celda con “01/01/0001”, tal como solicita SUNAT.

Comparamos el valor de la columna 19 (Tipo del comprobante de pago o documento original que se modifica) si es igual a vacío entonces rellenamos la celda con “00”, tal como solicita SUNAT.

Comparamos el valor de la columna 20 (Número de serie del comprobante de pago o documento original que se modifica) si es igual a vacío entonces rellenamos la celda con “-”, tal como solicita SUNAT.

Comparamos el valor de la columna 21 (Número del comprobante de pago o documento original que se modifica.) si es igual a vacío entonces rellenamos la celda con “-”, tal como solicita SUNAT.

Comparamos el valor de la columna 9 (Apellidos y nombres, denominación o razón social  del cliente) si es igual a ANULADA entonces:

Rellenamos la columna 22 (Tipo de Documento de Identidad del cliente) con “0”, tal como solicita SUNAT.

Rellenamos la columna 27 (Indica el estado del comprobante de pago y a la incidencia en la base imponible  en relación al periodo tributario correspondiente) con 2, tal como solicita SUNAT.

Si el valor de la columna 9 es diferente de ANULADA entonces rellenamos la columna 27 (Indica el estado del comprobante de pago y a la incidencia en la base imponible  en relación al periodo tributario correspondiente) con 1, tal como solicita SUNAT.

Sheets("REGISTRO DE VENTAS").Range("AB2:AB" & Range("A65536").End(xlUp).Row & "").Formula = "=RC[-5]&""|""&RC[-27]&""|""&TEXT(RC[-26]," & _

"""DD/MM/YYYY"")&""|""&TEXT(RC[-25],""DD/MM/YYYY"")&""|""&RC[-24]&""|""&TEXT(RC[-23],""0000"")&""|""&RC[-22]&""|""&RC[-21]&""|""&RC" & _

"[-6]&""|""&IF(RC[-6]=""1"",RIGHT(RC[-20],8),RC[-20])&""|""&RC[-19]&""|""&TEXT(RC[-18],""0.00"")&""|""&TEXT(RC[-17],""0.00"")&""|""&TEXT" & _

"(RC[-4],""0.00"")&""|""&TEXT(RC[-16],""0.00"")&""|""&TEXT(RC[-15],""0.00"")&""|""&TEXT(RC[-14],""0.00"")&""|""&TEXT(RC" & _

"[-3],""0.00"")&""|""&TEXT(RC[-2],""0.00"")&""|""&TEXT(RC[-13],""0.00"")&""|""&TEXT(RC[-12],""0.00"")&""|""&TEXT(RC[-11],""0.000"")&""|""&TEXT" & _

"(RC[-10],""DD/MM/YYYY"")&""|""&TEXT(RC[-9],""00"")&""|""&TEXT(RC[-8],""0000"")&""|""&RC[-7]&""|""&RC[-1]&""|"""

Esta sentencia rellena la columna AB (columna adicional que hemos llamado TXT), con una fórmula de concatenación, dentro de la fórmula se aprecia la función TEXT para formatear de acuerdo a lo requerido por SUNAT, asimismo el uso de la barra vertical “|” como separador de campos.

If Dir("W:\REAL\CONCAR80\T_COMP.dbf") <> "" Then

        Kill ("W:\REAL\CONCAR80\T_COMP.DBF")

    End If

    If Dir("W:\REAL\CONCAR80\T_DCTO.dbf") <> "" Then

        Kill ("W:\REAL\CONCAR80\T_DCTO.DBF")

    End If

    If Dir("W:\REAL\CONCAR80\REG_VTA1.dbf") <> "" Then

        Kill ("W:\REAL\CONCAR80\REG_VTA1.DBF")

    End If

    If Dir("W:\REAL\CONCAR80\REG_VTA2.dbf") <> "" Then

        Kill ("W:\REAL\CONCAR80\REG_VTA2.DBF")

    End If

Con estas sentencias se inicia y se termina el procedimiento, si por algún motivo las tablas temporales existen, Kill se encarga de eliminarlas, como ya se explico, previamente usamos Dir para saber si existen.

Unload Me

Esta sentencia descarga el formulario de la memoria.

Nota:

A propósito se han dejado expuestos los datos en la hoja de cálculo (con motivo de revisión previa al envío) asícomo la columna AB que contiene la estructura TXT para copiar y pegar en el block de notas y guardarlo con el nombre que solicita SUNAT.

Sin embargo usted puede ir más allá y copiar la columna AB como valores en una nueva hoja de cálculo

Luego tendría que agregar un código similar a:

RUC = InputBox("Ingrese RUC")

Nombre_Archivo_TXT = "LE" & RUC & txt_Año & txt_Mes & "00140100001111"

ActiveWorkbook.SaveAs Filename:="C:\" & Nombre_Archivo_TXT & ".txt", FileFormat:=xlText, CreateBackup:=False

Donde RUC es una variable que guarda el número de RUC digitado en un Inputbox.

En la variable Nombre_Archivo_TXT se guarda el nombre del archivo según lo requiere SUNAT.

Finalmente, se guarda el archivo como TXT en la carpeta o directorio C.

A continuación, alguna imágenes finales que resumen el proceso hasta su consistencia con el programa de libros electrónicos PLE 3.0 SUNAT

Este desarrollo ha sido (como su nombre lo indica) un ejemplo, le deseo suerte y espero que le sirva como guía para llevar a cabo este proyecto en su empresa. Así también como guía para quienes se inician en la programación con VBA de Excel.

Si usted, piensa copiar y/o adaptar este proyecto en otra página web, le pido que por favor, haga referencia mediante un vínculo a este blog.

Les doy gracias a las empresas que me han contactado para llevar cabo sus proyectos de migración hacia CONCAR.

Por último, no olviden que lo explicado es adaptable a cualquier otro software, por nombrar uno de ellos: a SAP Business One, tendrían que apuntar a la tabla de diario OJTD y /o validar con la tabla de Facturación OINV, obviamente los campos cambian pero no seria necesario crear las tablas temporales, el proveedor de la base datos es sqloledb, entre otros detalles.

Saludos y Exitos!!!