Pasar parámetros a una consulta en MS Query desde celdas de Excel

sábado, marzo 03, 2012

Esta nota viene a colación de la consulta de un lector que me preguntaba si era posible pasar parámetros a una consulta en MS Query desde una celda de Excel.

La respuesta es afirmativa y en esta entrada mostraré cómo hacerlo.

Empecemos por recordar que una de las herramientas para extraer datos de fuentes externas en Excel es el MS Query



Primer paso: crear la consulta en el MS Query

Antes de empezar el proceso hemos definido que las celdas B1:B3 contendrán los parámetros.



Para nuestro ejemplo usaremos la base de datos Northwind (el archivo no viene con el paquete de Office 2010).



Elegimos la tabla de la base de datos (en nuestro caso: Invoices) y los campos que queremos importar a la hoja de Excel



Apretamos “next” hasta que llegamos a la última etapa (Finalizar) y allí elegimos la opción “ver datos en MS Query”



Segundo paso: agregar parámetros a la consulta

Primero debemos hacer visible el área de criterios



Para ingresar criterios en forma de parámetros debemos usamos los paréntesis “[“ y “]” de esta manera


En la línea de Criterios ingresamos el campo, en nuestro ejemplo usamos Country (país) y Shipped Date (fecha de despacho). En la línea de Valor ponemos

[ingrese Pais] para hacer la consulta según país

Between ]fecha de inicio] and [fecha de cierre] para definir el rango de fechas.

Tercer paso: transferir los resultados a Excel




Al hacerlo se abre un diálogo para ingresar los valores de los parámetros; a esta altura del proceso no es necesario ingresar ningún valor, sencillamente apretamos Aceptar




En la hoja de Excel elegimos la ubicación de la tabla que será importada




Cuarto paso: definir las celdas de los parámetros en Excel

Antes de finalizar el proceso apretamos el botón Propiedades




Luego activamos la pestaña Definición para acceder al botón Parámetros




En el formulario que se abre definimos la opción “Tomar el valor de la siguiente celda” y también marcamos la opción “Actualizar automáticamente…”




Volvemos a este paso para cada uno de los parámetros y apretamos Aceptar para los siguientes tres pasos
Excel importa la tabla filtrada por los criterios que hemos definido en B1:B3




Como hemos elegido la opción de actualizar automáticamente al cambiar los valores en la celdas del rango B1:B3, tenemos una consulta que actúa dinámicamente






60 comments:

Anónimo,  05 marzo, 2012 10:33  

Hola Jorge L.Dunkelman quisiera saber de que página puedo descargar northwind.mdb
Gracias.

Anónimo,  05 marzo, 2012 15:00  

Hola Jorge:

¿Se puede elegir, en su caso, por ejemplo 2 paises?

Gracias anticipadas.

Saludos a tod@s.

Jorge L. Dunkelman 05 marzo, 2012 18:41  

En esta página hay un enlace para descargar la nueva versión de la base de datos.
En los medios de instalación de Office hasta la versión 2007, existe el archivo y de ahí se puede instalar.

Jorge L. Dunkelman 05 marzo, 2012 18:43  

Si, todo depende de cómo está organizada la consulta. Todo parámetro definido en la consulta puede referirse a una celda de la hoja.

Claudio,  05 marzo, 2012 19:03  

Excelente

Carlos De León 08 marzo, 2012 19:03  

Se puede utilizar este metodo cuando tenemos una consulta, con subconsultas, las cuales usan los mismos parametros de la consulta principal.

