Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Filtrar Datos Excel con Consulta SQL Vs Bucle While Wend


(adsbygoogle = window.adsbygoogle || []).push({});

En este post se presenta una comparación entre macros, se muestra por un lado una macro que permite filtrar datos mediante criterios utilizando un Bucle con la estructura While … Wend y por otro lado se usa SQL para buscar datos en Excel, los resultados son los mismos, en base de datos chicas el tiempo de búsqueda es prácticamente el mismo, pero en bases de datos más grandes SQL es mucho más rápido.

Descarga el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  
Mira una explicación detallada en el vídeo, suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

 

Al descargar el ejemplo y abrir el libro de Excel se podrá observar que se encuentran dos botones, el primero ejecuta un filtro o búsqueda utilizando un bucle con la estructura While…Wend; el segundo botón realiza la misma búsqueda o filtro de datos utilizando SQL.

Los criterios de búsqueda se encuentran en la Hoja1 celda B1 y Celda E1, en el ejemplo que se muestra se busca todos los datos coincidentes en la base de datos que se encuentra en la Hoja2, que sena iguales a la marca «Coca Cola» cuyo precio de venta (Pv) sea mayor a «Cinco».

Con el bucle While… When, se recorre todas las filas buscando los registros que coincidan con los criterios, en este ejemplo los criterios se asignan a variables que son las siguientes:

marca = UCase(b.Range(«B1»))
signo = b.Range(«D1»)
valor = b.Range(«E1»)
marcabus = UCase(a.Cells(filabus, «D»))
valorbus = a.Cells(filabus, «E»)
Las primeras variables son las de la hoja de destino y las últimas dos variables son los datos que va tomando cada vez que se recorra las filas con el bucle While … Wend

Si la marca ingresada en B1 es coincidente con la marca de la fila correspondiente de la Hoja2 que en cada momento recorre el bucle y además el valor de venta es mayor al valor buscado, entonces el registro cumple la condición, lo copia y pega en la Hoja1 y así sucesivamente con cada uno de los ítem de la base de datos.

If marcabus = marca And valorbus > valor Then
a.Range(«A» & filabus & «:G» & filabus).Copy Destination:=b.Range(«A» & fila)

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Quizá sea de utilidad también

Como crear una factura con excel, guardarla y enviarla por mail automáticamente

Como cargar listbox con datos provenientes de varias hojas

Como repetir en Excel un mismo caracter varias veces

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

La otra forma de búsqueda es el Filtro de Datos de Excel a través de SQL, para ello primero hay que realizar una conexión con el Libro Excel, que puede ser el mismo u otro libro, en este caso es el mismo, ya que los datos se encuentran en el mimos libro sobre el cual se trabaja.

La conexión con el mismo libro se realiza con el siguiente código, a continuación la SQL, que es el string de consulta que contiene los criterios de búsqueda o filtro de datos.

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»
sql = «SELECT * FROM [» & «Hoja2$» & «] WHERE Ucase(» & a.Range(«D1») & «) LIKE Ucase(‘%» & b.Range(«B1») & «%’) AND pv » & b.Range(«D1″) & » » & b.Range(«E1″) & » ORDER BY pv ASC»

La SQL se podría leer: Selecciones todas las columnas de la Hoja2 cuando la columna Marca coincida con el texto (marca) ingresado en B1 y el precio de venta sea Mayor al precio que figura en la columna «Pv» de la base de datos.

Una vez ejecutada la SQL los datos filtrados o coincidentes se mantienen en memoria (Recorset), hasta tanto sean usado y liberadas las variables.

Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs

En el código anterior los datos filtrados son copiados a partir de la fila 1 columna 1 es decir celda A3, ya que en la fila 2 van los encabezados de columna.

Para liberar las variables y por ende liberar recursos, se usa:

Set rs = Nothing
cn.Close
Set cn = Nothing

El ejemplos denominado Como Filtrar Datos Excel con Consulta SQL vs. Bucle While … Wend, se puede descargar desde el link del final que esta en forma posterior a la codificación del ejemplo que se encuentra a continuación.

