Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como buscar datos en Excel metodo Find Vs SQL


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

En el post se muestra una macro que permite buscar datos en Excel, la búsqueda de datos en Excel se hace de dos formas con el método Find y a través de SQL (Structured Query Language), Lenguaje de Consulta Estructurada). Es decir la macro busca un dato determinado en una base de datos de Excel, una vez encontrado el dato, copia y pega todos los registros relacionados con el dato buscado en la hoja de Excel.

Desde el final del post se puede descargar 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.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, 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.

  
El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; 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);
}
}

 

El objetivo del post es mostrar como se puede buscar datos en Excel utilizando dos formas diferentes, llegando a un mismo resultado, a mi entender para bases de datos chicas con el método find basta y sobra, pero en bases de datos más grandes SQL se comporta muy bien en cuanto a la rápidez de búsqueda.

Una ventaja que tiene SQL es que no solo se puede buscar datos sino se puede insertar datos, modificar los mismos, como así también usar criterios complejos para el filtro de datos acá tienes más ejemplos que muestran como se usa SQL en Excel.

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

Quizá sea de utilidad también

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

Como enviar mail con archivo Excel y PDF mediante Outlook con Excel

Como hacer un link o hiperlink a google maps con Excel

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

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

En el ejemplo se debe ingresar el número a buscar en la celda B1, la macro buscará en la columna A de la hoja «URL MACRO EJEMPLOS» el número ingresado en la celda B1, escribiendo los registros asociados de las distintas columnas en la fila 4.

Si se requiere buscar con el método Find se presionara el primero botón, el cual ejecuta para buscar el dato ingresado en la celda B1 en la Columna A de la hoja «URL MACRO EJEMPLOS», con el siguiente código:

busco = Cells(1, «B»)
Set codigo = a.Range(r).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)
Si el datos es encontrado es decir no es vacío el objeto código creado a los fines de la búsqueda, determina cual es la fila donde encontró el dato y lo carga en la varible: código.row; que luego se utiliza para obtener el restos de registros relacionados con el dato buscado y que se encuentran en esa fila, el siguiente es el código empleado:
If Not codigo Is Nothing Then
Cells(4, «A») = a.Cells(codigo.Row, 1)
Para realizar la misma búsqueda, pero utilizando SQL se debe proceder a realizar una conexión con el libro de Excel, en este caso es una hoja distinta del mismo libro de Excel, pero puede ser otro libro distinto también, el código es el siguiente:
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»
Seguidamente se crea la consulta SQL que es la que se debe ejecutar para poder filtrar el o los datos, es la siguiente:
sql = «SELECT * FROM [» & «URL MACRO EJEMPLOS$» & «] WHERE » & b.Range(«A1″) & » LIKE » & a.Range(«B1″) & » ORDER BY N ASC»
La SQL dice: Seleccione todas las columnas de la hoja (FROM) «URL MACRO EJEMPLOS» cuando el dato de la columna A de dicha hoja sea igual a la celda B1 de la hoja Hoja1, luego ordena por la columna denominada «N», aunque en este caso el orden no es necesario ya que es uno solo el dato coincidente, para filtrar los datos se debe ejecutar la SQL, de la siguiente forma:
Set rs = cn.Execute(sql)
Una vez ejecutada la SQL los datos obtenidos o filtrados  (Recordset) se conservan en memoria para ser usados, en este ejemplo los datos encontrados son escritos en la fila 4 de la Hoja1, el código es el siguiente:
a.Cells(4, 1).CopyFromRecordset Data:=rs
A continuación se encuentra el código completo que permite buscar datos en Excel con el Metodo Find y con SQL, seguidamente está el link de descarga del archivo de ejemplo, considera la posibilidad de aportar para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Código que se inserta en un módulo

Sub BuscarMetodoFind()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim uf As String
Set a = Sheets(«URL MACRO EJEMPLOS»)
pf = 2
uf = a.Range(«A» & Rows.Count).End(xlUp).Row
r = «A» & pf & «:A» & uf
busco = Cells(1, «B»)
Set codigo = a.Range(r).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)

