Querys en excel

22 envíos / 0 nuevos
Último envío
jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Querys en excel
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!
pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
por supuesto que sí

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:

parámetros definidos en una consulta de datos externa con MS Query desde Excel

al actualizar la consulta, Query nos pregunta por estas cosas que hemos puesto y que no sabe lo que son:

parámetros en una consulta query 

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)

configurar parámetros en consulta Excel a datos externos mediante query

En las opciones de cada parámetro podemos fijar su valor, o decirle a Excel que lo tome de una celda.
configurar parámetros en consulta Excel a datos externos mediante query

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Re: por supuesto que 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

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: por supuesto que sí

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

 

Data Tools Suite
datos y tablas con Excel

jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Re: por supuesto que 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

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
¿OLE DB?

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

 

Data Tools Suite
datos y tablas con Excel

jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Re: ¿OLE DB?
Excelente paco ya logre hacer que me sirviera, muchisimas gracias!

Joseph Torres

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: ¿OLE DB?

me alegro que te funcionase

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

chronot
Imagen de chronot
Offline
última acción: Hace 1 mes 1 hora
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 30/01/2011 - 22:33
Puntos: 2727
Re: Querys en excel
Este comentario ha sido movido aquí.
jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Re: ¿OLE DB?
Buenas tardes Paco! Sigo yo molestando, lo que me ayudaste me ha servido de maravilla, pero me esta dando problema con las fechas, no he encontrado la forma de que me funcione el parametro de fechas desde una celda, creeme he intentado de todo, ponerlo entre ##, darle un convert, ponerlo entre corchetes como lo pone el condigo {ts ....} y nada, no hay forma. Simplemente me descarta ese "and" y me da todos los datos para todas las fechas (no filtra) me podrias decir porfavor como hacerlo? xq ya estoy a punto de volverme loco en tratar de ver como lo hago :-P, gracias de antemano por tu ayuda!

Joseph Torres

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
prueba a convertirlo en un texto

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

 

Data Tools Suite
datos y tablas con Excel

jotor24
Imagen de jotor24
Offline
última acción: Hace 2 años 4 meses
Nivel 1 - 200 puntos
alta: 20/01/2011 - 20:23
Puntos: 341
Re: prueba a convertirlo en un texto
Gracias paco, esto tambien ya lo habia probado y nada :-S

Joseph Torres

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: ¿OLE DB?

pues no sé... así sin verlo no se me ocurre. ¿has probado en modo americano mm/dd/aaaa?

 


 

Data Tools Suite
datos y tablas con Excel

visitante (no verificado)
Imagen de visitante
Re: Querys en excel

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

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: Querys en excel

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

definir valor de parámetro en una consulta de datos externos

si no activas esto, te volverá a pedir el valor del parámetro cada vez que actualices.

 

Data Tools Suite
datos y tablas con Excel

visitante (no verificado)
Imagen de visitante
Re: Querys en excel

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!!!

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: Querys en excel

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.

 

Data Tools Suite
datos y tablas con Excel

visitante (no verificado)
Imagen de visitante
Re: Querys en excel

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

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 15 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10460
Re: Querys en excel

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.

 

Data Tools Suite
datos y tablas con Excel

visitante (no verificado)
Imagen de visitante
Re: Querys en excel

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

Fads84 (no verificado)
Imagen de visitante
Filtrar informacion de query desde una hoja de excel

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

AdjuntoTamaño
Image icon consulta_cod_filtro.png225.08 KB
DALILA (no verificado)
Imagen de visitante
LO MISMO PERO EN HOJAS DE GOOGLE

[comentario movido a tema nuevo en el foro http://www.necesitomas.com/lo-mismo-pero-hojas-google]