Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Conectar Excel con Excel Consulta SQL Un Criterio con Datos Mismo Libro


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

El ejemplo que se presenta es una macro que muestra como conectar Excel con Excel y Buscar Datos en Base a Criterio con SQL, estando los datos contenidos en el mismo libro, la consulta se realiza sin abrir el otro libro de Excel

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.

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);
}
}



 
Después de descargar el archivo podrás observar un botón, presionando el mismo se ejecuta la macro, la macro busca el dato de la celda H2 en la columna «Marca», cuyo nombre está en la celda H1, es variable así que se puede poner cualquier otro nombre de cabecera de columnas.

También su puede buscar una coincidencia exacta si se hace click en el checkbox o cualquier palabra que contenga la palabra escrita en la celda H2, para buscar los datos se combina VBA con SQL, para ello se debe realizar la conexión Excel con Excel con el siguiente código, aclarando que se usa Excel 365 / 2016.

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»

Luego se crea la SQL, que es el string de consulta, que luego se ejecuta para filtrar los datos dependiendo del criterio, es la siguiente dependiendo de si se requiere una coincidencia exacta o que la palabra contenga la cadena de texto de la celda H2:

Coincidencia no exacta es decir la palabra puede coexistir con otras cadenas de caracteres

sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(» & a.Range(«H1») & «) LIKE Ucase(‘%» & Range(«H2») & «%’) ORDER BY ID ASC»

Coincidencia exacta, solo filtra los registros que coincidan en forma exacta con lo escrito.

sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(» & a.Range(«H1») & «) LIKE Ucase(‘» & Range(«H2») & «‘) ORDER BY ID ASC»

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

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

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

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

La consulta de datos se realiza con el siguiente código, con este código se filtran los datos según el criterio y se mantienen en memoria, Recordset.

Set rs = cn.Execute(sql)

Para escribir o pegar los datos filtrados en el Hoja del Libro de Excel se usa el siguiente código, en este caso se pegan en la hoja2 a partir de la celda A2.

b.Cells(2, 1).CopyFromRecordset Data:=rs

Luego la macro determina si existen datos en dicho rango, en caso positivo se encontraron datos y sale un mensaje en caso que no se hayan encontrado datos, sale otro mensaje distinto, esto se hace con msgbox (si quieres saber más sobre msgbox sigue el link)

Para terminar se aconseja conectar con el libro ejecutar la sql, lo más cercano posible al momento de usar los datos y luego liberar las variables, por el simple hecho que consume recursos (Memoria de la PC), entonces se debe realizar la consulta en el preciso momento de usar los datos, usarlos y liberar las variables y cerrar la conexión así:

Set rs = Nothing
cn.Close
Set cn = Nothing

El código completo se encuentra a continuación y posteriormente está el link de descarga del ejemplo Conectar Excel con Excel Busqueda  en Base a Criterios con SQL con Dtos en el Mismo Libro.

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 a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja2»)

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»

‘uf = a.Range(«A» & Rows.Count).End(xlUp).Row
If a.CheckBox1 = False Then
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(» & a.Range(«H1») & «) LIKE Ucase(‘%» & Range(«H2») & «%’) ORDER BY ID ASC»
Else
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(» & a.Range(«H1») & «) LIKE Ucase(‘» & Range(«H2») & «‘) ORDER BY ID ASC»
‘sql = «SELECT * FROM [» & «Hoja1$A1:V65000» & «] WHERE Ucase(» & a.Range(«H1») & «) LIKE Ucase(‘» & Range(«H2») & «‘) ORDER BY ID ASC»
End If

