Buscar todas las coincidencias de texto en rango de otro libro cerrado

hola, Brel !

[en resumen]... si realmente va a ser preferible/necesario/ineludible/... que 'el otro' libro permanezca cerrado...
de acuerdo con el ultimo 'detalle' que comentas en esta consulta: -> '... cerrado y con contrase#as apertura/escritura ...'
[me temo que]... 'tendras que' mantener 'ese' archivo... d e s p r o t e g i d o [o abrirlo 'tras bambalinas'] por que ???
1) hasta la fecha [o hasta donde se]...
-> NO EXISTE ningun proveedor de acceso a bases de datos [como leer de libros de excel CERRADOS]...
que 'logre entrar' a bases de datos ENCRIPTADAS de excel :-(
2) las sugerencias que he visto -generalmente- van en el sentido de [y siempre y cuando lo necesites ->por codigo<-]...
-> usar el modelo de objetos de excel...
-> abrir una [nueva] instancia de excel ->CreateObject("Excel.Application")<-
-> abrir el libro, proveyendo password/s e indicando el modo de 'solo lectura'
-> 'tomar' los datos... cerrar el libro [sin guardar los cambios]... y 'terminar' la instancia en la que se abrio el libro 'cerrado' ;)

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

__ la consulta original __

... de lo que hace un 'BuscarV' pero para cuando -pueden existir mas coincidencias, el tipico siguiente de un formulario- ...
Texto a buscar situado en Libro 'Dpto'. Hoja 'Especial'Celda 'B81'
(por si a alguien le sirve el resultado de este se muestra en celda B1 que tiene la siguiente formula:
=SI($B$81="";"";SI(ESNUMERO(COINCIDIR("*"&$B$81&"*";'D:\[Tablas.xls]B'!$B$2:$B$5000;0));BUSCARV("*"&$B$81&"*";'D:\[Tablas.xls]B'!$B$2:$B$5000;1;0);0))
Detalle: Quise traerme los datos del libro 'Tablas' por codigo sin pasarlos a celdas de esta hoja 'Especial' y se eternizaba
o no di con el codigo correcto, claro. Lo digo porque luego vuelco los datos a otras celdas si estoy conforme con las modificaciones
y quiero volver a guardarlos en 'Tablas', pero tambien estoy atascado en eso que dejo para cuando tenga este tema resuelto...)
... cuando hay mas coincidencias esto no sirve (tampoco puedo hacer un formulario de la base porque Excel se queja de demasiados datos)
La base -aunque esta claro en la formula descrita- es: Libro 'Tablas'.Hoja 'B'. Rango 'B2:B5000' (o podriamos decir 'B:B')
[Ambos ficheros estan en la misma ruta]
Con esta formula se cuantas coincidencias hay: =SUMA(SI(ESERROR(ENCONTRAR(B81;'D:\[Tablas.xls]B'!$B$3:$B$5000));0;1))
[la formula es matricial, terminar con control+mayusculas+enter]
Lo que quisiera es que me pusiera en el libro 'Dpto'.Hoja 'Especial'.Rango 'B84:Bxx' donde estan estas coincidencias:
si son 2, que B84=Fila 2600 y B85=Fila 3001, por ejemplo...
De forma que con un click de un boton de comando 'sig' del formulario 'Localizador' -es desde donde hago todo-
pudiera traerme los datos de la siguiente coincidencia y este no estuviera habilitado cuando no hubiera coincidencias
o estuvieramos en la ultima. [el tipico 'siguiente' de un formulario]
Detalle: el libro 'Tablas' esta cerrado y con contrase#as apertura/escritura -sobra decir que son conocidas por mi.
Perdon por el ladrillo, a veces por intentar explicar uno se lia
y seguro me he dejado mas de un detalle interesante/necesario para poder darme ideas.

Hola Héctor y grupo,
tal y como comentaba en el anterior post vinculé 78 celdas del libro
'Dpto'.Hoja 'Especial'.Rango 'B1:B78' con el libro 'Tablas'.Hoja 'B' por
medio de BuscarV. (son los 78 datos asignados a cada cliente [una fila, en
la que encuentra coincidencia con el cliente en columna 'B'], y lo hace sin
problemas manteniendo el libro 'Tablas' cerrado y con contraseñas):