If Not codigo Is Nothing Then
Cells(4, «A») = a.Cells(codigo.Row, 1)
Cells(4, «B») = a.Cells(codigo.Row, 2)
Cells(4, «C») = a.Cells(codigo.Row, 3)
Cells(4, «D») = a.Cells(codigo.Row, 4)
Cells(4, «E») = a.Cells(codigo.Row, 5)
Cells(4, «F») = a.Cells(codigo.Row, 6)
Else
Range(«4:4»).Clear
Cells(4, «A») = «No se encontraron registros en la base de datos»
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub FiltroSQLExcel()
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 a = Sheets(«Hoja1»)
Set b = Sheets(«URL MACRO EJEMPLOS»)

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»
sql = «SELECT * FROM [» & «URL MACRO EJEMPLOS$» & «] WHERE » & b.Range(«A1″) & » LIKE » & a.Range(«B1″) & » ORDER BY N ASC»
Set rs = cn.Execute(sql)
a.Cells(4, 1).CopyFromRecordset Data:=rs
Set rs = Nothing
cn.Close
Set cn = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub limpiar()
Range(«A4:F4»).ClearContents
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 un comentario

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

Comentarios (83)

I’m the proprietor of JustCBD brand (justcbdstore.com) and I’m presently trying to broaden my wholesale side of business. I really hope that someone at targetdomain is able to provide some guidance . I thought that the most ideal way to do this would be to reach out to vape companies and cbd retail stores. I was hoping if anybody could recommend a reputable web site where I can purchase CBD Shops B2B Database I am presently looking at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence which one would be the best selection and would appreciate any support on this. Or would it be much simpler for me to scrape my own leads? Suggestions?

Responder

Pretty! This has been an extremely wonderful post. Thanks for supplying this info.

Responder

Very nice article. I absolutely love this site. Stick with it!

Responder

Greetings! Very helpful advice within this article! It is the little changes that produce the biggest changes. Thanks a lot for sharing!

Responder

This site definitely has all of the information I needed about this subject and didn’t know who to ask.

Responder

It’s difficult to find well-informed people about this subject, but you sound like you know what you’re talking about! Thanks

Responder

I need to to thank you for this excellent read!! I definitely loved every little bit of it. I’ve got you bookmarked to look at new stuff you post…

Responder

I really love your site.. Pleasant colors & theme.
Did you make this website yourself? Please reply back as I’m trying to
create my own site and want to learn where you got this
from or what the theme is called. Thank you! adreamoftrains
website hosting companies

Responder

Hey very interesting blog!

Responder

After exploring a number of the articles on your web page, I honestly like your way of writing a blog. I saved it to my bookmark webpage list and will be checking back soon. Please visit my web site too and let me know what you think.

Responder

You’re so cool! I don’t believe I’ve read something like this before. So nice to find another person with some unique thoughts on this subject. Really.. many thanks for starting this up. This web site is something that’s needed on the web, someone with a little originality!

Responder

I’m impressed, I must say. Rarely do I encounter a blog that’s both educative and amusing, and let me tell you, you have hit the nail on the head. The problem is an issue that too few people are speaking intelligently about. I am very happy I came across this during my search for something concerning this.

Responder

This is a topic that is close to my heart… Best wishes! Exactly where are your contact details though?

Responder

This site definitely has all the information I needed about this subject and didn’t know who to ask.

Responder

Achieving your fitness goals doesn’t have to require a certified personal trainer or an expensive gym memberships, it’s not hard to exercise at home. It’s easy to go down a training and fitness rabbit hole, however, when you’re looking for the best home exercise equipment to outfit your personal home gym.

Responder

I know this web page provides quality depending
posts and extra material, is there any other website which gives these stuff in quality?

Responder

Very descriptive article, I enjoyed that bit. Will there be a part
2?

Responder

Hi, yes this article is truly pleasant and I have learned lot of things from it concerning blogging.
thanks. cheap flights 3gqLYTc

Responder

Can I just say what a comfort to uncover an individual who truly knows what they are discussing on the internet. You certainly know how to bring an issue to light and make it important. More and more people really need to read this and understand this side of the story. It’s surprising you’re not more popular because you definitely have the gift.

Responder

I was very happy to discover this great site. I wanted to thank you for ones time due to this wonderful read!! I definitely really liked every little bit of it and i also have you saved as a favorite to check out new things in your web site.

Responder

You’re so cool! I don’t believe I’ve truly read a single thing like this before. So wonderful to find another person with some genuine thoughts on this subject. Seriously.. thanks for starting this up. This site is one thing that’s needed on the web, someone with some originality!

Responder

It’s difficult to find well-informed people about this topic, but you seem like you know what you’re talking about! Thanks

Responder

Wonderful post! We are linking to this particularly great post on our website. Keep up the good writing.

Responder

