Índice en una hoja del libro u otro libro con ciertos valores de las filas de un libro excel

periquito · el 28 de julio

Necesito crear una macro para que me genere un índice de todos ciertos valores de todas hojas de un libro.

El archivo excel "FEXCEL" se compone de un numero variable de hojas "HOJA1", "HOJA2", "HOJA3", "HOJA4", ....

Estos nombres de hojas corresponden a las personas que crean cada CD

Cada hoja contiene una referencia de CD, fecha de creación del CD, numero de archivo dentro del CD, nombre del archivo, características etc.

Ejemplo

[TABLE=width: 400]

[TR]

[TD]REF.CD

[/TD]

[TD]FECHA CREACION

[/TD]

[TD]NUM_FICHERO

[/TD]

[TD]NOM_FICH

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD1

[/TD]

[TD]01/02/2012

[/TD]

[TD]1

[/TD]

[TD]ASAD

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD1

[/TD]

[TD]01/02/2012

[/TD]

[TD]2

[/TD]

[TD]DFS

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD1

[/TD]

[TD]01/02/2012

[/TD]

[TD]3

[/TD]

[TD]ERET

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD2

[/TD]

[TD]02/03/2012

[/TD]

[TD]1

[/TD]

[TD]GFGFG

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD2

[/TD]

[TD]02/03/2012

[/TD]

[TD]2

[/TD]

[TD]VBVBVN

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD2

[/TD]

[TD]02/03/2012

[/TD]

[TD]3

[/TD]

[TD]HGHJHG

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD2

[/TD]

[TD]02/03/2012

[/TD]

[TD]4

[/TD]

[TD]GHJGHJ

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD3

[/TD]

[TD]02/03/2012

[/TD]

[TD]1

[/TD]

[TD]JGHJH

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD3

[/TD]

[TD]02/03/2012

[/TD]

[TD]2

[/TD]

[TD]JHGJGH

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]PACOCD3

[/TD]

[TD]02/03/2012

[/TD]

[TD]3

[/TD]

[TD]GHJGHJ

[/TD]

[TD]…

[/TD]

[/TR]

[/TABLE]

Cada hoja puede contener en torno a 400 filas, y se va añadiendo informacion diariamente

Finalmente lo que necesito es generar en otra hoja del archivo excel o si es más sencillo en otro fichero Excel una especie de índice en el que me ordene por fecha los cd creado de todos las personas. Por ejemplo como indico a continuación

[TABLE=width: 400]

[TR]

[TD]FECHA

[/TD]

[TD]REF. CD

[/TD]

[TD]NOM_HOJA

[/TD]

[TD]CANT_ FIECHEROS

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]01/02/2012

[/TD]

[TD]PACOCD1

[/TD]

[TD]HOJA1

[/TD]

[TD]3

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]05/02/2012

[/TD]

[TD]LUISCD1

[/TD]

[TD]HOJA2

[/TD]

[TD]50

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]06/02/2012

[/TD]

[TD]JUANCD1

[/TD]

[TD]HOJA3

[/TD]

[TD]20

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]20/02/2012

[/TD]

[TD]LUISCD2

[/TD]

[TD]HOJA2

[/TD]

[TD]60

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]02/03/2012

[/TD]

[TD]PACOCD2

[/TD]

[TD]HOJA1

[/TD]

[TD]4

[/TD]

[TD]…

[/TD]

[/TR]

[TR]

[TD]02/03/2012

[/TD]

[TD]PACOCD3

[/TD]

[TD]HOJA1

[/TD]

[TD]3

[/TD]

[TD]…

[/TD]

[/TR]

[/TABLE]

Esto me está dando mucha guerra y no se ni por dónde empezar.

Si hay alguien que le haya surgido este mismo problema os si sabe por donde empezar se lo agradecería.

tese1969 · el 28 de julio

Buenas, periquito

Debes adjuntar un archivo como ejemplo.........según las normas del foro.

Un saludo,

Tese

periquito · el 29 de julio

Tienes toda la razón tese1969,

Me dio la primera vez error y luego no lo adjunte

Adjunto lo envío

Gracias

Libro1.xls

tese1969 · el 29 de julio

Buenas de nuevo, periquito

Si no te importa, que el archivo que adjuntes tenga la información (ficticia) también de las hojas 2, 3, etc.... para poder ver a qué te refieres realmente......porque con el ejemplo único de la Hoja 1 no se podría llegar a tu Hoja "Indice" y es eso lo que debemos tomar como referencia para ver si funciona la macro.

Al final lo más sencillo es incluir una copia del archivo real que mantenga la estructura (no los datos, obviamente) con algunas filas en cada hoja y poco más.....

Un saludo,

Tese

periquito · el 30 de julio

Ok ahora lo adjunto,

Lo siento

Gracias,

- - - - - Mensaje combinado - - - - -

Adjunto el archivo

Con todos los ejemplos rellenos,

La hoja 1, 2 y 3 son los datos que dispongo en la "base de datos"