Detalle: Quise traerme los datos del libro 'Tablas' por código sin
pasarlos a celdas de esta hoja 'Especial' y se eternizaba
o no di con el código correcto, claro. Lo digo porque luego vuelco los
datos a otras celdas si estoy conforme con las modificaciones
y quiero volver a guardarlos en 'Tablas', pero también estoy atascado en
eso que dejo para cuando tenga este tema resuelto...)

Lo que busco es que si hay más coincidencias:
=SUMA(SI(ESERROR(ENCONTRAR(B81;'D:\[Tablas.xls]B'!$B$3:$B$5000));0;1))
encontrar la forma de que por un botón de comando 'sig' del formulario
'Localizador' traerme al mismo sitio [libro 'Dpto'.Hoja 'Especial'.Rango
'B1:B78'] la siguiente coincidencia encontrada.

Hasta aquí todo lo hado sin abrir ni desencriptar la base y es rápido (*),
otra cosa es que -intuyo- deberé hacerlo para devolver datos a esa base,
cuando haya modificaciones, claro. Estructura de ambos libros, en cuanto a
las Hojas implicadas:
(*) Insisto que intenté hacer todo por código sin meter datos a ninguna hoja
y se ralentizaba tanto -mi código- que opté por tu solución de una hoja
oculta y accesible por contraseña, no era mi idea pero si una solución
factible y rápida.
Libro 'Tablas'.Hoja 'B' --> Estructura:
Fila 1 Títulos: Nº / Cliente / CIF / Expdte / Gestor / ... (misma estructura
en fila)

Libro 'Dpto'.Hoja 'Especial' --> Estructura:
Columna A Títulos: Nº / Cliente / CIF / Expdte / Gestor / ...
Columna B --> Deposito los datos extraídos de la base coincidentes 'B1:B78'
(misma estructura en columna) / B81 es el TextBox0 del Formulario
'Localizador'-lo que tecleo para buscar un cliente por nombre, ordenado en
la base alfabéticamente.

No tengo forma de acceder a los atributos de los ficheros, todo esto lo
sitúo en un directorio compartido del servidor que funciona como 'servidor
web' con todas las limitaciones que lleva por política de 'seguridad
industrial'...no se permite 2 accesos simultáneos a un fichero como
escritura aunque se trate de un libro Excel compartido -te lo juro que es
real, he dejado varias neuronas en ese camino sin conseguirlo, es una
multinacional y...bueno, así están las cosas: Varias personas acceden como
lectura pero solo una -yo- puede actualizar. Si en 'Tablas' existe una fila
de títulos y una columna 'A' que es el nº del cliente no es sino por no
volverme loco al actualizar, si logro actualizar desde este formulario
obviamente sobrarían -hasta ahora actualizo directamente en la base.

De nuevo perdón, por extenderme en sin duda temas que no resuelven el
problema suscitado, aunque si aclaren las circunstancias de trabajo.
Saludos.

Brel


"Héctor Miguel" escribió en el mensaje
news:%23Lx9$

hola, Brel !

[en resumen]... si realmente va a ser preferible/necesario/ineludible/...
que 'el otro' libro permanezca cerrado...
de acuerdo con el ultimo 'detalle' que comentas en esta consulta: -> '...
cerrado y con contrase#as apertura/escritura ...'
[me temo que]... 'tendras que' mantener 'ese' archivo... d e s p r o t e g
i d o [o abrirlo 'tras bambalinas'] por que ???
1) hasta la fecha [o hasta donde se]...
-> NO EXISTE ningun proveedor de acceso a bases de datos [como leer de
libros de excel CERRADOS]...
que 'logre entrar' a bases de datos ENCRIPTADAS de excel :-(
2) las sugerencias que he visto -generalmente- van en el sentido de [y
siempre y cuando lo necesites ->por codigo<-]...
-> usar el modelo de objetos de excel...
-> abrir una [nueva] instancia de
excel ->CreateObject("Excel.Application")<-
-> abrir el libro, proveyendo password/s e indicando el modo de 'solo
lectura'
-> 'tomar' los datos... cerrar el libro [sin guardar los cambios]... y
'terminar' la instancia en la que se abrio el libro 'cerrado' ;)

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

__ la consulta original __

