Foro Excel

Hola a todos,
soy nuevo en esto de las macros, tengo que comparar números y nombres, con números y nombres de bases que se encuentran en excel, en varios libros, lo tengo que hacer con la funcion buscar, pero esto toma mucho tiempo pasando de un libro a otro y de una hoja a otra. Además los libros se cambian con cierta periodicidad.
lo que necesito es una macro que me permita buscar dentro de una carpeta, encada uno de los libros y en todas las hojas, y que al encontrar una coincidencia me genere un hypervinculo a ese libro y pagina especifica.
como dije antes estos libros se cambian constantemente asi que el codigo debe buscar dentro de todos los archivos de excel sin importar si hay muchos o pocos o si guardan nuevos archivos......
les agrdezco si me pueden ayudar

hola, Andres !

puedes lograr que excel haga maravillas por ti, lo unico que necesitas es "decirle" a excel (y de preferencia exactamente):
- que se busca y de donde lo ha de tomar
- donde lo ha de buscar (libro, hoja y rango)
- que necesitas en devolucion
- donde quieres que lo devuelva (y como y donde si hay mas de uno ?)
- y quiza algo mas (como condicionantes de busqueda y demas ?)

podrias hacer un bucle que abra todos los archivos (en la carpeta que le indiques), recorra todas las hojas y busque (en que rangos ?)

podrias hacer rutinas que consulten libros sin abrirlos (mas laborioso pero posible)

podria ser necesario alguna instruccion que prevenga/evite algun dato duplicado u obsoleto (si le dices como identificarlo ?)

lo que no es "recomendable" es que hagas preguntas al estilo de "adivinen lo que estoy pensando..."
imagina que estas "de este lado" (del mensaje) y que no tienes NPI (ni posees informacion) de como (RE)crear un modelo y entorno de trabajo (+/- exactamente) "iguales al tuyo"... como harias para empezar ?

saludos,
hector.

Bueno gracias por responder, lo que tengo es este codigo, pero no funciona,
no se que estoy haciendo mal.

Sub recorrer_libros()
Dim ruta_directorio As String 'Almacena la ruta del directorio dónde esta ejecutandose la macro
Dim archivo As String 'Nombre del arhivo en que se va recorriendo con la macro
Dim Abrir_Archivo As String 'Ruta y nombre del libro que se va Abrir
Dim Archivo_Actual As Workbook 'Asignacion del libro Abierto
Dim Hoja As Worksheet 'Asignacion de la Hoja del libro abierto
Dim SumaTotal As Double 'sumatoria de valores
ruta_directorio = ThisWorkbook.Path
archivo = Dir(ruta_directorio & "\" & "*.xls")
'para recorrer el directorio archivos excel 97-2003
Do While archivo <> "" 'Bucle para recorrer los directorios
If archivo <> ActiveWorkbook.Name Then 'Para evitar que tambien tome en cuenta nuestro libro que tiene la macro
Abrir_Archivo = ruta_directorio & "\" & archivo
Set Archivo_Actual = Workbooks.Open(Abrir_Archivo, 3) 'Abrimos el libro
For Each Hoja In Archivo_Actual.Worksheets 'Bucle para recorrer cada hoja del libro
'Buscar el numero o nombre
Cells.Find(What:=" TextBox1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
MsgBox "El valor en " & Archivo_Actual.Name & " " & Hoja.Name & _
" en el rango A1 es " & Hoja.Range("A1").Value, vbInformation
If IsNumeric(Hoja.Range("A1")) = True Then _
SumaTotal = SumaTotal + Hoja.Range("A1").Value
Next Hoja 'Siguiente Hoja
Archivo_Actual.Close 'Cerramos el libro
End If
archivo = Dir 'Obtiene siguiente entrada del directorio
Loop
MsgBox "La suma total es: " & SumaTotal
End Sub

lo que necesito es pasar la funcion buscar por todas las hojas de diferentes libros que estan dentro de una misma carpeta, estos archivos se cambian constantemente, por eso no puedo encadenar la busqueda a los nombres de los libros, sino a la carpeta y su contenido. cordial saludo y gracias por los consejos.

hola, Andres !

... lo que tengo es este codigo, pero no funciona, no se que estoy haciendo mal.

esta claro el por que no funciona:
1) a las instrucciones con el Cells.Find les falta la indicacion de "cual hoja"
2) en el What:= NO debes delimitar por comillas dobles el (contenido del) TextBox1
3) no creo necesario el Cells.FindNext (a menos claro, que exista mas de una coincidencia ?) ...

For Each Hoja In Archivo_Actual.Worksheets 'Bucle para recorrer cada hoja del libro
'Buscar el numero o nombre
Cells.Find(What:=" TextBox1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate

y falta que comentes acerca de dos de las (re)preguntas del mensaje anterior:
te recuerdo que en la consulta original solicitas establecer hipervinculos <= OJO

- que necesitas en devolucion
- donde quieres que lo devuelva (y como y donde si hay mas de uno ?)
- y quiza algo mas (como condicionantes de busqueda y demas ?)

saludos,
hector.

Buenas noches, le agradezco la respuesta, pero no me quedo claro esto: 1) a las instrucciones con el Cells.Find les falta la indicacion de "cual hoja", vera cada libro tiene varias hojas con nombres diferentes, y la cantidad de hojas varia de libro a libro, existe alguna forma para que revise todas las hojas, en todas las celdas que contengan texto o números?.
Para ser más exacto, yo tengo que revisar los libros de manera manual varias veces al día,por que allí se encuentran nombres de empresas, personas o números de identificación, que son potenciales amenazas para actividad de la empresa donde laboro. todos los archivos son de excel 2003, y cada cierto tiempo se hacen actualizaciones. todos están dentro de una misma carpeta. Por ejemplo imagine que tengo que buscar el número: 12345678 , que es un NIT de una empresa, y tienes que revisar 10 archivos de excel, cada uno con diferentes paginas, digamos que el primero dice: empresas de transporte, y en la primer hoja están todas la empresas de transporte fluvial, en la segunda las de transporte terrestre, en la tercera las de transporte aéreo, y así sucesivamente, en cada hoja está el nombre, el Nit, la dirección y otras cosas de cada empresa, pero no están en el mismo orden en todas las hojas, la forma como se llenó cada hoja es diferente. entonces utilizo la función buscar, introduzco el numero 12345678, tomo el primer libro y la primer hoja y lo busco, sino lo encuentro, paso a la segunda hoja, hasta descartar que ese numero se encuentre en alguna pagina del libro, cuando termino con el primer libro paso al segundo, y así hasta que busque en la ultima hoja del ultimo libro.
si el numero coincide, tengo que llenar un informe donde debo decir en que libro y que hoja lo encontré, para eso necesito el hipervinculo, para saber exactamente donde esta.
En cuanto a lo de que exista más de una coincidencia es posible, porque trabajamos con datos de diversos orígenes. El verdadero problema es que esto tengo que hacerlo tres o cuatro veces en una sola hora, es decir casi 32 consultas a lo largo de día, y es muy tedioso y me hace perder mucho tiempo valioso.
Me gustaría usar un formulario, similar al de búsqueda de excel, y que si existe coincidencia de un dato consultado, el hipervinculo me aparezca en el mismo formulario. O si tiene una opción mejor, pues bienvenida. Usted es el maestro.
Espero haberme hecho comprender esta vez.
cordial saludo.

hola, Andres !

... no me quedo claro esto: 1) a las instrucciones con el Cells.Find les falta la indicacion de "cual hoja"...

me refiero a que tus instrucciones "Cells.Find..." debieran iniciar con "cual hoja" se revisa en cada libro: "Hoja.Cells.Find..."

... Por ejemplo tengo que buscar el número: 12345678, que es un NIT de una empresa, y revisar 10 archivos de excel, cada uno con diferentes paginas...

en esta parte es importante saber si se busca en las celdas por una coincidencia parcial (LookAt:=xlPart), que puede encontrar tanto 12345678 como ABC12345678XYZ, o por una coincidencia total (LookAt:=xlWhole), que solo busca 12345678, lo que si considero es que se debiera buscar en los valores (LookIn:=xlValues) en lugar de buscar en las formulas (?)

... en cada hoja está el nombre, el Nit, la dirección y otras cosas de cada empresa, pero no están en el mismo orden en todas las hojas...

si los registros en las hojas no tienen un orden predeterminado (creo que) conviene tener una lista de los valores buscados y aprovechar cada apertura de libros para buscarlos todos, no abrir todos los libros para buscar UN dato y despues volver a abrir todos los libros para buscar cada uno de los datos siguientes (?)

... si el numero coincide, tengo que llenar un informe donde debo decir en que libro y que hoja lo encontré, para eso necesito el hipervinculo, para saber exactamente donde esta.

pregunta: es importante el hipervinculo ? (o seria suficiente con identificar que el dato fue encontrado en tal libro, en tal hoja y en tal celda ?)

En cuanto a lo de que exista más de una coincidencia es posible, porque trabajamos con datos de diversos orígenes...

razon de mas para evitar (en lo posible) abrir varias veces los libros por cada busqueda necesaria

si razonas los porque's de estas (re)preguntas y das la respuesta requerida, podremos iniciar con la busqueda de soluciones

saludos,
hector.

Buenas noches Héctor, Ok lo de las hojas ya lo entendí, pero tengo una duda con esto. Si en el código coloco Hoja1, pero en el libro la hoja tiene un nombre diferente, la macro tomara esta hoja como Hoja1 o no?.

en cuanto a la forma de búsqueda, tome tus consejos ya que la coincidencia debe ser total y en los libros solo hay valores o texto (alguna veces tengo que buscar por nombre o por apellidos).

"...si los registros en las hojas no tienen un orden predeterminado (creo que) conviene tener una lista de los valores buscados y aprovechar cada apertura de libros para buscarlos todos, no abrir todos los libros para buscar UN dato y despues volver a abrir todos los libros para buscar cada uno de los datos siguientes (?)