b.Cells.Clear
a.Range(«A1:G1»).Copy Destination:=b.Range(«A1»)
Set rs = cn.Execute(sql)
b.Cells(2, 1).CopyFromRecordset Data:=rs
b.Range(«B:B»).NumberFormat = «dd/mm/yyyy»
Set rs = Nothing
cn.Close
Set cn = Nothing
If b.Range(«A2») <> Empty Then
MsgBox («La busqueda se realizó con éxito»), vbInformation, «AVISO»
Else
MsgBox («No se encontraron registros 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 un comentario

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

Comentarios (73)

I’m the business owner of JustCBD label (justcbdstore.com) and am looking to develop my wholesale side of business. It would be great if someone at targetdomain share some guidance 🙂 I considered that the most ideal way to accomplish this would be to reach out to vape stores and cbd retail stores. I was hoping if someone could recommend a reliable web site where I can purchase Vape Shop B2B Leads I am already looking at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not exactly sure which one would be the most suitable solution and would appreciate any guidance on this. Or would it be easier for me to scrape my own leads? Suggestions?

Responder

You’ve made some good points there. I checked on the net to learn more about the issue and found most people will go along with your views on this website.

Responder

I love reading a post that can make people think. Also, thank you for permitting me to comment!

Responder

I need to to thank you for this fantastic read!! I certainly enjoyed every little bit of it. I’ve got you saved as a favorite to check out new things you post…

Responder

Good day! I just want to offer you a huge thumbs up for your excellent info you have got right here on this post. I am returning to your site for more soon.

Responder

You made some decent points there. I checked on the net to find out more about the issue and found most people will go along with your views on this web site.

Responder

I was excited to uncover this website. I need to to thank you for ones time just for this wonderful read!! I definitely really liked every bit of it and i also have you book-marked to check out new information in your blog.

Responder

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

Responder

Hello, I do believe your web site may be having browser compatibility issues. When I look at your web site in Safari, it looks fine however when opening in Internet Explorer, it’s got some overlapping issues. I just wanted to give you a quick heads up! Aside from that, wonderful blog!

Responder

Hello there! This blog post couldn’t be written any better! Going through this post reminds me of my previous roommate! He constantly kept talking about this. I will send this article to him. Fairly certain he’s going to have a great read. I appreciate you for sharing!

Responder

Achieving your fitness goal does not have to require a certified personal trainer or an expensive gym memberships, especially if you have the budget and the space to consider practically every workout machine on the market.

Responder

Good site you have got here.. It’s difficult to find good quality writing like yours nowadays. I seriously appreciate individuals like you! Take care!!

Responder

An outstanding share! I have just forwarded this onto a coworker who had been conducting a little research on this. And he in fact ordered me breakfast due to the fact that I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending some time to discuss this topic here on your site.

Responder

I blog quite often and I seriously appreciate your content. This great article has truly peaked my interest. I will bookmark your blog and keep checking for new details about once a week. I subscribed to your Feed too.

Responder

I needed to thank you for this great read!! I definitely enjoyed every bit of it. I have got you book-marked to check out new things you post…

Responder

Saved as a favorite, I love your web site!

Responder

Nice post. I learn something new and challenging on sites I stumbleupon every day. It will always be interesting to read through articles from other writers and use a little something from other websites.

Responder

An outstanding share! I have just forwarded this onto a coworker who had been conducting a little research on this. And he in fact bought me dinner because I found it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending time to discuss this matter here on your web page.

Responder

You have made some really good points there. I looked on the net to find out more about the issue and found most individuals will go along with your views on this website.

Responder

We tested coffee makers for the water temperature during the brew cycle and the brewing time as well as the temperature of the coffee at the end of brewing and during the keep warm cycle. In addition, we evaluated each brewer’s ease of use, including how easy it was to read and use the controls, access the water tank and brew basket, read the markings on the tank and carafe, whether or not it had automatic keep warm and shut off cycles, and the ease of cleaning the carafe.

Responder

I needed to thank you for this good read!! I certainly loved every bit of it. I have got you book marked to check out new stuff you post…

Responder

Great article. I’m experiencing many of these issues as well..

Responder

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

Responder

I like it whenever people come together and share ideas. Great blog, stick with it!

Responder

I quite like reading a post that can make people think. Also, many thanks for permitting me to comment!

Responder

I was able to find good info from your blog articles.

Responder

Very good info. Lucky me I found your blog by chance (stumbleupon). I have book marked it for later!

Responder

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

Responder

Way cool! Some extremely valid points! I appreciate you writing this write-up and also the rest of the site is extremely good.

Responder

Hi! I simply would like to give you a huge thumbs up for your great information you’ve got here on this post. I will be coming back to your site for more soon.

Responder

An impressive share! I’ve just forwarded this onto a co-worker who was conducting a little research on this. And he actually bought me breakfast simply because I found it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending some time to talk about this topic here on your web site.

Responder

Pretty! This was a really wonderful post. Thank you for providing this information.

Responder

A motivating discussion is definitely worth comment. I do believe that you should publish more about this issue, it might not be a taboo subject but generally folks don’t talk about such topics. To the next! Kind regards!!

Responder

I do believe the admin with this web site is in fact working hard in favor
of his site, because here every information is quality based stuff.

My blog post: MasonQSoos

Responder

It’s nearly impossible to find educated people in this particular topic, but you sound like you know what you’re talking about! Thanks

Responder

Pretty! This was an incredibly wonderful post. Many thanks for providing these details.

Responder

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

Responder

The very next time I read a blog, I hope that it doesn’t disappoint me just as much as this one. I mean, I know it was my choice to read, but I actually thought you would have something helpful to say. All I hear is a bunch of crying about something that you could fix if you were not too busy seeking attention.

Responder

Greetings! Very helpful advice within this post! It’s the little changes which will make the most important changes. Many thanks for sharing!

Responder

Saved as a favorite, I really like your blog!

Responder

Greetings from Los angeles! I’m bored to death at work so I decided to check out your website on my iphone during lunch break. I enjoy the info you present here and can’t wait to take a look when I get home. I’m amazed at how quick your blog loaded on my cell phone .. I’m not even using WIFI, just 3G .. Anyhow, excellent site!

Responder

After looking into a few of the blog posts on your blog, I honestly appreciate your technique of blogging. I saved it to my bookmark website list and will be checking back soon. Please visit my website too and tell me how you feel.

Responder

I could not resist commenting. Exceptionally well written!

Responder

Great blog you’ve got here.. It’s hard to find excellent writing like yours these days. I really appreciate people like you! Take care!!

Responder

Hi there! I could have sworn I’ve been to this web site before but after looking at some of the articles I realized it’s new to me. Anyways, I’m certainly delighted I stumbled upon it and I’ll be bookmarking it and checking back frequently!

Responder

Your style is so unique compared to other folks I’ve read stuff from. Many thanks for posting when you’ve got the opportunity, Guess I will just bookmark this web site.

Responder

Spot on with this write-up, I seriously believe this website needs a great deal more attention. I’ll probably be back again to read more, thanks for the information!

Responder

You actually make it seem so easy together with your presentation however I find this topic to be really something that I think I would never understand. It sort of feels too complicated and extremely large for me. I am having a look ahead in your subsequent post, Iíll try to get the hold of it!

Responder

This excellent website definitely has all the information I needed concerning this subject and didn’t know who to ask.

Responder

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

Responder

Excellent web site you’ve got here.. It’s hard to find high-quality writing like yours nowadays. I really appreciate individuals like you! Take care!!

Responder

Your style is really unique in comparison to other folks I’ve read stuff from. Many thanks for posting when you’ve got the opportunity, Guess I’ll just bookmark this page.

Responder

Having read this I believed it was rather informative. I appreciate you spending some time and effort to put this short article together. I once again find myself spending a significant amount of time both reading and commenting. But so what, it was still worthwhile!

Responder

I’m curious to learn what blog platform you’re working
with? I’m having some small security problems with my latest blog and I would love to find something more safe.
Have you got any recommendations?

my webpage – CaseyBEastin

Responder

A motivating discussion is definitely worth comment. I do believe that you should write more on this topic, it might not be a taboo subject but usually folks don’t talk about such topics. To the next! Many thanks!!

Responder

Hello! I could have sworn I’ve visited this blog before but after going through some of the posts I realized it’s new to me. Regardless, I’m definitely happy I came across it and I’ll be book-marking it and checking back regularly!

Responder

Very nice article. I definitely appreciate this site. Thanks!

Responder

Good blog you have here.. It’s hard to find high quality writing like yours nowadays. I really appreciate people like you! Take care!!

Responder

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

Responder

You need to take part in a contest for one of the most useful sites on the internet. I most certainly will recommend this site!

Responder

Next time I read a blog, I hope that it doesn’t disappoint me as much as this one. I mean, I know it was my choice to read through, however I actually believed you would have something useful to talk about. All I hear is a bunch of crying about something you could fix if you weren’t too busy seeking attention.

Responder

Greetings! Very helpful advice in this particular post! It’s the little changes that produce the greatest changes. Thanks a lot for sharing!

Responder

Can I simply just say what a relief to uncover someone that actually knows what they are discussing on the internet. You definitely understand how to bring a problem to light and make it important. More people have to check this out and understand this side of your story. I was surprised you are not more popular because you surely have the gift.

Responder

It’s hard to come by experienced people for this topic, however, you sound like you know what you’re talking about! Thanks

Responder

Pretty! This was an incredibly wonderful article. Thank you for providing this information.

Responder

You ought to be a part of a contest for one of the finest blogs on the internet. I’m going to highly recommend this website!

Responder

I really like it whenever people get together and share ideas. Great website, stick with it!

Responder

You are so interesting! I don’t think I have read a single thing like this before. So good to find somebody with a few genuine thoughts on this subject. Seriously.. thank you for starting this up. This web site is one thing that’s needed on the internet, someone with a little originality!

Responder

Oh my goodness! Impressive article dude! Thanks, However I am encountering problems with your RSS. I don’t know the reason why I am unable to subscribe to it. Is there anybody else having identical RSS issues? Anyone who knows the solution can you kindly respond? Thanx!!

Responder

When I originally commented I seem to have clicked the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I receive 4 emails with the exact same comment. Perhaps there is a way you are able to remove me from that service? Cheers!

Responder

After looking at a handful of the articles on your web page, I honestly appreciate your way of writing a blog. I book marked it to my bookmark site list and will be checking back soon. Please check out my website as well and let me know what you think.

Responder

Good day! I just would like to give you a huge thumbs up for your excellent information you’ve got here on this post. I am coming back to your site for more soon.

Responder

Next time I read a blog, Hopefully it does not disappoint me as much as this one. After all, Yes, it was my choice to read through, nonetheless I truly believed you’d have something helpful to talk about. All I hear is a bunch of crying about something you could possibly fix if you were not too busy searching for attention.

Responder