... de lo que hace un 'BuscarV' pero para cuando -pueden existir mas
coincidencias, el tipico siguiente de un formulario- ...
Texto a buscar situado en Libro 'Dpto'. Hoja 'Especial'Celda 'B81'
(por si a alguien le sirve el resultado de este se muestra en celda B1
que tiene la siguiente formula:
=SI($B$81="";"";SI(ESNUMERO(COINCIDIR("*"&$B$81&"*";'D:\[Tablas.xls]B'!$B$2:$B$5000;0));BUSCARV("*"&$B$81&"*";'D:\[Tablas.xls]B'!$B$2:$B$5000;1;0);0))
Detalle: Quise traerme los datos del libro 'Tablas' por codigo sin
pasarlos a celdas de esta hoja 'Especial' y se eternizaba
o no di con el codigo correcto, claro. Lo digo porque luego vuelco los
datos a otras celdas si estoy conforme con las modificaciones
y quiero volver a guardarlos en 'Tablas', pero tambien estoy atascado en
eso que dejo para cuando tenga este tema resuelto...)
... cuando hay mas coincidencias esto no sirve (tampoco puedo hacer un
formulario de la base porque Excel se queja de demasiados datos)
La base -aunque esta claro en la formula descrita- es: Libro
'Tablas'.Hoja 'B'. Rango 'B2:B5000' (o podriamos decir 'B:B')
[Ambos ficheros estan en la misma ruta]
Con esta formula se cuantas coincidencias hay:
=SUMA(SI(ESERROR(ENCONTRAR(B81;'D:\[Tablas.xls]B'!$B$3:$B$5000));0;1))
[la formula es matricial, terminar con control+mayusculas+enter]
Lo que quisiera es que me pusiera en el libro 'Dpto'.Hoja
'Especial'.Rango 'B84:Bxx' donde estan estas coincidencias:
si son 2, que B84=Fila 2600 y B85=Fila 3001, por ejemplo...
De forma que con un click de un boton de comando 'sig' del formulario
'Localizador' -es desde donde hago todo-
pudiera traerme los datos de la siguiente coincidencia y este no
estuviera habilitado cuando no hubiera coincidencias
o estuvieramos en la ultima. [el tipico 'siguiente' de un formulario]
Detalle: el libro 'Tablas' esta cerrado y con contrase#as
apertura/escritura -sobra decir que son conocidas por mi.
Perdon por el ladrillo, a veces por intentar explicar uno se lia
y seguro me he dejado mas de un detalle interesante/necesario para poder
darme ideas.

hola, ?

1) 'asumo' que ya tienes resuelto el hecho de que al mantener cerrado [y con claves contra apertura y escritura] tu libro 'Tablas.xls'
al abrir el otro libro 'Dpto' [SIN abrir el 'de base']... -> te solicite la clave para poder actualizar los vinculos -?-
[o incluso, cuando entras/editas -nuevas- fornulas con vinculos al libro que permanece cerrado] ;)

2) si necesitas 'rescatar'... dos [o mas] posibles 'coinciencias' de un mismo dato [ya NO por macros sino con el procedimiento anterior]...
para poder 'averiguar' en cual/es otra/s filas existe/n otro/s dato/s coincidente/s -> con el unico-mismo dato que se busca...
se necesita aplicar formulas 'matriciales' [ya sabes, de las que se 'terminan' con crl+mayus+enter] para estar 'en condiciones de'... ;)

