CONSULTAR LIBROS CERRADOS

Cualquier otro tipo de consultas sobre la Hoja de Cálculos Excel.

Reglas del Foro
1. Antes de hacer tu pregunta intenta con el buscador de este foro (muchas preguntas ya fueron respondidas antes!)
2. Si haces una nueva pregunta, es muy recomendable que adjuntes el ejemplo Excel para poder comprenderla mejor!
3. Realiza tu pregunta de forma clara, explicando bien cada paso de lo que haces y tendrás más probabilidad de respuesta!
Compartir en:
     

CONSULTAR LIBROS CERRADOS

Notapor jgregorio77 » 02 Feb 2011 15:52

Buenos dias expertos, mi requerimiento es el siguiente:
Tengo un libro de excel en donde deseo hacer consultas a otros libros con el contenido de una ficha. Dicha ficha en todos los libros tienen el mismo formato y las mismas referencias de datos por lo que en este Libro "Visualizador" intento hacer lo siguiente.
1.- A través de una pequeña macro dejo que liste en una celda los archivos contenidos en una carpeta, dicho listado se mantiene actualizado en un Formulario "ComboBox" para que este nombre vaya a una celda especifica y saber asi el nombre del archivo de la ficha a ver.
2.- Con la funcion INDIRECTO, puedo hacer las capturas de datos del archivo seleccionado a cada una de las referencias del visualizador.
Hasta aqui no hay problemas pues mientras tenga los archivos abiertos este hara la captura de datos de cualquiera de los archivos contenidos en la lista a la ficha Visalizador... El problema es que tengo que tener estos ficheros abiertos para poder hacer este trabajo....
Mi pregunta: Existe un modo mas eficaz y rapido que me perta hacer lo que deseo y sin necesidad de tener que dejar todos los archivos abiertos?... Gracias
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
jgregorio77
Miembro Nuevo
Miembro Nuevo
 
Registrado: 01 Feb 2011 18:08

Re: CONSULTAR LIBROS CERRADOS

Notapor Héctor Miguel » 03 Feb 2011 00:56

hola, j gregorio !

la funcion indirecto NO trabaja con libros cerrados, necesitarias de codigo para "fabricar" vinculos reales con datos variables, o...

busca en la pagina de Laurent Longre -> http://xcell05.free.fr/ [English page] (Excel add-ins) (e instala) el complemento MoreFunc.xll (MasFunciones)
-> http://xcell05.free.fr/morefunc/english/
-> http://xcell05.free.fr/morefunc/english/#Download
(opcionalmente, algunos ejemplos en -> http://xcell05.free.fr/downloads/Examples.zip)
podras usar la funcion: Indirect.Ext que permite interactuar con libros cerrados.
-mas otras 40+ funciones que tendrias que cargar con ellas- (todas muy utiles, pero... NO de uso frecuente)

por otra parte (y hasta donde se) desde hace mas de 20 años que la unica forma expuesta en foros y grupos para que una funcion llamada desde hoja de calculo "lea" datos en archivos cerrados de excel, es a traves de una funcion personalizada, desarrollada por Harlan Grove llamada "pull" (inspirada por BP y LL, y puedes consultar en internet al respecto), y lo que "hace" es crear una instancia separada de excel (donde se puede procesar el metodo "ExecuteExcel4Macro"), ABRIR el archivo solicitado (que se mantiene "cerrado" en la instancia donde se aplica la funcion), rescata el dato y lo devuelve como valor de la funcion, despues CIERRA el libro y la instancia separada de excel

o sea, puesto que la funcion =indirecto(... NO funciona para trabajar con libros cerrados, las funciones del MoreFunc estan basadas la tecnica de HG, y la "pega" es que en algun momento puedes ir dejando instancias NO cerradas de n_veces que abras excel (en instancias separadas, o sea, cada vez que entres, edites o recalcules las celdas donde la uses) :-(

la siguiente funcion personalizada solo esta preparada para leer UN DATO en libros cerrados de excel v. 97-2003 (*.xls), pero hace +/- lo mismo (sin el inconveniente de abrir otras instancias de excel), tomando 4 argumentos, suponiendo que en tu hoja tienes en celdas las variables: ruta, libro, hoja, celda (p.e.)

[D1] c:\ruta y\sub\carpetas
[D2] libro.xls
[D3] hoja5
[D4] h4

la funcion la llamas (como a cualquiera otra de las integradas en excel) de la siguiente forma:

=LeerDatoCerrado(d1,d2,d3,d4)

y el codigo de la funcion (copia/pega o escribe) en un modulo de codigo estandar en el editor de vba:

Código: Seleccionar todo
Function LeerDatoCerrado(ruta As String, archivo As String, hoja As String, ref As String)
  Dim Cnn As Object, Rec As Object
  Set Cnn = CreateObject("adodb.connection")
  Set Rec = CreateObject("adodb.recordset")
  Cnn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
                   ruta & IIf(Right(ruta, 1) <> "\", "\", "") & archivo & _
                   ";extended properties=""excel 8.0;hdr=no"""
  Rec.Open "select * from [" & hoja & "$" & Range(ref).Resize(2, 1).Address(0, 0) & "]", Cnn, 1, 1
  LeerDatoCerrado = Rec(0).Value
  Rec.Close: Set Rec = Nothing
  Cnn.Close: Set Cnn = Nothing
End Function

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector.
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
Héctor Miguel
Miembro Frecuente
Miembro Frecuente
 
Registrado: 26 Mar 2005 18:31

Re: CONSULTAR LIBROS CERRADOS

Notapor silvia quispe » 11 Ago 2014 14:29

Hola Jgregorio y HM ;)


Héctor, una preguntita:
el código que presentas, ¿se podría adecuar a versiones posteriores a excel 2003?

Muchas Gracias por la respuesta.
:mrgreen:
Silvia
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
silvia quispe
Miembro Frecuente
Miembro Frecuente
 
Registrado: 13 Jul 2010 16:35
Ubicación: Lima-Perú

Re: CONSULTAR LIBROS CERRADOS

Notapor silvia quispe » 11 Ago 2014 19:15

Héctor

Como leí esto:
Héctor Miguel escribió:la siguiente funcion personalizada solo esta preparada para leer UN DATO en libros cerrados de excel v. 97-2003 (*.xls),

lo hice en 2003.

ahora, el libro que hice en la mañana en versión 2003, lo he grabado en versión 2007 habilitado para macros y ha funcionado bien..
:shock: :shock:

Entonces, ya no es necesario cambiar nada..

:mrgreen:

gracias.
Silvia
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
silvia quispe
Miembro Frecuente
Miembro Frecuente
 
Registrado: 13 Jul 2010 16:35
Ubicación: Lima-Perú

Re: CONSULTAR LIBROS CERRADOS

Notapor Héctor Miguel » 11 Ago 2014 22:59

silvia quispe escribió:... lo hice en 2003...
Entonces, ya no es necesario cambiar nada...

en tanto requieras consultar (solo) archivos cerrados de versiones 97/2003 (*.xls) no habrá inconvenientes
cuando requieras consultar (incluso) archivos de versiones posteriores (2007/...) habrá que hacer ajustes ;)

saludos,
hector.
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
Héctor Miguel
Miembro Frecuente
Miembro Frecuente
 
Registrado: 26 Mar 2005 18:31

Re: CONSULTAR LIBROS CERRADOS

Notapor silvia quispe » 12 Ago 2014 12:51

Héctor Miguel escribió:cuando requieras consultar (incluso) archivos de versiones posteriores (2007/...) habrá que hacer ajustes

:shock: PLOP! y rePLOP!

Gracias por la respuesta Héctor
Y lo puedes ajustar?
por ahí cuando tengas un tiempito.. ¿?


Silvia
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
silvia quispe
Miembro Frecuente
Miembro Frecuente
 
Registrado: 13 Jul 2010 16:35
Ubicación: Lima-Perú

Re: CONSULTAR LIBROS CERRADOS

Notapor Héctor Miguel » 12 Ago 2014 15:05

silvia quispe escribió:... cuando tengas un tiempito.. ¿?

ya está, mira en [url=http://answers.microsoft.com/es-es/office/forum/office_2010-excel/la-función-leerdatocerrado-no-me-lee/a3262953-1f9d-40a3-a93a-9e14b153ac0f]esta otra consulta[/url]

(el segundo marcado como respuesta) ;)

saludos,
hector.
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
Héctor Miguel
Miembro Frecuente
Miembro Frecuente
 
Registrado: 26 Mar 2005 18:31

Re: CONSULTAR LIBROS CERRADOS

Notapor silvia quispe » 12 Ago 2014 17:04

gracias, gracias, nuevamente.. :wink:

ya me duele e coco..
-> :|
:mrgreen:

Entré aqui:
http://answers.microsoft.com/es-es/office/forum/office_2010-excel/la-funci%C3%B3n-leerdatocerrado-no-me-lee/a3262953-1f9d-40a3-a93a-9e14b153ac0f

con xlsx sale bien. :D :D
con xlsm me da, error valor... :cry: :cry:

Tal vez sea esto que comentas:
1) (probablemente) solo hacia falta instalar los MDAC, que son los que permiten la creacion de los objetos "connection" y "recordset", ya que los controladores (oledb) solo requieren que este instalado office (segun version)


mmm
1 no tengo idea de si me faltan o no, los MDAC ¿? , como reviso eso ¿?
2 probé 2 libros xlsm.


Silvia
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
silvia quispe
Miembro Frecuente
Miembro Frecuente
 
Registrado: 13 Jul 2010 16:35
Ubicación: Lima-Perú

Re: CONSULTAR LIBROS CERRADOS

Notapor Héctor Miguel » 12 Ago 2014 23:12

silvia quispe escribió:1 no tengo idea de si me faltan o no, los MDAC ¿? , como reviso eso ¿?
2 probé 2 libros xlsm.

1) (MDAC = Microsoft Data Access Component) no creo que te hagan falta las librerías de conexiones y consulta a bases de datos puesto que ya pudiste "escudriñar" en libros cerrados (.xls y .xlsx), en todo caso (quizá) convendría estar seguros de contar con la versión ultima a la mano ?...
- Descargar los MDAC v 2.8 SP1
- Descargar los Controles de conectividad Office 2007
- Descargar el Motor redistribuible Access 2010
- Blog para Consultas de Excel en plataformas x64 (en ingles)

2) en cuanto a la imposibilidad de consultar libros con macros (.xlsm)... cabría la posibilidad de que no sea con cualquier archivo con macros, sino con algunos en especifico ?
(errores, excesos, corrupciones, tablas dinámicas, ... ???)

saludos,
hector.
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
Héctor Miguel
Miembro Frecuente
Miembro Frecuente
 
Registrado: 26 Mar 2005 18:31

Re: CONSULTAR LIBROS CERRADOS

Notapor Héctor Miguel » 12 Ago 2014 23:46

silvia quispe escribió:1 no tengo idea de si me faltan o no, los MDAC ¿? , como reviso eso ¿?

(perdón) complementando la respuesta a esta pregunta...

hay dos formas de usar objetos desde vba a través de hacer referencia a sus librerías:

1) "Late Binding" usando la función CreateObject("objeto de automatización")
en el código hago uso del "Late Binding" asumiendo que la librería estará instalada en el equipo donde se usa
Dim Cnn As Object, Rec As Object
Set Cnn = CreateObject("adodb.connection")
Set Rec = CreateObject("adodb.recordset")

2) "Early Binding" estableciendo referencia a la librería en el proyecto de macros
para esta opción, puedes comprobar si tienes instalada (y registrada) la librería de las MDAC desde el editor de VBA:
- herramientas > referencias > (busca por orden alfabético) > Microsoft ActiveX Data Objects x.x
donde x.x corresponden a la versión instalada (mínimo la 2.1)

y la declaración de variables sería del tipo:
Dim conexion As New ADODB.Connection, registros As New ADODB.Recordset

saludos,
hector.
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
Héctor Miguel
Miembro Frecuente
Miembro Frecuente
 
Registrado: 26 Mar 2005 18:31

Re: CONSULTAR LIBROS CERRADOS

Notapor silvia quispe » 13 Ago 2014 11:22

♥Héctor♥:
:mrgreen: "hoy pude abrir los archivos..." :oops:


Héctor Miguel escribió:2) "Early Binding" estableciendo referencia a la librería en el proyecto de macros
para esta opción, puedes comprobar si tienes instalada (y registrada) la librería de las MDAC desde el editor de VBA:
- herramientas > referencias > (busca por orden alfabético) > Microsoft ActiveX Data Objects x.x
donde x.x corresponden a la versión instalada (mínimo la 2.1)

tengo esto:
Microsoft ActiveX Data Objects x.x Library
( 2, 2.1, 2.5, 2.6, 2.7, 2.8)
ninguna está marcada (seleccionada)..... :?:

También instalé el ......checker..., me dió esto:
MDAC 2.8 SP1 windows XP SP3

y después de todo eso... probé nuevamente...
Héctor Miguel escribió:) en cuanto a la imposibilidad de consultar libros con macros (.xlsm)... cabría la posibilidad de que no sea con cualquier archivo con macros, sino con algunos en especifico ?

y aquí esta el detalle: :O O_O

Los 2 que ayer probé, estaban así:
'C:\Documents and Settings\usuario\Mis documentos\Downloads\
si hubiera adjuntado el libro, te hubieras dado cuenta.. :oops:
Ayer cuando probé, que casualidad, el que si abrió, le había quitado la comilla ' , y a los xlsm no...

Como dice el ingeniero de Sistemas: es problema de usuario, no del Sistema...... :oops:

muchas gracias...

Silvia
* Te recomendamos estos productos Excel: Manual de Macros | Manual de Funciones | Nuevas Funciones | ddTraDa
Avatar de Usuario
silvia quispe
Miembro Frecuente
Miembro Frecuente
 
Registrado: 13 Jul 2010 16:35
Ubicación: Lima-Perú


Compartir en:
     

  • Anuncio
Manual Excel avanzado

Volver a Otras Consultas

¿Quién está conectado?

Usuarios navegando por este Foro: Google [Bot] y 2 invitados