Anexo Ejemplo:
SELECT T10.Cliente, t11.cardname as Cliente, t11.cardfname as planta,
t10.IA as 'Inventario Actual', T10.C as 'Consumo Periodo',
round(t10.C/DATEDIFF(DAY,[%1],[%2]),2) as 'Consumo Diario',
Case When round(t10.C/DATEDIFF(DAY,[%1],[%2]),2)=0
Then 0
Else T10.IA/round(t10.C/DATEDIFF(DAY,[%1],[%2]),2)
end as 'Dias Inventario'
FROM (SELECT DISTINCT SUBSTRING(T0.[ItemCode],4,3) AS cLIENTE,
(SELECT SUM(R0.InQty)
FROM OINM R0
WHERE R0.DocDate>='2009-12-31' AND R0.DocDate <= [%2] AND
R0.[Warehouse]=[%0] AND
substring(T0.[ItemCode],4,3)=substring(R0.[ItemCode],4,3)
GROUP BY substring(R0.[ItemCode],4,3))-
(SELECT SUM(R0.OutQty)
FROM OINM R0
WHERE R0.DocDate>='2009-12-31' AND R0.DocDate <= [%2] AND
R0.[Warehouse]=[%0] AND
substring(T0.[ItemCode],4,3)=substring(R0.[ItemCode],4,3)
GROUP BY substring(R0.[ItemCode],4,3)) as 'IA',
isnull((SELECT SUM(R0.OutQty)
FROM OINM R0
WHERE R0.DocDate>=[%1] AND R0.DocDate <= [%2] AND
R0.[Warehouse]=[%0] AND
substring(T0.[ItemCode],4,3)=substring(R0.[ItemCode],4,3)
GROUP BY substring(R0.[ItemCode],4,3)),0) as 'C'
FROM OINM T0
WHERE T0.Warehouse = [%0] AND T0.[DocDate]<=[%2]) t10
inner join OCRD t11 on t10.cliente=substring(t11.cardcode,4,3) and
case substring([%0],1,2) when 'TI' then 'TJ'
WHEN 'QR' then 'GD'
WHEN 'CU' then 'GD'
when 'ME' then 'GD'
when 'GD' then 'GD'
when 'MT' then 'MT'
end +'C'=substring(t11.cardcode,1,3)
order by 2,3

Donde [%0], [%1] y [%2] son mis parametros pero como Microsoft Query no puede mostrar este tipo de consultas complica el aplicar esta solucion planteada..

Jorge L. Dunkelman 08 marzo, 2012 21:52  

Carlos, gracias por compartir, pero quiero hacer una observación. El ejemplo, al no tener el contecto del modelo donde se lo está aplicando, no queda del todo claro. Sería más eficiente describir el método en términos generales para poder orientar a los lectores.

Fer Cipriani 13 marzo, 2012 18:46  

Como usuario de Excel cada vez lo utilizo mas como "centro operativo" para vincular consultas SQL, trabajar datos y generar documentos y/o correos; creo que es la tendencia y no es tanta la informaciòn específica que circula.

Jorge, felicitaciones por la nota.

Jorge L. Dunkelman 13 marzo, 2012 19:21  

Coincido contigo aunque no estoy seguro que se trate de una tendencia general.
Estoy considerando publicar una serie de notas sobre el tema Access-Excel. Es decir, como potenciar el análisis de datos combinando Access y Excel. ¿Qué opinan?

Fabio Ramirez,  17 marzo, 2012 05:05  

Excelente idea Jorge. Adelante con las notas Access-Excel

Juan Ignacio,  23 marzo, 2012 19:45  

Jorge soy lector silencioso desde hace mucho y creo que tu blog es más que fantástico! sería sumamente útil una serie de notas vinculando el excel con el access.

Coincido con Fer acerca que es ideal pero no se si es una tendencia general.

Fer Cipriani 23 marzo, 2012 22:37  

Jorge, una pequeña consulta: Cómo es el uso de comodines en una consulta de este tipo?
Me explico mejor con tu ejemplo: no quiero (o no puedo) tipear un campo en la búsqueda del M Query; quisiera que me tome US ó SA cuando busque el USA (Campo Pais).
Me da error, incluso intentando con US* ó US?.
Se puede?
Abrazo y gracias de antemano.

Jorge L. Dunkelman 25 marzo, 2012 20:37  

Se puede, pero no con la técnica que muestro en esta nota. Veré de publicar algo al respecto.

Ricardo Gonzalez 21 mayo, 2012 21:24  

Hola Jorge otro lector silencioso que aprendio muchisimo en este blog. Te cuento que esto que estas mostrando acá lo vengo usando hace dos años mas o menos, dado que la cantidad de registros con los que trabajo exceden los 50k. Levanto archivos txt o csv con access los limpio, ordeno, filtro, y diseño consultas, luego desde el excel solo debo clickear actualizar y me trae la atos perfecto en segundos. Es lo mejor para muchos registros.

Un gran abrazo y gracias por el aporte

Santandereano1970 08 julio, 2012 01:10  

Muchas gracias por sus aportes don Jorge..

Nelson Carrera 24 agosto, 2012 17:05  

Hola Jorge,
Se puede hacer los mismo que los parámetros pero con la base de datos a la que se consulta? Tengo muchas BD y quisiera que atravez de una modificacion en una celda en excel cambiara la consulta.
Desde ya gracias.

Jorge L. Dunkelman 24 agosto, 2012 18:12  