3) para poder 'devolver' datos 'actualizados' [en caso de modificaciones] hacia el libro 'de origen' [cerrado y con contrase#as]...
[supongo que] no podras 'escapar' de la 'necesidad' de -> abrirlo -?-

4) 'suponiendo' que tu libro 'base' [tablas] contiene los datos 'en horizontal' [por columnas] y los registros en filas...
y que a tu libro 'destino' [dpto] lo 'conviertes' a vertical [columnas a filas]...
te prepare un 'ejemplo' [3 'variantes'] PERO... unicamente con 40 filas/registros :-((
[obviamente] no tengo idea de como podria 'comportarse' con 5000 filas y [sobre todo] en un ambiente de redes y 'compartido' .-((

-> me puedes confirmar alguna direccion para hacertelo llegar ?

5) [obviamente] no lo he 'preparado' con alguna prevencion/correccion de posibles 'errores' [p.e. por no haber coincidencias]
pero creo que podras ingeniartelas una vez que lo estudies ;)

saludos,
hector.

__ 1 __

tal y como comentaba en el anterior post vincule 78 celdas del libro 'Dpto'.Hoja 'Especial'.Rango 'B1:B78'
con el libro 'Tablas'.Hoja 'B' por medio de BuscarV.
(son los 78 datos asignados a cada cliente [una fila, en la que encuentra coincidencia con el cliente en columna 'B']
y lo hace sin problemas manteniendo el libro 'Tablas' cerrado y con contrase#as):

__ 2 __

Lo que busco es que si hay mas coincidencias:
=SUMA(SI(ESERROR(ENCONTRAR(B81;'D:\[Tablas.xls]B'!$B$3:$B$5000));0;1))
encontrar la forma de que por un boton de comando 'sig' del formulario 'Localizador'
traerme al mismo sitio [libro 'Dpto'.Hoja 'Especial'.Rango 'B1:B78'] la siguiente coincidencia encontrada.
Hasta aqui todo lo hado sin abrir ni desencriptar la base y es rapido (*)

__ 3 __

otra cosa es que -intuyo- debere hacerlo para devolver datos a esa base, cuando haya modificaciones, claro.

__ 4 __

Estructura de ambos libros, en cuanto a las Hojas implicadas:
(*) Insisto que intente hacer todo por codigo sin meter datos a ninguna hoja y se ralentizaba tanto -mi codigo-
que opte por tu solucion de una hoja oculta y accesible por contrase#a, no era mi idea pero si una solucion factible y rapida.
Libro 'Tablas'.Hoja 'B' --> Estructura:
Fila 1 Titulos: Nº / Cliente / CIF / Expdte / Gestor / ... (misma estructura en fila)
Libro 'Dpto'.Hoja 'Especial' --> Estructura:
Columna A Titulos: Nº / Cliente / CIF / Expdte / Gestor / ...
Columna B --> Deposito los datos extraidos de la base coincidentes 'B1:B78' (misma estructura en columna) / B81 es el TextBox0 del Formulario 'Localizador'
-lo que tecleo para buscar un cliente por nombre, ordenado en la base alfabeticamente.

__ 5 __

No tengo forma de acceder a los atributos de los ficheros, todo esto lo situo en un directorio compartido del servidor
que funciona como 'servidor web' con todas las limitaciones que lleva por politica de 'seguridad industrial'...
no se permite 2 accesos simultaneos a un fichero como escritura aunque se trate de un libro Excel compartido
-te lo juro que es real, he dejado varias neuronas en ese camino sin conseguirlo, es una multinacional y...bueno, asi estan las cosas:
Varias personas acceden como lectura pero solo una -yo- puede actualizar.
Si en 'Tablas' existe una fila de titulos y una columna 'A' que es el nº del cliente no es sino por no volverme loco al actualizar
si logro actualizar desde este formulario obviamente sobrarian -hasta ahora actualizo directamente en la base.
De nuevo perdon, por extenderme en sin duda temas que no resuelven el problema suscitado, aunque si aclaren las circunstancias de trabajo.

Sorry, creo no debí contestar a tu correo personal directamente
Hola Hector,
te agradezco de veras tu entusiasmo en ayudarnos a aprender en tantas cosas,
y tantas veces.
Enviame a la dirección que aparezco en las news sin el NOSPAM.

Estudiaré tus datos y remitiré detalles al grupo.

Sobre lo que dices en tu correo:

1) Exacto, pide la clave al principio, mal menor al tratarse de una vez por
sesión.
2) Estuve probando con código pero se eternizaba. Incluso con crear una
nueva columna en la base que buscara coincidencias de lo tecleado en el otro
libro, pero esto me obligaba a mantenerlos abiertos y quería evitarlo al
tratarse del servidor y las restricciones -y más, comentadas.
3) Como bien dices para devolver datos tendré que abrirlo, pero solo cuando
se actualicen datos volviendolo a cerrar en el mismo proceso.
4) Mil gracias. Probaré aquí -casa- y la semana que viene en curro -lunes
viaje, y semana que viene con fiestas será más difícil, me quedo sólo en el
departamento, será que no sé ser jefe que doy fiesta a todos menos yo...
jeje -.
5) Teóricamente, como hay una celda que lee si hay coincidencias: si su
valor es mayor que 1 (si es 1 sólo hay una coincidencia y ya está en
pantalla) con boton 'sig' enviaré a leer la siguiente hasta igualar el valor
de esa celda (no haya más coincidencias); si el valor de esta celda es 0 (no
hay coincidencias) el boton 'sig' estará inactivo. Todo teoría porque como
no se sacar más coincidencias...jaja

