Jump to content

Hacer Consultas de datos como se hace en SQL .. en ExCel?????


Recommended Posts

tengo una base de datos de considerable tamaño 32.000 registros con 24 columnas... y deseo buscar ordenar los datos haciendo consultas Sql (SELECT COUNT... ORDER BY HAVING... WHERE ETC) como se hiciera en MySql, o Acces SQlServer...

Creo que mas de uno me respondera que importe mi base de datos a un Access u otro formato (XML o CVS) y la coloque en algun motor de BD; pero las consultas las quiero hacer desde un UserForm del mismo excel, y que el usuario de la hoja indique los paremetros de busqueda, ordenamiento y demas...

Bueno no siendo mas..

Cualquier información muchas gracias:rolleyes:

Link to post
Share on other sites

Si interesante los datos GRACIAS MACRO ANTONIO pero ... no hay forma de hacer consultas directamente con excel?? sin tener que recurrir a Acces o un motor de BD con el dsn?

Y si no se puede porque alguna vez oi o vi en algun otro foro que se podian hacer cubos olap en excel???

gracias

Link to post
Share on other sites

Hola:

Siguiendo el hilo de la respuesta de Abraham, he hecho estas macros:

Macro para leer una hoja, o un rango de una hoja, de un libro

Dim Datos As Variant
Private Sub Importar_Excel(Libro As String, _
Hoja As String, _
Optional Rango As String = "")

Dim conexion As ADODB.Connection, rs As ADODB.Recordset
Set conexion = New ADODB.Connection
conexion.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & Libro & ";" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""

' Nuevo recordset
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

If Rango <> ":" Then Hoja = Hoja & "$" & Rango

rs.Open "SELECT * FROM [" & Hoja & "]", conexion, , , adCmdText

Datos = rs.GetRows

End Sub[/CODE]

Macro para llamar al procedimiento de lectura

[CODE]

Private Sub LlamarImportar_Excel()

Importar_Excel "Libro1.xls", "Hoja1"

'Activamos y cargamos el recordset en la Hoja 1 del libro activo
ThisWorkbook.Sheets("Hoja1").Activate
For x = 0 To UBound(Datos, 1) - 1
For y = 0 To UBound(Datos, 2) - 1
ActiveSheet.Cells(y + 1, x + 1) = Datos(x, y)
Next y
Next x
[/CODE]

Saludos.

Link to post
Share on other sites

Adjunto ejemplo para consultar datos en Excel con el lenguaje SQL.

La tabla de datos está en la hoja Datos y los resultados van a parar a la hoja Resultados.

En las celdas de la columna A de la hoja Principal:

1º) se escriben las sentencias SQL,

2º) se selecciona una y

3º) se hace click en el botón SQL.

La tabla se referencia como [Datos$] en cada sentencia.

Ejemplo de sentencia:

SELECT manager_id, emp_fname, emp_lname, salary FROM [Datos$] WHERE status='T' ORDER BY salary DESC[/CODE]

Espero que esto sea lo que quería greg_dorian.

SentenciaSQL.zip

Link to post
Share on other sites

Hola

Solo para contribuir con el tema, recordar que el objeto range posee un método que permite copiar un recordset directamente sin necesidad de recorrerlo completamente registro a registro.

Range.CopyFromRecordset (método)

Copia el contenido de un objeto Recordset ADO o DAO en una hoja de cálculo, comenzando en la esquina superior izquierda del rango especificado. Si el objeto Recordset contiene campos con objetos OLE, este método no se completará con éxito.

tomando como base el ejemplo que amablemente subio el master pegones, seria algo como esto

Sheets(Resultado).Range("A2").CopyFromRecordset rst

saludos cordiales

Link to post
Share on other sites

Hola:

Pues nada, entre Abraham, Sailepaty, Pedro y Never que han aportado el conocimiento, yo gustosamente, adopto el papel de humilde "secretaria" pasandolo a "limpio":


Private Sub Importar_Excel(Libro As String, _
Hoja As String, _
Optional Rango As String = "")

Dim conexion As ADODB.Connection, rs As ADODB.Recordset
Set conexion = New ADODB.Connection
conexion.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & Libro & ";" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""

' Nuevo recordset
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

If Rango <> ":" Then Hoja = Hoja & "$" & Rango

rs.Open "SELECT * FROM [" & Hoja & "]", conexion, , , adCmdText

Sheets(Hoja).Range("A1").CopyFromRecordset rs

End Sub[/CODE]

Macro para llamar al procedimiento de lectura

Código:

[CODE]Private Sub LlamarImportar_Excel()

Importar_Excel "Libro1.xls", "Hoja1"

End Sub[/CODE]

Un saludo afectuoso para todos

Link to post
Share on other sites

Aquí va una opción con MSQuery. Se crea un QueryTable que se puede modificar mediante código o manualmente. Esta probado no extensamente en Excel 2007 y por el manejo de algunos objetos y parámetros no creo que funcione en versiones anteriores. Pero seguro que los expertos en VBA lo podrán adaptar fácilmente para versiones anteriores, además de corregirlo y mejorarlo indudablemente.

1.- Seleccionar en la celda B13 de la hoja PARAMETERS la opción Create.

2.- Seleccionar la sentencia SQL que se desea ejecutar en la celda B15 de la hoja PARAMETERS

3.- Presionar el botón RUN QUERY

Una vez creada la conexión ahora pueden jugar con las diferentes sentencias SQL que puse o crear las propias.

1.- Seleccionar en la celda B13 de la hoja PARAMETERS la opción Refresh

2.- Seleccionar la sentencia SQL que se desea ejecutar en la celda B15 de la hoja PARAMETERS

3.- Presionar el botón RUN QUERY

Saludos

EXCEL QUERY.zip

Link to post
Share on other sites

Abrí el archivo que había publicado y me di cuenta que es recomendable salvarlo antes de correr la macro para que identifique correctamente la ruta y el nombre del archivo.

Y ya estando con el archivo le hice una pequeña modificación para que no presente un error cuando la acción es igual a Crear y la conexión ya existe.

Saludos

EXCEL QUERY.zip

Link to post
Share on other sites

Hola

Muy buenos los ejemplos, tanto la recopilacion del master antoni como el ejemplo del master sailepaty

Por mi parte solo comentar que es el método(Query) que prefiero por ser nativo de excel, y por que usando el asistenta se puede divertir uno haciendo uniones, joins etc, lo cual presenta muchas ventajas (que claro igual sepuede hacer por medio de ado y dao), el asistente es digamos una versión simplificada de access, y la ventaja es que podemos usar la la grabadora de macros.

También comentar que es necesario que las tablas sean un rango con nombre definido (para usar obtención de datos externos desde MS Query), es decir cada tabla debe tener definido un nombre en el area(rango) que ocupan, por lo que ya sea que medante la macro vaya uno determinado el tamaño de la tabla y modificando la referencia la nombre de la nueva área que ocupa, o usar un rango dinámico con formulas para referirse al área que ocuapa la tabla, de forma tal que conforme vaya creciendo no haya que estar modificando constantemente las referencias.

saludos cordiales

Link to post
Share on other sites

Es eso lo que estoy buscando; tienes algun video o demostracion (paso a paso) con el msquery?

Alguna vez quise experimentar con eso, pero no se ahora con el office 2007 exista..

gracias si tienes algun otro aporte

Hola

Muy buenos los ejemplos, tanto la recopilacion del master antoni como el ejemplo del master sailepaty

Por mi parte solo comentar que es el método(Query) que prefiero por ser nativo de excel, y por que usando el asistenta se puede divertir uno haciendo uniones, joins etc, lo cual presenta muchas ventajas (que claro igual sepuede hacer por medio de ado y dao), el asistente es digamos una versión simplificada de access, y la ventaja es que podemos usar la la grabadora de macros.