R: No puedo crear una lista porque eso me tomaría mucho tiempo, pero no es necesario que los libros se estén abriendo en cada consulta, lo de abrir es porque es la forma como lo haría manualmente, pero los libros pueden estar abiertos todo el tiempo, o mejor aún cerrados, lo que me lleva responder tu siguiente pregunta, ya que no seria necesario el hipervinculo, a mi me basta con saber en que libro, que hoja y que celda esta. Veras mi reporte solo dice con esta empresa o esta persona no debemos hacer negocios ya que registra en la base tal (libro de excel) en la hoja tal, y ya con eso basta.

Una ultima pregunta, existe la forma de hacer la búsqueda sin tener que abrir el libro, donde realizo la consulta, no me refiero al donde hice la macro sino al donde estoy consultando. Si es así como puedo hacerlo, esto me espacio en el escritorio pues trabajo con muchas paginas y archivos abiertos a la vez.
como siempre mis agradecimiento por la ayuda que me puedas brindar.
cordial saludo.

hola, Andres !

... tengo una duda con esto. Si en el código coloco Hoja1, pero en el libro la hoja tiene un nombre diferente, la macro tomara esta hoja como Hoja1 o no?.

si alguna instruccion trata de hacer una referencia a un objeto (hoja) que no existe, la macro se detendaia devolviendo algun error (p.e. error 91: indice fuera del intervalo)

R: No puedo crear una lista porque eso me tomaría mucho tiempo, pero no es necesario que los libros se estén abriendo en cada consulta... basta con saber en que libro, que hoja y que celda esta.

ok, entiendo que usas un formulario (userform) donde preguntas desde el "TextBox1" el dato que se busca, pero no has comentado donde necesitas la "devolucion" (???), o sea, si no puedes tener una lista de los datos a buscar... (podria haber algunas eventualidades con las busquedas siguientes)

... existe la forma de hacer la búsqueda sin tener que abrir el libro ...

si no conoces el nombre exacto de las hojas (aunque se pueden obtener sin abrir los libros) y los rangos precisos donde buscar, lo mejor es dejar de lado el intento de usar libros cerrados

en tanto comentas donde "devolver" si el dato buscado se encuentra o no... comenzare a preparar alguna propuesta

saludos,
hector.

Buenas noches, Hector, bueno lo de las hojas ya lo solucioné, dejé de indexar las páginas por su nombre la indexe por su posición: Sheets (1).Select, así selecciono la primer hoja a la izquierda del libro sin importar su nombre.
La información me gustaría verla en un MsgBox, o en una pagina del libro donde escriba la macro, aunque realmente me inclino por la primera opción.
Muchas Gracias por su tiempo e interés.
Cordial saludo

hola, Andres !

... lo de las hojas... la indexe por su posición: Sheets (1).Select ...
La información me gustaría verla en un MsgBox ...

como ya sabras, hay "mil formas de despellejar un gato", el adjunto es una de ellas (para este caso, tampoco es necesario ".Select"ionar objetos para administrar sus propiedades, colecciones, etc.)
analiza los prodecimientos utilizados (no es "a prueba de balas") y deje para otra ocasion alguna forma de cerrar los libros abiertos cuando cierres el "libro maestro" (o cuando lo decidas ?)

saludos,
hector.

Héctor Miguel, le agradezco mucho por la macro, pero creo que me equivoque cuando dije que la búsqueda se debía hacer Whole, porque intente usar la macro con uno de los libros pero no pude encontrar lo que buscaba, te adjunto uno de los libros en los que tengo que revisar, es pequeño pero te dará una idea, por ejemplo si trato de buscar fiduciaria davivienda, no la encuentra aunque esta en" x85" trate de modificar la macro pero este código es realmente avanzado para mi conocimiento actual, por favor me lo puede explicar.
Un cordial saludo

hola, Andres !

... creo que me equivoque cuando dije que la búsqueda se debía hacer Whole
... ejemplo si trato de buscar fiduciaria davivienda, no la encuentra aunque esta en" x85" trate de modificar la macro pero ...

en la macro del Private Sub CommandButton1_Click() solo cambia dos lineas...

esta: Registrado = Application.CountIf(Hoja.UsedRange, TextBox1)
por esta: Registrado = Application.CountIf(Hoja.UsedRange, "*" & TextBox1 & "*")

y esta: Label3 = Hoja.Cells.Find(TextBox1, Hoja.[a1], xlValues, xlWhole).Address
por esta: Label3 = Hoja.Cells.Find(TextBox1, Hoja.[a1], xlValues, xlPart).Address

OJO: deberas tener cuidado porque (ahora) vas a encontrar algunas (semi)coindidencias... todas aquellas que tengan una "ambiguedad" suficiente como para incluir otras similares p.e....

si buscas solo por "colombia", encontrarias indistintamente: bancolombia, credicolombia, ... de colombia, etc.

saludos,
hector.

Buenas noches, Héctor , le agradezco mucho, con los cambios que le hiciste a la macro, quedo perfecta, le agregue un botón para lanzarla y ya. Mil gracias.