Gracias mil.

Brel
"Héctor Miguel" escribió en el mensaje
news:

hola, ?

1) 'asumo' que ya tienes resuelto el hecho de que al mantener cerrado [y
con claves contra apertura y escritura] tu libro 'Tablas.xls'
al abrir el otro libro 'Dpto' [SIN abrir el 'de base']... -> te
solicite la clave para poder actualizar los vinculos -?-
[o incluso, cuando entras/editas -nuevas- fornulas con vinculos al
libro que permanece cerrado] ;)

2) si necesitas 'rescatar'... dos [o mas] posibles 'coinciencias' de un
mismo dato [ya NO por macros sino con el procedimiento anterior]...
para poder 'averiguar' en cual/es otra/s filas existe/n otro/s dato/s
coincidente/s -> con el unico-mismo dato que se busca...
se necesita aplicar formulas 'matriciales' [ya sabes, de las que se
'terminan' con crl+mayus+enter] para estar 'en condiciones de'... ;)

3) para poder 'devolver' datos 'actualizados' [en caso de modificaciones]
hacia el libro 'de origen' [cerrado y con contrase#as]...
[supongo que] no podras 'escapar' de la 'necesidad' de ->
irlo -?-

4) 'suponiendo' que tu libro 'base' [tablas] contiene los datos 'en
horizontal' [por columnas] y los registros en filas...
y que a tu libro 'destino' [dpto] lo 'conviertes' a vertical [columnas
a filas]...
te prepare un 'ejemplo' [3 'variantes'] PERO... unicamente con 40
filas/registros :-((
[obviamente] no tengo idea de como podria 'comportarse' con 5000 filas
y [sobre todo] en un ambiente de redes y 'compartido' .-((

-> me puedes confirmar alguna direccion para hacertelo llegar ?

5) [obviamente] no lo he 'preparado' con alguna prevencion/correccion de
posibles 'errores' [p.e. por no haber coincidencias]
pero creo que podras ingeniartelas una vez que lo estudies ;)

saludos,
hector.

Hola Héctor,
Una salvedad... si sabemos que hay x registros (pongamos 3 en tu caso del
cliente 10) no sería más fácil identificar en qué filas están los repetidos
y luego sacar los datos con BuscarV directamente a esa fila? Si esto es
posible, con una única columna 'Especial(2)' Columna 'B' valdría no?
Incluso aunque tengamos los 5000 registros de B, si en a3, a4!, a51
(corresponde a las filas en que aparece Cliente 10) no se podría montar los
necesarios BuscarV a cada click del botón 'sig' por código? Así nos
ahorraríamos 78 columnas (B:BC) en Especial.(2) Aunque fuera (en hoja
Especial(2)) con un formato condicional vinculado al valor de 'A1' para todo
la columna 'B' se podrían identificar por código las celdas en letra roja
(x. e.) y averiguar así su fila, no?

No se si me explico. Quizás ante mi falta de perspectiva hago sencillo un
problema complejo...
Saludos.
Brel

"Héctor Miguel" escribió en el mensaje
news:

hola, ?

1) 'asumo' que ya tienes resuelto el hecho de que al mantener cerrado [y
con claves contra apertura y escritura] tu libro 'Tablas.xls'
al abrir el otro libro 'Dpto' [SIN abrir el 'de base']... -> te
solicite la clave para poder actualizar los vinculos -?-
[o incluso, cuando entras/editas -nuevas- fornulas con vinculos al
libro que permanece cerrado] ;)

2) si necesitas 'rescatar'... dos [o mas] posibles 'coinciencias' de un
mismo dato [ya NO por macros sino con el procedimiento anterior]...
para poder 'averiguar' en cual/es otra/s filas existe/n otro/s dato/s
coincidente/s -> con el unico-mismo dato que se busca...
se necesita aplicar formulas 'matriciales' [ya sabes, de las que se
'terminan' con crl+mayus+enter] para estar 'en condiciones de'... ;)

