Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

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


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

El ejemplo que se presenta a continuación permite Filtrar Datos de Excel en Base a Criterio donde la Base de Datos está en Otro Libro distinto en el cual estamos trabajando, la búsqueda y extracción de datos se realiza utilizando una conexión ADODB – OLEDB, combinando VBA y SQL, es decir se filtran datos mediante sentencias SQL.

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

   

Para una mejor compresión debes descargar y descomprimir el archivo rar de ejemplo, al descomprimir puedes guardar en cualquier directorio de la PC, la única condición es que estén los dos archivos en el mismo directorio, porque el archivo con la macro buscará los datos en el libro que contiene la base de datos.

Presionando el botón se ejecuta una macro que conecta el libro de Excel que contiene la macro de Excel con el libro que contiene la base de datos, lograda esa conexión a través de una sentencia SQL se logra filtrar los datos, teniendo presente que no se abre el libro de Excel con la base de datos, los datos pueden ser obtenidos por la conexión establecida, dicha conexión se realiza con el siguiente código, en la variable mybook se guarda la dirección del archivo con la base de datos.

mybook = ThisWorkbook.Path & «414 Conectar Excel con Excel Consulta SQL Base Datos.xlsx»

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


Realizada la conexión se debe realizar la consulta SQL o string de consulta que también se le denomina de la siguiente forma:

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

En el caso anterior la SQL filtra todos los registros de la hoja1 con la condición que el campo «Marca» sea igual al valor ingresado en la cela H2, no solo filtra los valores que coinciden plenamente no lo ingresado sino todos aquellos registros que contengan en la cadena de texto del registro «Marca» el texto ingresado en H2, ejemplo si se ingresa la marca ARCOR, buscará en la columna «Marca» todo lo que contenga la cadena de caracteres ARCOR, es decir buscará por ejemplo ARCOR, ARCOR GALLETAS, etc.

La siguiente SQL se usa cuando se tilde el casillero del checkbox, donde se expresa que se requiere solo se filtren la coincidencias exactas, extrayendo los datos que coinciden exactamente con la cadena de texto ingresada en H2, es decir solo extraerá la coincidencia iguales a la cadena ingresada.

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

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

Quizá sea de utilidad también

Como buscar archivos en una carpeta y hacer link al fichero

Como truncar o cortar el nombre de un archivo a una cantidad especifica de caracteres

Como pasar datos de un listbox a otro con Enter

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

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

Para ejecutar la SQL se usa:

Set rs = cn.Execute(sql)

Para grabar los datos obtenidos al ejecutar la SQL, que están contenidos en memoria, se usa el siguiente código:

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

El ejemplo de macro que permite filtrar por un criterio con SQL estando la base de datos en otro libro, se puede descargar desde el final del post, a continuación se expone el código completo que contiene el ejemplo que se descarga.

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»)

mybook = ThisWorkbook.Path & «414 Conectar Excel con Excel Consulta SQL Base Datos.xlsx»
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & mybook & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»

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 (68)

I am the business owner of JustCBD brand (justcbdstore.com) and I’m presently aiming to broaden my wholesale side of business. I am hoping anybody at targetdomain can help me ! I considered that the most effective way to accomplish this would be to talk to vape companies and cbd retailers. I was really hoping if someone could suggest a dependable web site where I can purchase Vape Shop B2B Sales Leads I am currently looking at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the most ideal solution and would appreciate any assistance on this. Or would it be easier for me to scrape my own leads? Ideas?

Responder

This blog was… how do you say it? Relevant!! Finally I’ve found something which helped me. Thanks a lot!

Responder

This site was… how do I say it? Relevant!! Finally I have found something which helped me. Appreciate it!

Responder

Way cool! Some very valid points! I appreciate you penning this post and also the rest of the website is also very good.

Responder

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

Responder

Your style is very 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

The next time I read a blog, Hopefully it does not fail me as much as this one. I mean, Yes, it was my choice to read, but I truly believed you would have something interesting to say. All I hear is a bunch of complaining about something you could possibly fix if you were not too busy looking for attention.

Responder

