Excel - Macro para prepara informacion para CFDI

Hola a todos.

Espero que alguien me pueda ayudar, tengo la siguiente situación:

Estamos implementando los recibos CFDI. La información la tenemos en excel y la debemos de pasar a txt. El problema consiste en que tengo la información de los empleados en varios renglones y sólo debo de tener un renglón por empleado para que se timbre correctamente. Lo que complica es que la cantidad de información es variable entre empleados, es decir, un empleado puede tener 3 conceptos de nomina y otro 10.

Dejo el archivo de excel, marcado con amarillo los campos a copiar. En la primer hoja vienen los datos y en la segunda el ejemplo de como debe de quedar. Había pensado que la macro hago lo siguiente:

1. Anexar una línea entre empleado para identificar los conceptos de cada uno.

2. Empezar a cortar los conceptos y pegarlos en la primer renglón del empleado y eliminar el renglón copiado. Empezar por deducciones y percepciones.

3. Al final ordenarlos para quitar los renglones vacíos.

Los campos que se deben de poner de forma linea son:

Percepciones:

TIPO PERCEPCION CLAVEp CONCEPTOp IMPORTEGRAVADOp IMPORTEEXENTOp

Deducciones:


TIPO DEDUCCION CLAVEd CONCEPTOd IMPORTEGRAVADOd IMPORTEEXENTOd


De antemano gracias.

Atte. José Ayala

• ARCHIVO_EXPORTADO_EDITADO.zip(195,6 KB)

EL resultado fue 275 empleados.

Te mando el archivo con las formulas utilizadas.

Saludos.

• ARCHIVO_EXPORTADO_EDITADO_-_RESUELTO.zip(504,3 KB)

Hola Victor.


Gracias. Es similar a la solución que sugieres pero creo que no me explique muy bien. Trataré de hacerlo nuevamente,

El archivo de excel contiene información del empleado como son Información Personal, Total Percepciones, Total Deducciones y conceptos de nomina (sueldo, impuesto, bono entre otros). La información de un empleado viene en varios Renglones, un renglón por concepto de nomina sin importar si es percepción o deducción.

Las deducciones empiezan en la columna BY, BZ, CA, CB, CC, son los campos que debo poner en forma lineal.

Las percepciones empiezan en la columna BR, BS, BT, BU, BV, son los campos que debo poner en forma lineal.

El problema o lo que requiero consiste en que debo de tener todos los conceptos de un empleado en un solo renglón. Trataré de ejemplificar un pequeño caso, en archivo de excel tienen más columnas pero solo pondré algunas para ver si se entiende. El nombre de las columnas las voy a eliminar y no importa que en empleado la columna BR sea para deducción y en otro empleado sea para percepcion.

• ejemplo.zip(9,0 KB)

checa esto... a ver si te capte la idea.

• salida.zip(115,7 KB)

Hola.

Exacto!!!.....es eso lo que requiero hacer de manera automatizada. Lo único que hizo falta son las ultimas 7 columnas que deben de ir en cada empleado. a partir de CD que se llama DIASINCAPACIDAD, esas columnas deben de ir al final sin importar en que columna queden, es decir, en los que tienen menos conceptos puede quedar en la CE y en algunos otros en la CX.

Lo hiciste a través de una Macro...Yo estoy tratando de hacerlo con una macro pero no he podido definir como identificar el cambio de empleado para que copie los conceptos y elimine los renglones que sobra.

Gracias.

En algunos casos hay inf en esas ultimas 7 columas... la mayoria son ceros... que criterio aplicarias.

Esas son campos obligatorios que se deben de tomar en cuenta sin importar que tienen valor 0. Esas columnas son necesarias para que el proveedor pueda formar el XML.

Saludos.

Quieres decir que si el empleado esta repetido 5 veces... se deberan agregar 5 veces las 7 columnas ?

Por ej. el empleado: HCC12790QNA072014 esta repetido 9 veces y las 8 primeras son ceros (7 ultimas columnas) y la novena tiene inf. Que criterio utilizas o le agregas al final los 9 grupos de las 7 ultimas columnas.

No me explique....se deben de agregar los 7 campos,

DIASINCAPACIDAD TIPO DE INCAPACIDAD DESCUENTOinc DIASte TIPO DE HORAS E HORAS EXTRA IMPORTE PAGADOte


En los casos de que haya percepción con incapacidad los campos que se agregan son los valores de las celdas TIPO DE INCAPACIDAD DESCUENTOinc

Los siguientes campos siempre tendrán los valores
DIASINCAPACIDAD = 0 DIASte = 0 TIPO DE HORAS E = vacio HORAS EXTRA = 0 IMPORTE PAGADOte = 0


En ejemplo siguiente hay una percepción de Incapacidad en los conceptos del empleado. Los valores de las 7 últimas columnas serían

DIASINCAPACIDAD Col1 CD (en excel)
TIPO DE INCAPACIDAD Col2 CE (en excel)
DESCUENTOinc Col3 CF (en excel)
DIASte Col4 CG (en excel)
TIPO DE HORAS E Col5 CH (en excel)
HORAS EXTRA Col6 CI (en excel)
IMPORTE PAGADOte Col7 CJ (en excel)

Col1 Col2 Col3 Col4 Col5 Col6 Col7
001 9862 INCAPACIDAD POR MATERNIDAD -12499.95 0 3 -12499.95 0 Vacio 0 0


En los casos de que no haya incapacidad los valores siempre serian los siguientes


Col1 Col2 Col3 Col4 Col5 Col6 Col7
0 vacío 0 0 vacío 0 0


Creo que no se verán bien organizados los títulos de las columnas en los ejemplos. De la Col1 a la Col7 los valores asignados son los últimos de cada ejemplo.

Disculpa, cómo lograste llegar hasta este punto?

Gracias por todo.

checalo de nuevo...Saludos.

• CADENA.zip(249,4 KB)

Así debe de quedar....ya sería cuestión de revisar los detalles ya que algo que me di cuenta al revisarlo de rápido es lo siguiente.

Hay 2 celdas que hacen falta

TOTAL GRAVADOd en excel BW
TOTAL EXENTOd en excel BX

Los campos anteriores indican el inicio de las Deducciones así como TOTAL GRAVADOp TOTAL EXENTOp indica el inicio de las percepciones.

Se debe de formar de la siguiente manera:

TOTAL GRAVADOp TOTAL EXENTOp Percepciones TOTAL GRAVADOd TOTAL EXENTOd Deducciones

Gracias.

Saludos.

Quede peor.... si estan los dos campos que dices... para ahorrar tiempo, dame el registro completo del empleado: HCC12790QNA072014



como seria su registro de salida y con eso seria el mejor ejemplo... tiene de todo.

Saludos.
Si lo hago con macro... tarda en correr como 4 o 5 min. (soy nuevo en esto... este es mi primer ejercicio).

Hola.

Las celdas que te comentaba son las siguientes:

Marcan el inicio de las Percepciones

TOTAL GRAVADOp --- BP
TOTAL EXENTOp ---- BQ

Marcan el inicio de las Deducciones

TOTAL GRAVADOd --- CL
TOTAL EXENTOd ----- CM

En general como la tienes ya funciona, imagino que es cuestión de indicar las celdas que empieza a copiar.

De acuerdo a la forma en que creaste la macro se puede editar?. lo comento porque hay filas por agregar pero el procedimiento es el mismo que tienes.

Son 5 celdas para cada concepto de Percepción y Deducción en con las celdas que se agregan serian 7 por eso comento que seria cuestión de indicar el inicio para copiar los datos.

Gracias.

Adjunto archivo.

• Formato.zip(8,2 KB)

Nuevamente y con los cambios requeridos.

Saludos...

• CADENA_FINAL.zip(111,8 KB)

Exacto....así sería el final, solo seria pequeños cambios en donde empezar a mover las renglones de deducciones y percepciones porque se deben agregar algunas columnas pero la macro se puede editar.

Quise ver la macro en el archivo que enviaste pero creo que esta en otro documento. La macro la hiciste programada o con la opción de "Grabar Macro"?

Gracias.

Te envío el archivo con macro...

- Respeta los nombres de las hojas DATOS y SALIDA
- La salida esta en blanco... ejecuta la macro y listo... tardara un poco, pero sale.
- Le puse colores para distinguirlos en la salida.

Para futuras corridas... no hagas nada en SALIDA, únicamente monta tu archivo en la hoja DATOS a partir de la celda A2.

En futuros archivos checa que la estructura de las columnas no cambie.

Saludos y me comentas.

• CADENA_DE_DATOS.zip(492,1 KB)

Gracias.....ya vi la macro y entiendo el funcionamiento, voy a anexar algunas columnas y a editar los rangos/celdas de la macro para cada función espero que al final funcione sin ningún problema...

Eres muy bueno en esto de las macros, un par de dudas.....eres de México?, en donde aprendiste a programar macros?......lo pregunto porque quisiera aprender a hacer macros de este tipo.

Saludos.