Nelson, no estoy seguro de haber entendido. ¿Te refieres a pasar los parámetros a una consulta en una base de datos como por ejemplo Access? Si es así se puede extraer los datos con MsQuery y tratar de aplicar la técnica que muestro en la nota. No estoy seguro si funciona ya que nunca lo he intentado. Si se puede hacer usando ADO.

Nelson Carrera 24 agosto, 2012 18:33  

Jorge, Tratare de explicar mejor.
Primero indicar que lo presentado en este blog funciona perfectamente. Adjunto la consulta SQL que estoy utilizando y que tiene incorporada la logica de los parametros:

SELECT GLTran.Acct, GLTran.BatNbr, GLTran.CpnyID, GLTran.CrAmt, GLTran.Crtd_User, GLTran.DrAmt, GLTran.ExtRefNbr, GLTran.FiscYr, GLTran.LedgerID, GLTran.PerPost
FROM B001APP.dbo.gltran GLTran
WHERE (GLTran.FiscYr=?) AND (GLTran.PerPost>=? And GLTran.PerPost<=?) AND (GLTran.LedgerID=?)

Los signos de interrogacion son los parametros que uno puede modificar en las celdas en excel vinculadas y esto como indique anteriormente funciona perfectamente.

Mi problema es que quiero agregar como un paramentro mas el origen de la información. Entonces suponia que dejando la BD igual que los otros parametros (con signo "?") funcionaria
ejemplo:
Original
FROM B001APP.dbo.gltran GLTran
Supuesto
FROM ? GLTran

pero no funciono

Espero que se haya entendido

Jorge L. Dunkelman 26 agosto, 2012 15:04  

Te sugiero que sigas la técnica de la nota, es decir, crear la consulta en MsQUery sin los parámetros; luego, en el MsQuery, agregar los parámetros (segundo paso) y finalmente en la hoja de Excel definir las celdas asociadas.

Mrndrsn 09 octubre, 2012 02:53  

Jorge.

te he entendido muy bien el ejemplo, pero me ha surgido una duda que no me deja dormir...

a partir de la tabla:

id_persona
546
878
257

como puedo obtener:

id_persona | nombre | paterno
586. | Juan. | Álvarez
878. | Carlos. | vaQuez
257. | Nicolás. | franco

ya que, en las opciones de los parámetros, tan sólo permite seleccionar una celda, y no un rango de celdas.

es decir, ejecutar la consulta como si fuera una función que podemos arrastrar y ejecutar según el valor de la celda A#

Jorge L. Dunkelman 15 octubre, 2012 19:27  

Agregando los campos correspondientes (nombre, apeliido) en la consulta. Estos no son parámetros, el parámetro de la consulta es el campo ID.

Carlos Camacho 16 noviembre, 2012 21:57  

Tengo esta consulta
SELECT l.Descripcion, Fecha_Hora, MODO_PAGO, MODO_PASO, N_TARJETA, CAT_TABULADA, CAT_DETECTADA1
FROM ttransitos tr, lugares l
WHERE l.id_lugar = tr.id_lugar
and CAT_TABULADA <> cat_detectada1 AND IMPORTE <> 200 AND MODO_PASO <> 4 AND MODO_PASO <> 5 AND
FECHA_HORA BETWEEN '20121113 00:00:00.000' AND '20121113 23:59:59.999'
ORDER BY Descripcion, fecha_hora desc

Pero quiero sustituir las fechas por unas celdas de excel, alguien me puede ayudar.

Gracias

Erwin Albert 20 noviembre, 2012 16:14  

Excelente aporte Jorge!!

Estoy haciendo una consulta con el ejemplo de arriba en Excel 2003, me trae los datos a una hoja y me permite seleccionar una celda para introducir el valor del parámetro, pero al finalizar me dice que el valor del parámetro no es el esperado; ¿será un tema de formatos?(Estoy poniendo una fecha)

Muchísimas gracias nuevamente!!

Jorge L. Dunkelman 21 noviembre, 2012 17:56  

Si, aparentemente es un problema de formato. Fijate como aparecen las fechas en el ejemplo que publico.

Jorge L. Dunkelman 21 noviembre, 2012 18:22  

Carlos, es lo que muestro en la nota (o no entendí la consulta).

y@nus 27 noviembre, 2012 16:03  

COMO SE HACE PARA EXPORTAR mas de 10.000 registros en una consulta a excel?

Jorge L. Dunkelman 27 noviembre, 2012 17:48  

Excel Clásico (97-2003) cuenta con 65536 filas por hoja; el nuevo Excel (2007/2010) con más de un millon! ¿Cuál sería el problema de importar 10000 registros?