La hoja "INDICE ejemplo" debe ser la que se genere automáticamente, y como decía antes si da menos trabajo podría estar en otro fichero, no es necesario que sea una hoja del mismo fichero.

Muchas gracias.

Libro1.xls

tese1969 · el 30 de julio

Buenas, periquito

Pues parece que algo ya vamos obteniendo.....

Te adjunto tu archivo sin la pestaña "Indice Ejemplo"........para que veas el resultado final........además de que cuando comienza a coger los datos de cada una de las pestañas podría interferir (se soluciona fácil pero no vas a tener en realidad dicha pestaña).

Si la estructura cambia (orden ó número de campos, etc....) deberías incluirla en el código......

En fin, a ver qué te parece....

Prueba y me cuentas,

Un saludo,

Tese

periquito.xls

periquito · el 31 de julio

Gracias tese1969, en principio eso es lo que quiero,

lo único como podria discriminar para que solo salga una linea por CD

Es decir que solo muestre:

la referencia del cd

la fecha que corresponde a ese cd

el nombre de la hoja en el que se encuentra

la cantidad de ficheros

De esta forma no duplicar informacion.

Lo voy a estudiar con detenimiento, y te comento

Muchas gracias.

Un saludo,

tese1969 · el 31 de julio

Buenas, periquito

¿Lo que quieres decir es que tomara en cuenta únicamente la cantidad total de ficheros de la misma referencia y fecha?

Por ejemplo, en vez de


     FECHA   REF. CD   NOM_HOJA   CANT_ FICHEROS
       01/02/2012   PACOCD1   Hoja1   1       
       01/02/2012   PACOCD1   Hoja1   2
       01/02/2012   PACOCD1   Hoja1   3   
[/PHP]

algo así?:

[PHP]
FECHA REF. CD NOM_HOJA CANT_ FICHEROS
01/02/2012 PACOCD1 Hoja1 3 (el último valor de las 3 líneas)

[/PHP]

Si es eso lo que necesitas no es muy complicado.....pero te dejo que lo pienses o aclares lo que necesitarías....

Un saludo,

Tese

periquito · el 1 de agosto

Si, Correcto, en este caso sería suficiente que tomara el ultimo valor de las lineas, ya que el numero de fichero, coincide con el numero de ficheroS.

Aunque hay casos en los que por diferentes circunstancias se eliminan algunos archivos de un cd debido a actualizaciones, etc., la anterior coincidencia no se cumple, y deberia contar el numero de registros (lineas) de la hoja que contienen la misma referencia

Otro error que me da al ejecutar la macro, es que una vez que existe la pestaña "INDICE" en vez de actualizarla, da un error y crea otra hoja pero vacía.

Muchas gracias.

Esto me ayudara mucho.

Saludos,

tese1969 · el 1 de agosto

Buenas, periquito

Antes de retocar la macro y volver a subirla para que cuente el nº real de archivos, sólo decirte que el error te lo da porque Excel no permite tener dos hojas con el mismo nombre y como en el código se pide crear una hoja con el nombre "INDICE" si le das la segunda vez, pues dice que ni de coña....jejejej....

Si te interesa contar con diferentes versiones de la hoja se puede solucionar simplemente añadiendo la fechayhora en que se crea al propio nombre y de esta manera es prácticamente imposible que vuelvas a tener ese error....

Si quieres que cree una sola, se puede intentar que compruebe previamente si existe ya y que en tal caso elimine los datos para volver a incluirlos actualizados.....

Ya sabes, a gusto del consumidor......jejejeje

Me pongo con ello.....

Un saludo,

Tese

- - - - - Mensaje combinado - - - - -

Buenas de nuevo, periquito....

Prueba ahora si te gusta cómo calcula el nº de archivos.....y el nombre de la pestaña.....jejejej

Un saludo,

Tese

periquito.xls

periquito · el 2 de agosto

Buenas noches tese1969

Lo primero agradecer toda la ayuda que estas ofreciendo

Al analizar el funcionamiento se me ocurren nuevas modificaciones, y debido a mi desconocimiento en mis primeros paso de este aprendizaje hay cierto codigo que no comprendo y te comento a continuación

Me gustaria conocer el comando para borrar una hoja (determinada o que contenga una cadena de caracteres)
Tengo dudas en la interpretacion del codigo introducido en el ultimo archivo para, que consigue solo mostrar una unica "referencia de cd con el numero de archivos que contiene el CD" que si no me equivoco es el siguiente.
2. ahora = Now()Sheets.Add.Name = "INDICE"
  Range("a1") = ahora
  Range("a1").Select
  Selection.Replace What:=":", Replacement:="_"
  Selection.Replace What:="/", Replacement:="_"
  ahora = Range("a1")
  ActiveSheet.Name = "INDICE " & ahora
  actualizado = ActiveSheet.Name
  Si deseo que sea otra fila la cual no quiero que se repita no se que debo modificar, no me aclaro bien y me genera incorrecciones como que me cambio en todo el archivo las fechas tipo xx/xx/xxx por xx_xx_xxxx, jeje