After looking at a number of the blog articles on your blog, I truly like your technique of writing a blog. I bookmarked it to my bookmark site list and will be checking back in the near future. Please check out my website as well and let me know what you think.

Responder

Achieving your fitness goals does not need a certified personal trainer or an expensive gym membership, 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

An impressive share! I’ve just forwarded this onto a co-worker who had been conducting a little research on this. And he actually ordered 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, thanks for spending the time to talk about this subject here on your web site.

Responder

I enjoy looking through an article that will make men and women think. Also, thank you for permitting me to comment!

Responder

Hello, I think your blog may be having internet browser compatibility problems. When I take a look at your website in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping issues. I just wanted to give you a quick heads up! Besides that, wonderful blog!

Responder

Howdy! This blog post couldn’t be written much better! Looking through this article reminds me of my previous roommate! He always kept talking about this. I will send this article to him. Pretty sure he’ll have a great read. I appreciate you for sharing!

Responder

Hi, I do believe this is a great blog. I stumbledupon it 😉 I am going to come back once again since i have book-marked it. Money and freedom is the best way to change, may you be rich and continue to help others.

Responder

There is definately a great deal to know about this issue. I love all of the points you have made.

Responder

There is definately a great deal to know about this subject. I like all of the points you’ve made.

Responder

An impressive share! I have just forwarded this onto a co-worker who has been doing a little homework on this. And he actually bought me dinner 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 issue here on your blog.

Responder

I like it when individuals get together and share opinions. Great site, continue the good work!

Responder

bookmarked!!, I like your website!

Responder

After I originally commented 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 receive four emails with the same comment. Perhaps there is a means you are able to remove me from that service? Cheers!

Responder

You are so interesting! I do not believe I’ve truly read a single thing like that before. So wonderful to find someone with original thoughts on this topic. Really.. thank you for starting this up. This web site is one thing that is required on the internet, someone with a little originality!

Responder

Good info. Lucky me I found your blog by chance (stumbleupon). I’ve book-marked it for later!

Responder

Greetings! Very useful advice in this particular article! It is the little changes which will make the most important changes. Thanks a lot for sharing!

Responder

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

Responder

After I originally commented I appear to have clicked the -Notify me when new comments are added- checkbox and now each time a comment is added I get 4 emails with the same comment. There has to be a means you can remove me from that service? Thank you!

Responder

This website was… how do you say it? Relevant!! Finally I have found something that helped me. Kudos!

Responder

Hello there! I could have sworn I’ve visited this web site before but after looking at some of the articles 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

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

Responder

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

Responder

I could not refrain from commenting. Well written!

Responder

Pretty! This was an incredibly wonderful article. Thank you for supplying this info.

Responder

Pretty! This was a really wonderful article. Many thanks for providing this info.

Responder

I’m really enjoying the theme/design of your web site. Do you ever run into any web browser compatibility issues? A number of my blog readers have complained about my site not working correctly in Explorer but looks great in Chrome. Do you have any advice to help fix this issue?

Responder

I needed to thank you for this wonderful read!! I absolutely loved every bit of it. I have you saved as a favorite to check out new things you post…

Responder

Oh my goodness! Awesome article dude! Thanks, However I am experiencing difficulties with your RSS. I don’t know the reason why I can’t subscribe to it. Is there anybody getting identical RSS issues? Anyone who knows the answer can you kindly respond? Thanks!!

Responder

Hey very nice site!! Man .. Beautiful .. Amazing .. I’ll bookmark your website and take the feeds alsoÖI am happy to find so many useful information here in the post, we need work out more techniques in this regard, thanks for sharing. . . . . .

Responder

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

Responder

Way cool! Some extremely valid points! I appreciate you penning this post and also the rest of the site is also really good.

Responder

I blog frequently and I really thank you for your content. This article has really peaked my interest. I’m going to bookmark your blog and keep checking for new information about once a week. I subscribed to your RSS feed as well.

Responder

You’ve made some really good points there. I looked on the web for additional information about the issue and found most individuals will go along with your views on this web site.

Responder