También comentar que es necesario que las tablas sean un rango con nombre definido (para usar obtención de datos externos desde MS Query), es decir cada tabla debe tener definido un nombre en el area(rango) que ocupan, por lo que ya sea que medante la macro vaya uno determinado el tamaño de la tabla y modificando la referencia la nombre de la nueva área que ocupa, o usar un rango dinámico con formulas para referirse al área que ocuapa la tabla, de forma tal que conforme vaya creciendo no haya que estar modificando constantemente las referencias.

saludos cordiales

Link to post
Share on other sites

Hola greg_dorian

Por aca puedes econtrar un video básico que te puede dar una idea del funcionamiento de MS Query

Vídeo Tuorial básico MS Query - grupos.emagister.com

Si bien en el video la conexión es con una base de datos de access, el proceso es similar para excel, los pasos hacerlo con datos en excel son:

-Asignarle un nombre al area de datos (es decir a cada tabla) desde la ficha de formulas boton administrador de nombres, nuevo , seleccionas tu rango y le asignas un nombre, o simplemente seleccionar tus datos y asigales el nombre un desde el cuadro de nombres (aparece del lado izquierdo en la parte superior inmediatamente a lado de la barra de formulas aqui el proceso)

En la ficha datos boton de otras fuentes y eliges desde ms Query, iniciara un asistente, ahi seleccionas tu mismo archivo de excel es decir una conexion con si mismo (eso si es que tienes ahi las tablas o en su caso el archivo donde las tengas, de preferencia es mejor que esten en otro libro) y entonces reconocera automaticamente las tablas (ello gracias al paso 1)

Y entonces ya desde MS Query puedes usar la herramientas que presenta para la consulta, o en su caso escribir la instruccion SQL (todo ello desde ms query) una vez que obtengas lo que deseas puedes cierra y devuelves los datos a excel (ello desde el menu archivo de MS Query Devolver datos a excel), y en excel te preguntara un asistente cómo los deseas, como tabla, como tabla dimamica, etc.

La mejor alternativa es que subas un extracto de tus datos y trabajar sobre ellos par colocar ahi los ejemplos

saludos cordiales

Link to post
Share on other sites

Gracias rugarte y me alegra que te sirva mi aporte.

Ahora lo subo para consultas con 2 tablas y, como verás, se pueden crear distintos formatos de las sentencias SQL:

SELECT Departamentos.dept_name AS "Departamento", COUNT(*) AS "Total Empleados"
FROM Departamentos Departamentos, Empleados Empleados
WHERE Departamentos.dept_id = Empleados.dept_id
GROUP BY Departamentos.dept_name[/CODE]

[CODE]SELECT `Empleados$`.emp_fname, `Empleados$`.emp_lname, `Empleados$`.birth_date, `Empleados$`.phone
FROM `Empleados$` `Empleados$`
WHERE (`Empleados$`.sex='F')
ORDER BY `Empleados$`.birth_date[/CODE]

[CODE]SELECT salary, * FROM [Empleados$] WHERE status='A' ORDER BY salary DESC[/CODE]

Como ves se puede declarar la consulta de varias maneras y sobre todo los nombres de las tablas.

He incluido la sugerencia de [b]neverdelimon1[/b] sobre

Me parece muy interesante como ejercicio la idea de crear un UserForm como un asistente equipado con textos para que se concatenen y poder generar la consulta que deseemos, de forma parecida a como lo hace [b]MS Query[/b], con la que tan amablemente nos ha ayudado [b]sailepaty[/b].

Por cierto, para darme las gracias puedes pinchar abajo a la izquierda de este mensaje, en la imagen de una mano con la palabra "Gracias".

SentenciaSQL2.zip

Link to post
Share on other sites
Guest
This topic is now closed to further replies.
×
×
  • Create New...

Important Information

Privacy Policy