[*] Tengo otra duda sobre un campo pero creo que conociendo el anterior punto lo pueda solucionar

Bueno voy a seguir repasando la leccion.

Muchas gracias.

Saludos,

tese1969 · el 2 de agosto

Buenas, periquito

Como dijo Jack el destripador.......vayamos por partes.....jejeje

Punto 1.- Una línea del tipo



Sheets("nombre determinado").Delete 

[/CODE]

eliminaría esa hoja (nombre determinado). El valor puede ser una variable que hayas creado anteriormente o un nombre específico que nunca cambie.....de eso va a depender el código previo a esa línea.

Punto 2.- El código que adjuntas lo que hace realmente es obtener el nombre de la hoja "INDICE" pero con el dato de la fecha y hora en el propio nombre, sustituyendo previamente algunos caracteres que no pueden ser parte de un nombre de hoja.

La parte del código que ha cambiado para que realice la aparición de una única línea estaría aquí:

[CODE]
With Sheets(actualizado).Columns("b")
Set c = .Find(cell, LookIn:=xlValues)
If c Is Nothing Then
Sheets(actualizado).Range("a" & lineaindice) = cell.EntireRow.Columns("b").Value
Sheets(actualizado).Range("b" & lineaindice) = cell.EntireRow.Columns("a").Value
Sheets(actualizado).Range("c" & lineaindice) = pestaña.Name
Sheets(actualizado).Range("d" & lineaindice) = cell.EntireRow.Columns("c").Value
lineaindice = lineaindice + 1
Else
c.EntireRow.Columns("d") = c.EntireRow.Columns("d") + 1
End If
End With
[/CODE]

En este caso y según la primera línea, es el dato de la columna "b" de la pestaña creada (ref. cd) el que debe tomar en cuenta para saber si ya está en dicha pestaña. Si está (te irías al "Else"), pues que sume uno a la cantidad de la columna d...........y si no está.........(a partir de "If c is Nothing Then") pues entonces incluye todos los datos nuevos.

En cuanto a lo de la modificación de las fechas, es obvio que si has pensado que debías cambiar el código que tú adjuntas para "fijar" un dato, lo que has hecho realmente es que en vez de cambiar el nombre de la hoja, has cambiado en toda la hoja unos caracteres ("/" y ":") por otros.....jejeje........tranquila, que esto pasa cuando empiezas y en cualquier momento.......pero siempre aprendes algo......

En fin, espero haberte ayudado.....

Un saludo,

Tese

periquito · el 7 de agosto

Después de haber analizado y comprendido todo el código, lo he podido adaptar a mis necesidades y funciona correctamente.

Claro que debido al afán de mejorar la macro, me gustaría conocer la posibilidad de que la hoja creada como indice tenga el formato de diseño de pagina y si es posible diseñar una cabecera para que numere las paginas, ademas me genere bordes e introducir colores tanto a celdas y texto,

Ademas me genera en una formato de fecha tipo 02-ene, pero solo es una celda algo raro, supongo que definiendo el formato de todas celdas suprima este bug

Supongo que para esto me tendría que ir a un manual, pero con los comandos básicos, yo podría ir probando hasta conseguir el diseño deseado.

Me esta quedando fenomenal.

Muchas gracias,

tese1969 · el 7 de agosto

Pues como dices, periquito, quizás podrías ir trasteando tú un poco, aunque si adjuntas un archivo con la hoja índice tal y como te gustaría (sobre todo en cuanto a formato, colores, bordes), algo se podría intentar......rizando el rizo....jejeje

Lo del formato diseño de página no lo he entendido del todo.........debe ser que esto de las macros me nubla y no me deja ver otras cosas que están ahí.......jejejeje

Un saludo,

Tese

periquito · el 7 de agosto

Adjunto el fichero (libro) sobre la que hemos trabajado,

En la hoja "indice *" he diseñado el formato aproximado que me gustaria generar, controlando: que salga en una hoja, el tamaño de las columnas, color de relleno de celdas, color de texto, justificacion, etc.

Sigo agradeciendo todo el interes mostrado, muchas gracias.

Saludos

periquito 02-08-2012_modelo formato indice.xls

tese1969 · el 7 de agosto

Buenas, periquito........

Si con esto ya no te vale, me retiro.........jejjejejeej

Ya me dices...

Un saludo,

Tese

periquito 02-08-2012_modelo formato indice.rar

killertapia · el 8 de agosto

Hola que tal.

Primero quiero agradecer, pues me ha servido mucho el foro, ya estuve buscando en todo el foro y este es el tema que más se parece a lo que necesito.

estoy intentando formar una lista "indice" de las hojas que tengo en mi libro, y si se crea otra hoja con una macro agregarla a dicha lista "indice", se que la macro que han diseñado lo hace, pero al ver el código me confundo con la lista que se genera soy muy nuevo para esto del VB.

Saludos

Anexo mi ejemplo

Ejemplo_indice.xls