Spot on with this write-up, I honestly believe this website needs much more attention. I’ll probably be returning to read through more, thanks for the advice!

Responder

That is a great tip especially to those new to the blogosphere. Brief but very accurate information… Thank you for sharing this one. A must read article!

Responder

I am extremely inspired along with your writing talents and also with the layout for your weblog.
Is this a paid topic or did you modify it yourself?
Anyway keep up the nice high quality writing, it is rare
to peer a great blog like this one nowadays..

Responder

Just desire to say your article is as astounding. The clarity in your post is simply spectacular and
i can assume you are an expert on this subject. Well with your
permission let me to grab your RSS feed to keep updated with forthcoming
post. Thanks a million and please carry on the
gratifying work.

Responder

I want to to thank you for this fantastic read!! I absolutely enjoyed every bit of it. I’ve got you bookmarked to check out new things you post…

Responder

What i do not realize is if truth be told how you’re no
longer really a lot more well-preferred than you might be right
now. You are so intelligent. You understand thus considerably in relation to this matter, produced me personally
consider it from so many numerous angles. Its like women and men don’t seem to be fascinated unless
it is one thing to do with Lady gaga! Your own stuffs nice.
All the time deal with it up!

Responder

Hello, i think that i saw you visited my website so i came to “return the favor”.I’m attempting to
find things to enhance my site!I suppose its ok to use
some of your ideas!!

Responder

Hello! I could have sworn I’ve visited this web site before but after looking at a few of the posts I realized it’s new to me. Nonetheless, I’m definitely delighted I came across it and I’ll be book-marking it and checking back frequently!

Responder

I really love your site.. Great colors & theme. Did you create this amazing site yourself? Please reply back as I’m hoping to create my very own site and want to learn where you got this from or what the theme is named. Many thanks!

Responder

Howdy! This article could not be written much better! Looking at this article reminds me of my previous roommate! He constantly kept preaching about this. I’ll forward this post to him. Pretty sure he will have a great read. I appreciate you for sharing!

Responder

Everything is very open with a clear description of the challenges. It was really informative. Your website is useful. Thanks for sharing!

Responder

I do trust all the ideas you’ve introduced for your post.
They’re very convincing and can certainly work. Nonetheless,
the posts are very brief for starters. May just you please lengthen them a little from subsequent
time? Thanks for the post.

Responder

Hi, I do think this is an excellent website. I stumbledupon it 😉 I am going to revisit once again since I book-marked it. Money and freedom is the best way to change, may you be rich and continue to guide others.

Responder

After looking at a handful of the blog posts on your blog, I seriously appreciate your technique of writing a blog. I book-marked it to my bookmark webpage list and will be checking back in the near future. Please visit my web site too and tell me your opinion.

Responder

This is a topic that’s near to my heart… Thank you! Exactly where are your contact details though?

Responder

An intriguing discussion is definitely worth comment. I think that you need to publish more on this subject matter, it might not be a taboo matter but typically people do not discuss such topics. To the next! Best wishes!!

Responder

Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I will come back yet again since I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to guide other people.

Responder

I want to to thank you for this fantastic read!! I certainly enjoyed every bit of it. I have got you book-marked to check out new stuff you post…

Responder

Aw, this was an extremely nice post. Finding the time and actual effort to generate a very good article… but what can I say… I hesitate a whole lot and don’t manage to get anything done.

Responder

Your style is so unique compared to other people I have read stuff from. Thank you for posting when you have the opportunity, Guess I will just bookmark this web site.

Responder

There’s definately a lot to find out about this subject. I love all of the points you made.

Responder

Wonderful article! We will be linking to this particularly great article on our site. Keep up the good writing.

Responder

An outstanding share! I have just forwarded this onto a friend who has been doing a little homework on this. And he in fact bought me dinner because I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending the time to talk about this matter here on your web site.

Responder

I would like to thank you for the efforts you’ve put in writing this website. I really hope to see the same high-grade blog posts from you in the future as well. In fact, your creative writing abilities has motivated me to get my own website now 😉

Responder

Spot on with this write-up, I honestly feel this website needs much more attention. I’ll probably be returning to see more, thanks for the information!

Responder

I could not resist commenting. Perfectly written!

Responder

I would like to thank you for the efforts you have put in writing this blog. I am hoping to check out the same high-grade content from you in the future as well. In truth, your creative writing abilities has inspired me to get my own, personal website now 😉

Responder

You are so interesting! I do not think I have read through anything like that before. So wonderful to discover someone with a few genuine thoughts on this topic. Really.. many thanks for starting this up. This website is something that is required on the internet, someone with some originality!

Responder

Everything is very open with a really clear description of the issues. It was really informative. Your website is very helpful. Thanks for sharing!

Responder

I really love your website.. Great colors & theme. Did you make this website yourself? Please reply back as I’m trying to create my very own blog and would love to find out where you got this from or just what the theme is named. Thanks!

Responder

I really like reading an article that can make people think. Also, thank you for allowing me to comment!

Responder

I’m impressed, I have to admit. Rarely do I come across a blog that’s both educative and engaging, and without a doubt, you have hit the nail on the head. The problem is something that too few folks are speaking intelligently about. I’m very happy I found this during my hunt for something relating to this.

Responder

I would like to thank you for the efforts you have put in penning this site. I’m hoping to see the same high-grade blog posts from you in the future as well. In fact, your creative writing abilities has motivated me to get my very own website now 😉

Responder

Excellent blog post. I definitely love this website. Stick with it!

Responder

Can I simply say what a relief to discover somebody that really knows what they are discussing on the net. You actually know how to bring a problem to light and make it important. More and more people ought to check this out and understand this side of the story. It’s surprising you’re not more popular given that you most certainly possess the gift.

Responder

This excellent website really has all the info I wanted about this subject and didn’t know who to ask.

Responder

This is a topic that is near to my heart… Thank you! Exactly where are your contact details though?

Responder

When I originally left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and from now on every time a comment is added I get 4 emails with the same comment. Perhaps there is a means you can remove me from that service? Cheers!

Responder

This is the right webpage for anyone who hopes to find out about this topic. You understand so much its almost tough to argue with you (not that I personally will need to…HaHa). You definitely put a brand new spin on a topic that has been discussed for ages. Excellent stuff, just great!

Responder

I love it when people get together and share opinions. Great blog, stick with it!

Responder

Spot on with this write-up, I absolutely believe this web site needs much more attention. I’ll probably be back again to see more, thanks for the info!

Responder

btp [url=https://freecasinoww.com/#]free casino games[/url]

Responder

This page definitely has all of the information and facts I wanted concerning this subject and didn’t know who to ask.

Responder

Excellent write-up. I absolutely love this website. Keep it up!

Responder

This website was… how do you say it? Relevant!! Finally I’ve found something which helped me. Cheers!

Responder

I want to to thank you for this excellent read!! I definitely loved every bit of it. I have you book marked to check out new things you post…

Responder

Right here is the right web site for everyone who hopes to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want to…HaHa). You definitely put a brand new spin on a subject that has been written about for ages. Excellent stuff, just wonderful!

Responder

I could not resist commenting. Well written!

Responder

I was pretty pleased to uncover this web site. I need to to thank you for your time due to this wonderful read!! I definitely liked every little bit of it and i also have you book marked to see new information on your site.

Responder

This web site truly has all the information I wanted concerning this subject and didn’t know who to ask.

Responder

Very good article! We are linking to this great article on our site. Keep up the great writing.

Responder

Nice post. I learn something totally new and challenging on websites I stumbleupon on a daily basis. It will always be exciting to read content from other authors and practice something from their sites.

Responder

Hi, I do think this is an excellent web site. I stumbledupon it 😉 I’m going to revisit once again since i have bookmarked it. Money and freedom is the best way to change, may you be rich and continue to help other people.

Responder

Excellent post. I absolutely appreciate this site. Thanks!

Responder

When I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now whenever a comment is added I recieve 4 emails with the same comment. There has to be a means you are able to remove me from that service? Appreciate it!

Responder

Pretty! This has been an incredibly wonderful post. Thanks for providing this information.

Responder

An outstanding share! I have just forwarded this onto a co-worker who had been conducting a little homework on this. And he actually bought me lunch due to the fact that I found it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanx for spending the time to discuss this issue here on your website.

Responder

Hello there, I do think your blog may be having internet browser compatibility issues. Whenever I look at your website in Safari, it looks fine however, when opening in Internet Explorer, it has some overlapping issues. I merely wanted to give you a quick heads up! Other than that, excellent website!

Responder

Excellent site you have here.. It’s difficult to find good quality writing like yours these days. I really appreciate individuals like you! Take care!!

Responder

This is the right blog for anybody who really wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want to…HaHa). You definitely put a fresh spin on a subject that’s been written about for decades. Great stuff, just wonderful!

Responder