Buen dia!
Resulta que he aprendido a hacer conexiones a bases de datos SQL server desde excel mediante Querys y me funciona de maravilla, pues mis informes se actualizan de forma automatica desde el servidor de la compañia.
Por ejemplo lo utilizo para averiguar los pagos de cierto cliente en cierto mes.
Mi problema se da cuando cambio de mes o de cliente pues tendria que ir a modificar el query desde la conexion, y no lo puedo dejar abierto a todos los clientes y todos los meses porque la bd es gigante y me pega el excel.
Entonces ¿hay alguna forma de que la conexion de datos lea una celda de una hoja de excel donde yo le pueda poner los parametros necesarios para hacer la consulta en la bd.
Yo se que dentro del mismo query puedo usar un varchar, pero no se como hacer que lea los datos desde una hoja en el excel.
Gracias de antemano por la ayuda!
Jue, 07/04/2011 - 17:11
#1
Querys en excel
Necesitas definir unos parámetros en tu consulta. Luego Excel puede tomar el valor del parámetro de una celda, e incluso puede actualizar la consulta cada vez que cambie la celda.
te explico cómo hacerlo.
en Query, cuando escribas una condición para un campo, emplea un nombre entre corchetes de algo que no sea ningún nombre de campo, al realizar la consulta, como no sabe qué es ese nombre, te preguntará qué valor quieres que tome, es decir, lo interpreta como un parámetro.
por ejemplo:
al actualizar la consulta, Query nos pregunta por estas cosas que hemos puesto y que no sabe lo que son:
cuando volvemos con la consulta a Excel, en las propiedades tenemos unos parámetros
(también podremos configurarlo desde el menú contextual sobre los resultados de la consulta)
En las opciones de cada parámetro podemos fijar su valor, o decirle a Excel que lo tome de una celda.
------
Ya sé Excel, pero necesito más ahora en pdf
------
Ya sé Excel, pero necesito más.
Gracias paco, pero no me funciona, en efecto, no lo reconoce pero la unica opción que me da es de continuar o no, y nunk me deja modificar los parámetros, es mas no se me habilita la opción. Que estoy haciendo mal?
Joseph Torres
la imagen es tan pequeña que no veo nada, pero me imagino algo.
lo de emplear un nombre desconocido en la consulta funciona en MSQuery, pero por lo que veo (o casi veo) parece que estás escribiendo la consulta en el cuadro de diálogo de definición de la conexión en Excel.
en la sentencia SQL emplea un signo de interrogación en tu criterio, por ejemplo:
... Where [fecha] >= ? ... (suponiendo que tengas un campo que se llame fecha)
A ver si te pregunta por el parámetro.
------
Ya sé Excel, pero necesito más ahora en pdf
------
Ya sé Excel, pero necesito más.
Joseph Torres Gracias paco y perdon por ser tan necio pero todavia no me sirve, te vuelvo a poner las imágenes independientemente es que el blog me las redujo de tamaño,
nuevamente gracias por tu ayuda!
Joseph Torres
en las propiedades de la conexión veo que la consulta es de tipo OLEDB (esto deshabilita los botones Editar consulta y Parámetros).
prueba a utilizar MSQuery para crear la consulta, con Query seguro que puedes emplear parámetros.
------
Ya sé Excel, pero necesito más ahora en pdf
------
Ya sé Excel, pero necesito más.
Joseph Torres
me alegro que te funcionase
------
Ya sé Excel, pero necesito más ahora en pdf
------
Ya sé Excel, pero necesito más.
Joseph Torres
puede que dependa de cómo interpreta las fechas la base de dato (que no coincidirá con cómo lo interpreta Excel).
en alguna ocasión lo he resuelto convirtiendo en Excel la fecha en un texto con la función TEXTO con formato dd/mm/aaaa
luego lo que empleaba como parámetro era ese texto en vez de la fecha directamente.
------
Ya sé Excel, pero necesito más ahora en pdf
------
Ya sé Excel, pero necesito más.
Joseph Torres
pues no sé... así sin verlo no se me ocurre. ¿has probado en modo americano mm/dd/aaaa?
------
Ya sé Excel, pero necesito más.
Hola Paco!
Estoy mirando la infromación que pusiste sobre los parametros para la cosulta de la query y me ha funcionado de maravilla.
La verdad el inconveniente que tengo es que cuando cierro el archivo (y guardo, por supuesto) una vez lo vuelvo a abrir, la referencia a la celda donde quiero poner el filtro desaparece, estuve consultando con algunos amigos y les pasa lo mismo.
Alguna idea de que podría ser??
Saludos!
Mateo
Así sin más pistas...
¿los parámetros y las celda de donde toman los valores están definidos en las propiedades de la consulta?
Otra posibilidad es que hayas definido que tome el valor de una celda miesntras actualizas.
En este caso, asegúrate de marcar la opción usar este valor o referencia para futuras actualizaciones
si no activas esto, te volverá a pedir el valor del parámetro cada vez que actualices.
------
Ya sé Excel, pero necesito más.
Hola, una pregunta si me puedes ayudar, como hacer para que la opción Usar este valor o referencia para futuras actualizaciones aparezca deshabilitado y el usuario se vea obligado siempre a digitar? Muchas Gracias!!!
Esa es una opción que ofrece Excel, no se puede deshabilitar.
Yo creo que es más práctico tomar el valor de una celda y marcar la casilla de Actualizar cuando cambie el valor de la celda.
Así no se muestra ese formulario, sino que simplemente al escribir en la celda la consulta se actualizará.
Esto tiene también la ventaja de que el valor del parámetro se puede ver, no está oculto.
------
Ya sé Excel, pero necesito más.
Sabria alguien decirme por que esto no se puede parametrizar cuando en vez de devolver los datos a excel se crea una tabla dinamica......
O lo que es lo mismo, cuando parametrizamos no nos deja crear una tabla deinamica al devolver los datos a excel....
Muchas gracias
Creo que te toca hacerlo en dos pasos:
1-insertar los datos en una tabla en una hoja,
2-y luego crear la tabla dinámica a partir de esa tabla.
Excel no permite que las tablas dinámicas lean de una consulta con parámetros directamente. imagino que es porque tendría que hacer dos actualizaciones, primero con el parámetro y luego la tabla.
------
Ya sé Excel, pero necesito más.
Muchas gracias Paco por la respuesta y la rapidez
Pues es una faena porque creo la tabla dinamica directamente ya que la hoja de excel no tiene capacidad en filas para my consulta.
Muchas gracias nuevamente y un saludo
Hola Paco Megia estuve leyendo las respuestas que dabas a los demas integrantes de este foro, yo tengo un problema similar, tengo un excel que se esta conectando a una Base de datos AS400 que me muestra un reporte de ventas y quiero filtrar ese query (Datos/actualizar Datos/Propiedades de conexion/Texto del comando)con una lista de codigos que estaria en otra pestaña del excel, el botón de parametros lo tengo desabilitado, ¿Como podria hacer para realizar ese filtro? ¿En el query tendria que apuntar a la lista de codigos de la pestaña? agradecere mucho tu apoyo. Gracias
[comentario movido a tema nuevo en el foro http://www.necesitomas.com/lo-mismo-pero-hojas-google]
Cordial saludo tengo un inconveniente parecido, al ingresar la fecha me genere error el dia del mes debe estar entre 1 y el ultimo dia del mes
Atento saludo y un abrazo desde Colombia,
Les cuento que tengo un problema similar pero con algo puntual.
Al momento de parametrizar la consulta por fecha debo ingresar en la celda que contiene el parametro la fecha en formato "DDMMAAAA" (sin comillas), hasta allí no tengo problemas. El problema es que necesito cargar los datos de una fecha pero a partir de una HORA especifica y es alli donde mi funcion de la honda colapsa.
Ejemplo:
Codigo de consulta: ((MOVIMIENTOS.MVM_FEHOIN>={ts '2020-01-15 14:00:00'}))
al ponerlo como parametro para una celda queda:
((MOVIMIENTOS.MVM_FEHOIN>=?))
Al momento de ingresar este parametro en una celda NO FUNCIONA. He intentado cambiando formatos de la celda del parámetro, he intentado con la funcion TEXTO, he intentado omitiendo el espacio que separa la fecha de la hra, elimiando los ":" del formato y hasta ahora nada.
Llevo varios días lidiando con esto, tratando por diferentes formas pero en todas las circunstancias me arroja el error:
[Oracle][ODBC][Ora]ORA-01861: el literal no coincide con la cadena de formato.
¿Que estoy haciendo mal?
Gracias por millones si alguien me puede dar respuesta
Abrazos
Primero para que habilite los parámetros, en la consulta creo que debes llamar al parámetro con el signo ?, en un WHERE CAMPO IN(?) luego ya puedes ir al botón parámetro y definirlo en "Tomar el valor de la siguiente celda:"
Mi problema es que ese parámetro no me recibe una lista de códigos como ['A320', 'A330', 'A340', 'A350'] he dejado el parámetro en una celda, con y sin comillas y no lo toma, me dice que es un string y que será truncado por la derecha.
Agradecería mucho el apoyo si alguien sabe en qué formato se debe escribir la lista para que la tome como parámetro.
Lo solucionaste?
Sabras como pasar un lista, como parametros a la consulta, lo he intentado, pero solo funciona con 1 solo dato