Yo estoy en el D.F. y me creerás que es mi primer macro que hago... iba viendo paso a paso como se hacia en agravación de macros y le seguía la pista. Yo anteriormente manejaba DBase y algo de lógica me quedo.de echo... analizándolo un poco mas, se puede eficientar muchísimo este proceso, pero ya será mas adelante.


En realidad mi fuerte son las funciones.

Sale... cuídate y suerte, por aquí vamos a estar.

Hola.

Hice una macro para anexar unas quitar y agregar unas columnas, se ejecuta Ctrl + h.

El cambio basicamente consiste en agregar 2 columnas antes de cada bloque y se mueven junto con los movimientos que hiciste.

En donde afecta es al momento de mover los conceptos de percepcion y deducccion ya que para moverlo debe de ser 2 columnas mas que son previas.

En las Percepciones son

^
Percepciones


Ejemplo.


^ Percepcion 001 1003 SALARIO 2200.05 0 ^ Percepcion 020 11625 PRIMA DOMINICAL 73.34 0


Al modifiicar la Macro si me hace los cambios pero esta haciendo algo raro ya que me deja unos espacios. No ubico en donde aplica ese espacios en blancos.

Si no es mucha molestia me podrias ayudar por fa. Ahora el fin de archivo es DI ya que agregue los siguientes caracteres al final

'
'
*
'

Anexo archivo que incluye la macro que realicé.

Gracias.

• CADENA_DE_DATOS_MODIFICADO.zip(269,8 KB)

Nuevamente no capto.

Quieres decir que cada bloque de percepciones BR - BV le quieres anteponer una columna y que esta contenga "Percepcion" ?

Mejor dime como deberá quedar finalmente el registro HCC12790QNA072014 toma este empleado como ejemplo con todo y su registro terminado.

OK. Lo envío, primero ejecuto al Macro que te comento y despues ya que realizaste. En la hoja 2 esta marcado en amarillo dos empleados como debe de quedar. Uno es el el empleado que comentas que tiene incapcidad y otro sin incapacidad.

En la macro que hago pongo valores hasta la celda 20000 pero despues quiero quitar los renglones que hay despues del ultimo empleado hasta el 20000 para eliminar los datos de sobra.

Hay un cambio. Cuando hay incapacidad se deben de agregar las celdas donde indica el inicio del bloque de la incapacidad asi como los campos siguientes. Si no tiene incapacidad no debe de poner el bloque.

los ultimos 4 campos deben de ser:

'
'
*
'
Estos campos ya los trae pero el simbolo ' no se ve.

Gracias.

• CADENA_DE_DATOS_MODIFICADO.zip(485,7 KB)

Te comento lo siguiente:

Veo que alteraste el registro original de la base de datos, es decir, insertaste campos en diferentes posiciones, incluso omitiste 1 de ellos.

Opino que la base debe de ser la original siempre y la insercion u omicion de campos se haga via macro. Te lo comento porque cada que hagas modificaciones pudieras cometer algun error y la macro ya no funcionaria.

Haz una tabla del como vas a armar tu registro final. ej. (no hagas caso a los rangos, es un ejemplo)

1. de la "A" a la "P" y agregar 2 mas ("texto o caracter") y ("texto o caracter")
2. de la "Q" a la BR y agregar 1mas ("texto o caracter")
3, Si "BP" es diferente a cero de la "BT" a la "BZ"
etc. etc.

Los cambios respecto a la tabla original son los siguientes:

1. Eliminar columna CG, CH, CI, CJ

2. Insertar en:

CG = '
CH = *
CI = '

3. ELIMINAR COLUMNA O

4. INSERTAR 2 COLUMNAS EN P

Q = TipoComprobante
P = ^

5. INSERTAR 2 COLUMNAS EN T

U = Emisor
T = ^

6. INSERTAR 2 COLUMNAS EN Y

Z = ExpedidoEn
Y = ^

7. INSERTAR 2 COLUMNAS EN AK

AL = Receptor
AK= ^

8.INSERTAR 2 COLUMNAS EN AQ

AR = Concepto
AQ = ^

9. INSERTAR 2 COLUMNAS EN AY

AZ = Impuestos
AY = ^

10. INSERTAR 2 COLUMNAS EN BC

BD = Retencion
BC = ^

11. INSERTAR 2 COLUMNAS EN BG

BH = Nomina
BG = ^

12. Dar forrmato de fecha AAAA-MM-DD a la columna BO, CP, BQ, BV

13. INSERTAR 2 COLUMNAS EN CE

CF = Percepciones
CE = ^

14. INSERTAR 2 COLUMNAS EN CI