Anónimo,  09 diciembre, 2012 19:15  

Hola Jorge:

¿Se puede elegir, en su caso, por ejemplo 2 paises?

En la consulta sql he sustituido el = por un IN pero como tengo que informar en la celda los distintos países.
Un Saludo

Jorge L. Dunkelman 09 diciembre, 2012 21:00  

Te sugiero intentar lo siguiente (no lo he intentado): en la celda B1 ponemos el primer país; en la celda C1 el segundo país; en la celda D1 armamos la cadena del comando:
=CONCATENAR("IN(";B1;",";C1;")")
Luego usamos la celda D1 para pasar el argumento.
Otra posibilidad es usar la técnica de consolidación que muestro en esta nota.

Anónimo,  25 abril, 2013 18:06  

Hola estimado yo soy un primeriso en los temas de Query, te comento que estoy relaizando con tu procedimiento con una query que esta recuperando los datos desde una bd oracle el tema es que funciona le origen de datos, pero cuando llego al apartado de agregar los parametros este se encuentra deshabilitado, por favor me puedes acalarar que puedo hacer para que se active..

Jorge Dunkelman 25 abril, 2013 21:24  

Supongo que tiene que ver con las definiciones de la BD Oracle. Consulta con tu DBA.

Anónimo,  30 abril, 2013 12:17  

Me ha sido de gran ayuda pero tengo un par de problemas que no se resolver.

Yo trabajo con hojas dinámicas en lugar de listas. Configuro los parámetros pero la hoja dinámica no cambia al modificarlos, a pesar de pulsar Actualizar cuando cambie el valor de las celdas.

Además, cuando guardo la hoja y la vuelvo a abrir, me vuelve a pedir las celdas de los parámetros, a pesar de hacerlo "para futuras actualizaciones"

Que me falta??

Jorge Dunkelman 03 mayo, 2013 16:39  

Por hojas dinámicas, ¿te refieres a tablas dinámicas (pivot tables)? Te sugiero que me envíes el cuaderno para que me pueda hacer una idea más clara del problema (la dirección aparece en el enlace Ayuda en la parte superior del blog).

Robbie Bozzacchi 12 junio, 2013 15:58  

Hola Jorge,
Excelente tu nota.
Yo había logrado traer una consulta con un parámetro asociado el mismo a una celda.

Ahora, si en una columna por ejemplo en A1 tengo una serie de datos, en mi caso son Expedientes, y quiero que la consulta vaya expediente por expediente trayendo el resto de los datos. Cómo se puede hacer?
Se entiende? Quiero que la consulta recorra la columna hasta que se encuentre con una celda vacía.
Se puede hacer esto?

Jorge Dunkelman 04 septiembre, 2013 07:55  

Robbie,tu comentario se me pasó por alto y recién ahora lo publico. Mis disculpas.
Bien, eso es lo que hace la consulta, trae todos los valores de las tabla que cumplen con la condición.

Adolfo Fernández 15 octubre, 2013 12:20  

Excelente post. Fácil y directo. Muchas gracias por este magnifico blog

Facundo 19 noviembre, 2013 17:50  

Genial !!! Lo que estaba buscando...

Anónimo,  26 noviembre, 2013 15:35  

Hola Jorge, es genial el post, me ha servido de mucha ayuda.
Una consulta, en tu ejemplo haces una consulta del país y fechas. como se haría para una consulta de país o de fechas.
porque intento hacerlas diferentes pero me dice que no puedo hacer el resultado en las mismas líneas que la otra consulta.....
espero haberme explicado

Muchas gracias

Jorge Dunkelman 26 noviembre, 2013 16:36  

Cuando construyes la consulta en la pantalla del MS Query, criterios puestos en la misma fila en el área de criterios significan que ambas condiciones deben cumplirse (AND); si queremos los registros que cumplen una u otra condición (OR) debemos usar filas separadas en el área de los criterios.

Anónimo,  27 noviembre, 2013 09:36  

Muchas gracias Jorge

Anónimo,  29 noviembre, 2013 01:37  

Jorge:
Porque pierde la celda que defines cuando vuelves abrir el archivo de excel en una tabla dinámica (tomar el valor de la siguiente celda)

Jorge Dunkelman 30 noviembre, 2013 19:02  

¿Podrías definir un poco más lo consulta o enviarme una decripción por mail privado?

Anónimo,  16 enero, 2014 13:52  

Hola Jorge, excelente tutorial
una pregunta, se hacer que sólo devuelva los datos sin que muestre el nombre del campo??
muchas gracias

