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
Hola Jorge L.Dunkelman quisiera saber de que página puedo descargar northwind.mdb
ResponderBorrarGracias.
Hola Jorge:
ResponderBorrar¿Se puede elegir, en su caso, por ejemplo 2 paises?
Gracias anticipadas.
Saludos a tod@s.
En esta página hay un enlace para descargar la nueva versión de la base de datos.
ResponderBorrarEn los medios de instalación de Office hasta la versión 2007, existe el archivo y de ahí se puede instalar.
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.
ResponderBorrarExcelente
ResponderBorrarSe puede utilizar este metodo cuando tenemos una consulta, con subconsultas, las cuales usan los mismos parametros de la consulta principal.
ResponderBorrarAnexo 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..
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.
ResponderBorrarComo 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.
ResponderBorrarJorge, felicitaciones por la nota.
Coincido contigo aunque no estoy seguro que se trate de una tendencia general.
ResponderBorrarEstoy 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?
Excelente idea Jorge. Adelante con las notas Access-Excel
ResponderBorrarJorge 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.
ResponderBorrarCoincido con Fer acerca que es ideal pero no se si es una tendencia general.
Jorge, una pequeña consulta: Cómo es el uso de comodines en una consulta de este tipo?
ResponderBorrarMe 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.
Se puede, pero no con la técnica que muestro en esta nota. Veré de publicar algo al respecto.
ResponderBorrarHola 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.
ResponderBorrarUn gran abrazo y gracias por el aporte
Muchas gracias por sus aportes don Jorge..
ResponderBorrarHola Jorge,
ResponderBorrarSe 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.
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.
ResponderBorrarJorge, Tratare de explicar mejor.
ResponderBorrarPrimero 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
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.
ResponderBorrarJorge.
ResponderBorrarte 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#
Agregando los campos correspondientes (nombre, apeliido) en la consulta. Estos no son parámetros, el parámetro de la consulta es el campo ID.
ResponderBorrarTengo esta consulta
ResponderBorrarSELECT 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
Excelente aporte Jorge!!
ResponderBorrarEstoy 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!!
Si, aparentemente es un problema de formato. Fijate como aparecen las fechas en el ejemplo que publico.
ResponderBorrarCarlos, es lo que muestro en la nota (o no entendí la consulta).
ResponderBorrarCOMO SE HACE PARA EXPORTAR mas de 10.000 registros en una consulta a excel?
ResponderBorrarExcel 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?
ResponderBorrarHola Jorge:
ResponderBorrar¿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
Hermano en las mismas ando yo, no logro encontrar el método, si lo encontraste por favor comparte el dato.
BorrarComo ya puse en algunos otros comentarios, MS Query se ha vuelto obsoleto. Es hora de pasar a las nuevas (no tan nuevas) herramientas: Power Query, PowerPivot, Power BI. Para importar y transformar datos Power Query es infinitamente superior a MS Query (qepd).
BorrarTe 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:
ResponderBorrar=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.
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..
ResponderBorrarSupongo que tiene que ver con las definiciones de la BD Oracle. Consulta con tu DBA.
ResponderBorrarMe ha sido de gran ayuda pero tengo un par de problemas que no se resolver.
ResponderBorrarYo 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??
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).
ResponderBorrarHola Jorge,
ResponderBorrarExcelente 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?
Robbie,tu comentario se me pasó por alto y recién ahora lo publico. Mis disculpas.
ResponderBorrarBien, eso es lo que hace la consulta, trae todos los valores de las tabla que cumplen con la condición.
Excelente post. Fácil y directo. Muchas gracias por este magnifico blog
ResponderBorrarGenial !!! Lo que estaba buscando...
ResponderBorrarHola Jorge, es genial el post, me ha servido de mucha ayuda.
ResponderBorrarUna 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
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.
ResponderBorrarMuchas gracias Jorge
ResponderBorrarJorge:
ResponderBorrarPorque pierde la celda que defines cuando vuelves abrir el archivo de excel en una tabla dinámica (tomar el valor de la siguiente celda)
¿Podrías definir un poco más lo consulta o enviarme una decripción por mail privado?
ResponderBorrarHola Jorge, excelente tutorial
ResponderBorraruna pregunta, se hacer que sólo devuelva los datos sin que muestre el nombre del campo??
muchas gracias
¿Te refieres a que no aparezcan los encabezamientos de las columnas en la hoja de Excel?
ResponderBorrarSi, me resfiero , en tu ejemplo , a que no salgan los campos de Country, salesperson,.....
ResponderBorrarSólo los datos.
Los campos pasan con los datos. Lo que puedes hacer es ocultar la fila.
ResponderBorrarsi, es lo que imaginaba, la duda era si se podía hacer con alguna opción o no.
ResponderBorrarMuchas gracias por la aclaración.
Una duda, esto se puede realizar con Power Query, es decir, asignar un valor dependiendo de una celda?
ResponderBorrarCreo 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.
ResponderBorrarHola Jorge.
ResponderBorrarEstoy 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.
¿Con ruta a la celda te refieres a la referencia, o la celda se encuentra en un cuaderno remoto?
ResponderBorrarEstimado 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:
ResponderBorrar"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."
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.
ResponderBorrarHola Jorge,
ResponderBorrarPrimeramente 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
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.
ResponderBorrarSaludos Jorge D, excelente aporte y de mucha ayuda, Gracias
ResponderBorrarMuchas 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
ResponderBorrarSusana
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?
ResponderBorrarMi sistema tiene el Formato Español (de España) y el formato de fecha corta es: dd/MM/aaaa
ResponderBorrar¿Te da alguna pista a lo que me sucede? Gracias
Te sugiero que me env[ies el cuaderno (ver el enlace Ayuda, en la parte superior de la plantilla).
ResponderBorrarMuy bien explicado Jorge, me surge una duda.
ResponderBorrarEn el Tercer paso: transferir los resultados a Excel, se detalla que no hace falta ingresar los datos de los parámetros. Si se hubiesen informado funcionaría igual.
¿Me explico, es necesario importar primero todos los datos de la entidad o no?
Muchas gracias
No, se puede introducir los valores de los parámetros en el MS Query. La idea de la nota es construir el modelo de manera que los parámetros se introduzcan en celdas de la hoja de Excel.
ResponderBorrarBuen artículo. Pero, cómo puedo pasar la dirección de la celda del parámetro mediante una macro?
ResponderBorrarTe explico.
Tengo un archivo que ejecuto en varias oficinas. En todas ellas los archivos locales tienen la misma estructura, pero diferentes rutas. Así que el MSQuery lo hace una macro que lo adapta a la ruta local.
Pero no encuentro el modo de hacer en macro la parte de definir la celda del parámetro.
Si uso la grabadora de macros, sólo me graba la parte del refresh.
Alguna idea de como ponerlo?
Hola, tendrías que definir una variable y usarla en el código para pasar la dirección de la celda. Esta tarea no se puede hacer usando la grabadora y requiere ciertos conocimientos de Vba.
ResponderBorrarDefinirla no es problema, pero como la paso?
ResponderBorraren la documentación de Microsoft al respecto, sólo encuentro como hacerlo como tu lo publicaste, pero nada sobre los atributos y variables de la consulta.
Ya tengo hechas todas las que necesito, pero es latoso que a mitad de la macro te aparezca una ventana para definir los parámetros, y más cuando son varios (tengo algunas que usan 3). Ni puedo hacer que otra persona lo use, porque se perdería.
Ojalá me puedas decir como pasar la variable de celda o de rango a los parámetros.
Saludos y gracias
Estimado, MS Query tiene muchas limitaciones, entre ellas la muy pobre documentación. Si lo estás usando en una macro no veo otra forma que usando el código pasando las variables desde un formulario.
ResponderBorrarHola quisiera saber como aplicar un filtro con referencia a un rango de fechas ejemplo primer dia del mes actual y dia presente del mes actual en un campo de fecha
ResponderBorrarChristian, no termino de entender la consulta, si se refiere al MsQuery (el tema del post) o a Autofiltro...
ResponderBorrarPor favor, fijate lo que pongo en el enlace Ayuda (en la parte superior del blog).
¿y cómo se podría usar algo del tipo WHERE código IN () Y que ese listado lo recoja de una serie de celdas??
ResponderBorrarTendrías que echar mano a programación (fijate en el comentario de Carlos de León del 8/12/2012). Pero eso es llevar el MS QUery a un extremo donde ya puedes usar herramientas más apropiadas, como Access o Power Query.
ResponderBorrarHola Jorge, esta de mas decirte que tu blog es excelente! actualmente tengo el mismo problema que reportaron César y Ezequiel entre febrero y abril 2014 y en la linea de comentarios no se publico la solución, por eso pregunto por esta vía ya que no consigo nada claro por la web; Tengo lo siguiente y lo probé en Excel 2010 y 2016 y me da el mismo error:
ResponderBorrar1) Abro Excel->Obtener datos externos->desde MsQuery
2) hago la conexión a la consulta de SQL
3) Paso unos parametros
4) devuelvo los datos en Tabla Dinamica.
Hasta aquí todo bien!
5) Coloco unos valores en las Celdas de Excel para pasarlos como parámetros
6) Me voy a Propiedades de Conexión-> Definición-> Parámetros-> Tomar el Valor de la siguiente celda-> selecciono las que coloque en el punto 5)
7) Actualizo y todo funciona perfecto.
8) hago pruebas cambiando las celdas de excel y vuelvo actualizar y funciona perfectamente.
Aquí el problema:
9) cierro y abro el archivo y Excel no guardo los parámetros seleccionados (punto 6)
como hago para que Excel guarde la información??
de antemano muchas gracias por la ayuda que puedas brindarme.
saludos!!
Hola Miguel, el MS Query nunca recibió mucha atención por parte de Microsoft y lo que te sucede parece ser un bug, como alguién señaló en uno de los comentarios.
ResponderBorrarHa legado el momento de abandonar MS QUery y pasar al Power Query. Prometo cumplir mi promesa de hace mucho tiempo y publicar en breve una nota sobre el tema.
Gracias Jorge por tu pronta repuesta, me imagine también que era un bug pero quería la confirmación de los expertos!! jejeje me tocara hacer una macro para ver si puedo normalizar esta situación o migrar a Power Query y hacer pruebas por ahí, de nuevo muchas gracias y saludos!
ResponderBorrarGracias Jorge
ResponderBorrar