Way cool! Some very valid points! I appreciate you penning this post plus the rest of the site is extremely good.

Responder

I appreciate, cause I found exactly what I was looking for. You have ended my 4 day long hunt! God Bless you man. Have a great day. Bye

Responder

I have to thank you for the efforts you have put in writing this website. I really hope to view the same high-grade content by you later on as well. In truth, your creative writing abilities has motivated me to get my own site now 😉

Responder

You’re so awesome! I don’t believe I’ve truly read through something like this before. So good to find someone with a few unique thoughts on this subject matter. Really.. thank you for starting this up. This site is one thing that is required on the internet, someone with a bit of originality!

Responder

Oh my goodness! Incredible article dude! Thanks, However I am having difficulties with your RSS. I don’t understand why I can’t subscribe to it. Is there anybody else having similar RSS problems? Anyone who knows the answer will you kindly respond? Thanx!!

Responder

Aw, this was an exceptionally nice post. Taking a few minutes and actual effort to produce a superb article… but what can I say… I procrastinate a whole lot and never seem to get nearly anything done.

Responder

I really like looking through a post that will make men and women think. Also, thank you for allowing me to comment!

Responder

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

Responder

You’re so awesome! I don’t suppose I have read through anything like that before. So good to find someone with some original thoughts on this subject matter. Seriously.. thanks for starting this up. This web site is one thing that’s needed on the internet, someone with some originality!

Responder

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

Responder

The next time I read a blog, Hopefully it does not disappoint me as much as this one. I mean, I know it was my choice to read, nonetheless I genuinely believed you would probably have something helpful to say. All I hear is a bunch of complaining about something you could possibly fix if you weren’t too busy looking for attention.

Responder

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

Responder

I would like to thank you for the efforts you’ve put in writing this blog. I am hoping to view the same high-grade content by you in the future as well. In truth, your creative writing abilities has inspired me to get my own blog now 😉

Responder

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

Responder

I love reading through an article that will make men and women think. Also, thanks for allowing for me to comment!

Responder

I’m very pleased to find this page. I wanted to thank you for ones time for this particularly fantastic read!! I definitely savored every little bit of it and I have you book-marked to check out new things in your site.

Responder

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

Responder

There’s certainly a great deal to find out about this issue. I love all the points you’ve made.

Responder

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

Responder

Oh my goodness! Awesome article dude! Thanks, However I am encountering difficulties with your RSS. I don’t understand why I cannot subscribe to it. Is there anybody getting identical RSS issues? Anyone who knows the answer can you kindly respond? Thanx!!

Responder

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

Responder

Oh my goodness! Impressive article dude! Many thanks, However I am having troubles with your RSS. I don’t understand why I am unable to join it. Is there anybody getting identical RSS problems? Anybody who knows the solution will you kindly respond? Thanks!!

Responder

An impressive share! I have just forwarded this onto a coworker who had been conducting a little research on this. And he actually ordered me dinner simply 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 discuss this topic here on your internet site.

Responder

Excellent site you have got here.. It’s difficult to find excellent writing like yours these days. I honestly appreciate people like you! Take care!!

Responder

Greetings I am so thrilled I found your webpage, I really found you by accident, while I was
researching on Digg for something else, Anyhow I am here now and would just like to say
many thanks for a remarkable post and a all round entertaining blog (I also love the theme/design), I don’t have time to look
over it all at the minute but I have saved it and also added your RSS
feeds, so when I have time I will be back to read a great deal more,
Please do keep up the excellent job.

My homepage RoxanaUSimao

Responder

I truly love your blog.. Great colors & theme. Did you build this amazing site yourself? Please reply back as I’m trying to create my own site and want to find out where you got this from or exactly what the theme is called. Appreciate it!

Responder

This is a very good tip especially to those new to the blogosphere. Short but very accurate info… Appreciate your sharing this one. A must read post!

Responder

Can I just say what a relief to discover an individual who actually knows what they are discussing over the internet. You definitely know how to bring an issue to light and make it important. A lot more people need to check this out and understand this side of the story. I was surprised you are not more popular because you most certainly have the gift.

Responder