Jorge Dunkelman 16 enero, 2014 18:03  

¿Te refieres a que no aparezcan los encabezamientos de las columnas en la hoja de Excel?

Anónimo,  17 enero, 2014 13:36  

Si, me resfiero , en tu ejemplo , a que no salgan los campos de Country, salesperson,.....
Sólo los datos.

Jorge Dunkelman 17 enero, 2014 13:46  

Los campos pasan con los datos. Lo que puedes hacer es ocultar la fila.

Anónimo,  17 enero, 2014 15:26  

si, es lo que imaginaba, la duda era si se podía hacer con alguna opción o no.
Muchas gracias por la aclaración.

Jhonatan Araneda 19 febrero, 2014 21:56  

Una duda, esto se puede realizar con Power Query, es decir, asignar un valor dependiendo de una celda?

Jorge Dunkelman 21 febrero, 2014 11:16  

Creo que si, pero no es trivial. Si sigues la serie de artículos que he publicado últimamente (Usos del Power Query...), verás que yo mismo me encuentro en proceso de estudio de la herramienta. Espero poder publicar una nota sobre el tema dentro de unos días.

Ezequiel 25 febrero, 2014 19:45  

Hola Jorge.
Estoy teniendo el siguiente inconveniente:
Cuando vinculo la celda con los parámetros, funciona perfecto. Sin embargo cuando cierro el libro, no me guarda la ruta a la celda para pasar el parámetro al query. Vuelvo a abrir el libro, ejecuto la consulta y no funciona.
Sabés que puede estar pasando?

Gracias.

Jorge Dunkelman 09 marzo, 2014 19:07  

¿Con ruta a la celda te refieres a la referencia, o la celda se encuentra en un cuaderno remoto?

Cesar Ayala 29 abril, 2014 07:36  

Estimado Ezequiel, yo tengo el mismo problema, al parecer es un bug de excel. Tal vez lo pudiste solucionar??? A continuación cito lo que tú mencionas:
"Cuando vinculo la celda con los parámetros, funciona perfecto. Sin embargo cuando cierro el libro, no me guarda la ruta a la celda para pasar el parámetro al query. Vuelvo a abrir el libro, ejecuto la consulta y no funciona."

Jorge Dunkelman 29 abril, 2014 11:52  

Hola César y Ezequiel, he reconstruido el proceso, guardando y cerrando el cuaderno y no se produce el problema que ustedes señalan.Pueden mandarme el cuaderno para que le de un vistazo.

Jorge Reyes 04 junio, 2014 03:11  

Hola Jorge,

Primeramente agradecerte y felicitarte por tu gran labor.

Tengo un archivo en excel el cual contiene una base de datos y generé varias consultas MS Query ambas las grabe en una misma carpeta. Si copio la carpeta completa en otra PC para que trabaje con ella otra persona, al querer refrescar no trabaja. La ruta generada en la primer PC queda grabada como absoluta y no la reconoce la segunda PC. ¿Hay algo que pueda hacer?

Saludos!

Jorge Reyes

Jorge Dunkelman 04 junio, 2014 08:57  

Hola Jorge, se puede editar la cadena de conexión (de hecho, toda la consulta) en el editor del MSQuery. En el editor verás un botón "Sql" que te permite editar el texto del comando de la consulta.

Unknown 07 junio, 2014 07:32  

Saludos Jorge D, excelente aporte y de mucha ayuda, Gracias

susana cortés 04 agosto, 2014 14:19  

Muchas gracias por tu ayuda, he seguido al pie de la letra tus indicaciones pero si intento vincular as fechas a dos casillas excel me da el siguiente error: el día del mes debe estar entre 1 y el último día del mes, parece un problema de formato, pero tengo las dos celdas con formato fecha. Si lo ejecuto desde la primera opción de los parámetros, pedir valor usando la cadena siguiente, me funciona perfectamente. ¿A que se puede deber el problema? Muchas gracias, un saludo
Susana

Jorge Dunkelman 04 agosto, 2014 15:43  

Más que un problema de formato parece ser un problema de notación. En ciertos países se usa la notación americana (mes/días/año). ¿Cuáles son las definiciones regionales de tu sistema?

susana cortés 04 agosto, 2014 15:52  

Mi sistema tiene el Formato Español (de España) y el formato de fecha corta es: dd/MM/aaaa

¿Te da alguna pista a lo que me sucede? Gracias

Jorge Dunkelman 04 agosto, 2014 16:18  

Te sugiero que me env[ies el cuaderno (ver el enlace Ayuda, en la parte superior de la plantilla).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP