Querys en excel

25 envíos / 0 nuevos
Último envío
jotor24
Imagen de jotor24
Offline
última acción: Hace 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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

 

------
Ya sé Excel, pero necesito más.

jotor24
Imagen de jotor24
Offline
última acción: Hace 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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

 

------
Ya sé Excel, pero necesito más.

jotor24
Imagen de jotor24
Offline
última acción: Hace 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
¿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

 

------
Ya sé Excel, pero necesito más.

jotor24
Imagen de jotor24
Offline
última acción: Hace 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
Re: ¿OLE DB?

me alegro que te funcionase

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

 

------
Ya sé Excel, pero necesito más.

chronot
Imagen de chronot
Offline
última acción: Hace 9 meses 3 semanas
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 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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

 

------
Ya sé Excel, pero necesito más.

jotor24
Imagen de jotor24
Offline
última acción: Hace 3 años 1 mes
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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
Re: ¿OLE DB?

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.

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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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.

 

------
Ya sé Excel, pero necesito más.

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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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.

 

------
Ya sé Excel, pero necesito más.

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 7 horas 4 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10640
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.

 

------
Ya sé Excel, pero necesito más.

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]

 

velez (no verificado)
Imagen de visitante
inconveniente

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

AdjuntoTamaño
File error_fecha.docx96.39 KB
Jair B (no verificado)
Imagen de visitante
Filtro Fecha y Hora

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

AdjuntoTamaño
Image icon captura00001.png5.22 KB
cristian.pasten (no verificado)
Imagen de visitante
Pasar lista de códigos como parámetro a la consulta

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.