3) para poder 'devolver' datos 'actualizados' [en caso de modificaciones]
hacia el libro 'de origen' [cerrado y con contrase#as]...
[supongo que] no podras 'escapar' de la 'necesidad' de ->
irlo -?-

4) 'suponiendo' que tu libro 'base' [tablas] contiene los datos 'en
horizontal' [por columnas] y los registros en filas...
y que a tu libro 'destino' [dpto] lo 'conviertes' a vertical [columnas
a filas]...
te prepare un 'ejemplo' [3 'variantes'] PERO... unicamente con 40
filas/registros :-((
[obviamente] no tengo idea de como podria 'comportarse' con 5000 filas
y [sobre todo] en un ambiente de redes y 'compartido' .-((

-> me puedes confirmar alguna direccion para hacertelo llegar ?

5) [obviamente] no lo he 'preparado' con alguna prevencion/correccion de
posibles 'errores' [p.e. por no haber coincidencias]
pero creo que podras ingeniartelas una vez que lo estudies ;)

saludos,
hector.

__ 1 __

tal y como comentaba en el anterior post vincule 78 celdas del libro
'Dpto'.Hoja 'Especial'.Rango 'B1:B78'
con el libro 'Tablas'.Hoja 'B' por medio de BuscarV.
(son los 78 datos asignados a cada cliente [una fila, en la que encuentra
coincidencia con el cliente en columna 'B']
y lo hace sin problemas manteniendo el libro 'Tablas' cerrado y con
contrase#as):

__ 2 __

Lo que busco es que si hay mas coincidencias:
=SUMA(SI(ESERROR(ENCONTRAR(B81;'D:\[Tablas.xls]B'!$B$3:$B$5000));0;1))
encontrar la forma de que por un boton de comando 'sig' del formulario
'Localizador'
traerme al mismo sitio [libro 'Dpto'.Hoja 'Especial'.Rango 'B1:B78'] la
siguiente coincidencia encontrada.
Hasta aqui todo lo hado sin abrir ni desencriptar la base y es rapido (*)

__ 3 __

otra cosa es que -intuyo- debere hacerlo para devolver datos a esa base,
cuando haya modificaciones, claro.

__ 4 __

Estructura de ambos libros, en cuanto a las Hojas implicadas:
(*) Insisto que intente hacer todo por codigo sin meter datos a ninguna
hoja y se ralentizaba tanto -mi codigo-
que opte por tu solucion de una hoja oculta y accesible por contrase#a,
no era mi idea pero si una solucion factible y rapida.
Libro 'Tablas'.Hoja 'B' --> Estructura:
Fila 1 Titulos: Nº / Cliente / CIF / Expdte / Gestor / ... (misma
estructura en fila)
Libro 'Dpto'.Hoja 'Especial' --> Estructura:
Columna A Titulos: Nº / Cliente / CIF / Expdte / Gestor / ...
Columna B --> Deposito los datos extraidos de la base coincidentes
'B1:B78' (misma estructura en columna) / B81 es el TextBox0 del
Formulario 'Localizador'
-lo que tecleo para buscar un cliente por nombre, ordenado en la base
alfabeticamente.

__ 5 __

No tengo forma de acceder a los atributos de los ficheros, todo esto lo
situo en un directorio compartido del servidor
que funciona como 'servidor web' con todas las limitaciones que lleva por
politica de 'seguridad industrial'...
no se permite 2 accesos simultaneos a un fichero como escritura aunque se
trate de un libro Excel compartido
-te lo juro que es real, he dejado varias neuronas en ese camino sin
conseguirlo, es una multinacional y...bueno, asi estan las cosas:
Varias personas acceden como lectura pero solo una -yo- puede actualizar.
Si en 'Tablas' existe una fila de titulos y una columna 'A' que es el nº
del cliente no es sino por no volverme loco al actualizar
si logro actualizar desde este formulario obviamente sobrarian -hasta
ahora actualizo directamente en la base.
De nuevo perdon, por extenderme en sin duda temas que no resuelven el
problema suscitado, aunque si aclaren las circunstancias de trabajo.