CJ = SI (CK# > 0, "Percepcion", " ")
CI = SI (CJ# = "Percepcion", " ^ ". " ")

15. INSERTAR 2 COLUMNAS EN CP

CQ = Deducciones
CP = ^

16. INSERTAR 2 COLUMNAS EN CT

CU = SI (CV# > 0, "Deduccion", " ")
CT = SI (CU# = "Deduccion", " ^ ", " ")

17. INSERTAR 2 COLUMNAS EN DA

DB = SI (DC# > 0, " Incapacidad", " ")
DA = SI (DC# > 0, " ^ ", " ")

Lo anterior es lo que hice en otra macro y la cadena se forma de acuerdo al archivo que te envie hace un rato.

Otra condicion es que si DC tiene texto "Incapacidad" debe de copiar los datos en caso contrario que los omita.

En el archivo que te mande estan marcado con Amarillos un ejemplo con Incapacidad y otro Sin Incapacidad.

Ojala me pudieras ayudar porque lo debo entregar mañana y al mdificar la macro que hiciste tiene un comprtamiento extraño.

De antemano gracias.

Nooooooooooooooooooooo

La base original nunca se tocara.... el procedimiento sera en la macro para generar la salida.

1. se extrae un bloque de columnas de "DATOS" A - P + espacio + deducciones
2. se extrae un bloque de columnas de "DATOS" O - BR + ' + TEXTO
etc.
etc.

Hola.

Ya hice algunos cambios y ya esta funcionado. Tengo una duda.


Como seria la macro para eliminar los renglones despues del ultimo que tiene datos. La grabacion de macro hace lo siguiente:

Range("A2").Select
Selection.End(xlDown).Select
Range("A23").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp


Eso hace, pero como puedo hacer que cuente los registros, se mueva al renglón de abajo y los elimine?

Utilice un Count pero no se como aplicarlo dentro del Range.

Gracias.

RECUERDA QUE TIENES LA VARIABLE TOPE PERO COMO COMIENZA EN EL RENGLON 2... LE PONGO TOPE +1

AHORA... SI QUIERO SELECCIONAR UN RENGLON DESPUES DEL ULTIMO REGISTRO, PONDRIA.

RANGE("A" & TOPE+2).SELECT
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

CUANDO LO TERMINES... MANDAME UNA COPIA... PORFAS. TIENES SKYPE ?

Hola.

Ya casi queda, Por el momento lo deje en macros separadas. Hace lo siguiente:

1. Ejecuto la macro "Insertar_Columnas_Bloques"

2. Ejecuto la macro "ultimo"

3. Ejecuto la macro "Insertar_Comprobante"

4. El archivo final l guardo como .CSV (separado por comas). Esta separado por " ] "

5. Después lo abro en Notepad para guardarlo como .TXT

Las ultimas columnas de cada renglon es el simbolo * y ' Al abrirlo en Notepa debiera de terminar cada renglon en ]*] pero pareciera en el el archivo de excel hay mas datos despues de la ultima columna y me los separa entonces al abrirlo en Notepad los terminas en ]*]]]]]]]]]]]]]]]


Sabes que puede ser?

En cuento este listo te lo envio.

skype: joseayala.p

Gracias.

• Final.zip(461,4 KB)
• Prueba.zip(31,1 KB)

checa la columna IV lo uso para marcas de controi

Si...ya la elimine voy a tratar de copiar los datos a otro documento de excel o de alguna manera darle formato en el txt.

Saludos.

REVISA ESTA SALIDA Y PASALA A TXT

SI TODO VA BIEN... QUE LE FALTARIA ?

NO TE VEO EN EL SKYPE

• SALIDA_CICLOS.zip(256,2 KB)

tE MANDO EL CSV, ABRELO CON BLOCK DE NORAS O CON EXCEL.

sALUDOS.

• SALIDA_CSV_TXT.zip(32,8 KB)

Hola.

Exacto, así debiera de quedar....que procedimiento aplicaste?

Hola.

Reviviendo el tema. Hace unos años me ayudó janlui a hacer una macro para los recibos cfdi de nómina, espero que aún visite este sitio para ver si me puede explicar un par de dudas.

Estoy actualizando la macro de acuerdo a los cambios del SAT pero al momento de correr la macro y depurarla me manda un error. Dejo el archivo por si alguien puede echarle un "ojo".

La hoja que se llama "proceso" y donde creo que utiliza datos y por lo mismo arroja el error no muestra los datos, a ver si me pueden ayudar a que la hoja se vea "normal".

Saludos y gracias.

• CFDI-ver-03-copia.zip(363,8 KB)

Por aqui ando.