Código que se inserta en un módulo

Sub ConsutaSQLExcel()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ctl As Object
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String
On Error Resume Next
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set b = Sheets(«Hoja1»)
Set a = Sheets(«Hoja2»)

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»
sql = «SELECT * FROM [» & «Hoja2$» & «] WHERE Ucase(» & a.Range(«D1») & «) LIKE Ucase(‘%» & b.Range(«B1») & «%’) AND pv » & b.Range(«D1″) & » » & b.Range(«E1″) & » ORDER BY pv ASC»

uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range(«A3:G» & uf).Clear
a.Range(«A1:G1»).Copy Destination:=b.Range(«A2»)
Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs
b.Range(«B:B»).NumberFormat = «dd/mm/yyyy»
Set rs = Nothing
cn.Close
Set cn = Nothing
If b.Range(«A3») <> Empty Then
MsgBox («La busqueda se realizó con éxito»), vbInformation, «AVISO»
Else
MsgBox («No se encontraron regisgros para el criterio de búsqueda»), vbInformation, «AVISO»
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub ConsutaBucleWhileWend()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
‘On Error Resume Next
Set b = Sheets(«Hoja1»)
Set a = Sheets(«Hoja2»)
filabus = 2
fila = 3
uf = b.Range(«A» & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range(«A3:G» & uf).Clear
a.Range(«A1:G1»).Copy Destination:=b.Range(«A2»)

While a.Cells(filabus, «A») <> Empty
marca = UCase(b.Range(«B1»))
signo = b.Range(«D1»)
valor = b.Range(«E1»)

marcabus = UCase(a.Cells(filabus, «D»))
valorbus = a.Cells(filabus, «E»)
If marcabus = marca And valorbus > valor Then
a.Range(«A» & filabus & «:G» & filabus).Copy Destination:=b.Range(«A» & fila)
fila = fila + 1
End If
filabus = filabus + 1
Wend

b.Range(«B:B»).NumberFormat = «dd/mm/yyyy»
If b.Range(«A3») <> Empty Then
MsgBox («La busqueda se realizó con éxito»), vbInformation, «AVISO»
Else
MsgBox («No se encontraron regisgros para el criterio de búsqueda»), vbInformation, «AVISO»
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (33)

Wow, that’s what I was searching for, what a stuff! present here at this website,
thanks admin of this website.

Responder

Great blog here! Also your site loads up fast!
What web host are you using? Can I get your affiliate link to your
host? I wish my website loaded up as fast as yours lol

Responder

Today, while I was at work, my cousin stole my apple ipad and
tested to see if it can survive a 30 foot drop, just so she
can be a youtube sensation. My apple ipad is now destroyed and she
has 83 views. I know this is entirely off topic but I had to
share it with someone!

Responder

Howdy fantastic website! Does running a blog like
this require a large amount of work? I’ve no understanding of programming but I had been hoping to start my own blog in the near
future. Anyway, should you have any ideas or tips
for new blog owners please share. I understand this is off subject nevertheless I simply
had to ask. Thank you!

Responder

Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to far added agreeable from you! By the way, how can we communicate?

Responder

At this time it appears like Drupal is the best blogging platform out there right now.
(from what I’ve read) Is that what you are using on your blog?

Responder

Wonderful goods from you, man. I’ve understand your stuff previous to and you’re just extremely fantastic.
I really like what you’ve acquired here, really like what you’re stating and the way
in which you say it. You make it entertaining and you still
take care of to keep it wise. I can’t wait to read much more from you.
This is actually a tremendous web site.

Responder

Hi to every , since I am in fact eager of reading this weblog’s
post to be updated regularly. It contains fastidious
stuff.

Responder

I used to be recommended this web site by means of my cousin. I am now not sure whether or not this post is written by way of him as nobody else recognize such specified about my trouble.
You’re incredible! Thank you!

Responder

If you would like to improve your knowledge only keep visiting this website and be
updated with the most recent news update posted here.

Responder

My family members always say that I am killing my
time here at web, except I know I am getting familiarity every day by reading such fastidious content.

Responder

At this time it sounds like Expression Engine is the preferred blogging platform available right now.
(from what I’ve read) Is that what you are using on your blog?

Responder

I’ve been surfing online more than 3 hours today, yet I never found any interesting article like
yours. It’s pretty worth enough for me. Personally,
if all webmasters and bloggers made good content as you did, the net will
be a lot more useful than ever before.

Responder

Pretty section of content. I just stumbled upon your site and in accession capital to assert that I get
in fact enjoyed account your blog posts. Any way I will
be subscribing to your feeds and even I achievement you
access consistently quickly.

Responder

Since the admin of this web page is working, no question very quickly
it will be famous, due to its feature contents.

Responder

That is a very good tip particularly to those fresh to the
blogosphere. Brief but very precise info… Thanks for sharing this one.

A must read post!

Responder

Wow that was odd. I just wrote an incredibly long comment but after I clicked submit my comment didn’t show
up. Grrrr… well I’m not writing all that over again. Anyway, just wanted to say
superb blog!

Responder

It is perfect time to make some plans for the future and it is time
to be happy. I’ve learn this publish and if
I could I wish to recommend you few fascinating things or
suggestions. Perhaps you could write subsequent articles referring to this article.

I want to learn more things about it!

Responder

When some one searches for his necessary thing, so he/she desires to be available that in detail, thus that thing is maintained over here.

Responder

Admiring the persistence you put into your website and in depth information you present.
It’s great to come across a blog every once in a while that isn’t the same old rehashed material.
Wonderful read! I’ve saved your site and I’m including your RSS feeds to my Google account.

Responder

Attractive portion of content. I simply stumbled upon your blog and in accession capital to say
that I acquire actually loved account your blog
posts. Any way I’ll be subscribing on your augment or even I achievement you access
constantly rapidly.

Responder

What’s up to every body, it’s my first pay a visit of this website; this website contains
awesome and really excellent data in support of readers.

Responder

If some one desires to be updated with latest technologies after
that he must be pay a quick visit this website and be
up to date daily.

Responder

Can I just say what a relief to find somebody who truly knows what they are discussing online.
You definitely realize how to bring an issue to light
and make it important. More and more people ought to read this and understand this
side of your story. I can’t believe you are not more popular since you definitely have
the gift.

Responder

This article will help the internet visitors for setting up new blog or even a
blog from start to end.

Responder

Thanks a bunch for sharing this with all of us you
actually recognise what you are speaking about! Bookmarked.

Kindly also visit my website =). We can have a hyperlink alternate arrangement between us

Responder

It’s perfect time to make some plans for the future and it’s time
to be happy. I have read this post and if I could I want to suggest
you few interesting things or advice. Maybe you can write next articles referring to this article.
I desire to read even more things about it!

Responder

Way cool! Some very valid points! I appreciate you writing this article and
the rest of the site is really good.

Responder

Hi there, There’s no doubt that your site might be having
web browser compatibility issues. When I look at your site in Safari, it looks fine however, when opening in I.E., it has some overlapping issues.
I simply wanted to provide you with a quick heads up!
Apart from that, wonderful blog!

Responder

Hello there, just became aware of your blog through Google, and found that
it’s really informative. I am gonna watch out for brussels.
I will be grateful if you continue this in future. Many people will be benefited from your writing.
Cheers!

Responder

I love your blog.. very nice colors & theme. Did you design this website yourself or did
you hire someone to do it for you? Plz respond as
I’m looking to construct my own blog and would like to find out where
u got this from. many thanks

Responder

Good day! I know this is kinda off topic nevertheless I’d figured I’d ask.
Would you be interested in trading links or maybe guest writing a blog post or vice-versa?
My website addresses a lot of the same topics as yours and I feel we
could greatly benefit from each other. If you’re interested feel free to shoot me
an email. I look forward to hearing from you! Awesome blog by the way!

Responder

Hello! I just want to give you a big thumbs up for your great info you’ve got here on this post.
I am returning to your site for more soon.

Responder