Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Conectar Excel con Excel Consulta SQL Rango Fechas con Datos Otro Libro


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

Buscar datos en un rango de fechas con macro, algo que es fundamental para gente que trabaja arduamente con libros de Excel registrando todo tipo de datos cuyo elemento común en la mayoría es la fecha del registro.

Este ejemplo precisamente muestra lo mencionado, es decir Como Filtrar por un Rango de Fechas Estando la Base de Datos en Otro Libro de Excel distinto al cual estamos trabajando, utilizando una combinación de código VBA y SQL, suena difícil, pero no lo es tanto solo de debe poner atención a las explicaciones de este vídeo como el resto de la saga dedicado al tema, en el link encuentras el resto de vídeos relacionados con Conectar Excel con Excel Sentencias SQL anteriormente se vio el mismo ejemplo pero la base de datos se encontraba en el mismo libro de Excel sobre el que se encontraba la macro.

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.

 

Descarga el ejemplo desde el link del final como primer medida para poder entender en forma simple y rápida la codificación contenida, que es explicada en este post y en el vídeo tutorial de nuestro canal de You Tube.

Es necesario para que la macro funcione, en primer lugar que se conecte al Libro Excel que contiene los datos a filtrar, esto se realiza con el siguiente código:

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

Posteriormente se debe crear la SQL para que consulte los datos en un rango de fecha, se debe establecer la fecha de inicio y de final para el filtrado de datos, en este ejemplo la fecha inicial y final de búsqueda se agregan en las celdas I1 y K1, el códigos es el siguiente:

sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Fecha >= #» & Format(CDate(a.Range(«I1»)), «mm/dd/yyyy») & «# AND Fecha <= #» & Format(CDate(a.Range(«K1»)), «mm/dd/yyyy») & «# ORDER BY fecha ASC»

El código anterior se puede leer como: Seleccione todas las columnas de la tabla Hoja1, cuando se cumpla la siguiente condición: que la fecha que está en la columna fecha sea mayor e igual a la fecha inserta en la celda I1, es decir para que el registro se filtre debe la fecha registrada debe ser mayor e igual a la fecha inicial; la otra condición es que la fecha registrada en la columna fecha debe ser menor o igual a la fecha final, en este ejemplo está en la celda K1.

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

Quizá sea de utilidad también

Como crear un menú que se desplaza a medida que se hace scroll

Como eliminar el botón X o cerrar de un formulario

Como copiar varias tablas vinculadas de Excel a Word

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

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

En forma posterior se ejecuta la SQL, los datos filtrados o Recorset se mantendrán en memoria hasta tanto sean usados y la memoria no se haya liberado, se usa el siguiente código:

Set rs = cn.Execute(sql)

Para pegar los datos filtrados en la hoja de Excel se usa:

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

En este ejemplo los datos se pegan a partir de la celda A2.

Descarga el ejemplo como filtrar entre fecha y fecha con datos contenidos en otro libro utilizando SQL y VBA, lo podrás hacer desde el link del final, a continuación la codificación completa del ejemplo.

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;»»»
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Fecha >= #» & Format(CDate(a.Range(«I1»)), «mm/dd/yyyy») & «# AND Fecha <= #» & Format(CDate(a.Range(«K1»)), «mm/dd/yyyy») & «# ORDER BY fecha ASC»

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

Sub BORRAR()
Sheets(«Hoja2»).Cells.Clear
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.
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 (67)

I’m the co-founder of JustCBD Store brand (justcbdstore.com) and am aiming to develop my wholesale side of business. I am hoping anybody at targetdomain is able to provide some guidance . I considered that the best way to do this would be to talk to vape shops and cbd retailers. I was hoping if anyone could suggest a dependable website where I can purchase Vape Shop Business Data I am presently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not sure which one would be the very best solution and would appreciate any advice on this. Or would it be simpler for me to scrape my own leads? Ideas?

Responder

An outstanding share! I have just forwarded this onto a colleague who had been doing a little research on this. And he actually bought me dinner due to the fact that I discovered it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending time to discuss this topic here on your web site.

Responder

Right here is the perfect website for anybody who wants to find out about this topic. You know so much its almost hard to argue with you (not that I personally will need to…HaHa). You definitely put a brand new spin on a subject that has been written about for years. Great stuff, just excellent!

Responder

You made some decent points there. I looked on the internet for more info about the issue and found most people will go along with your views on this web site.

Responder

I just want to mention I am beginner to blogs and truly savored you’re page. Most likely I’m likely to bookmark your site . You really have awesome stories. Regards for sharing with us your website.

Responder

I simply want to mention I am new to blogging and absolutely liked this blog. Very likely I’m going to bookmark your blog . You amazingly come with outstanding articles. Thanks a bunch for sharing your blog.

Responder

Youre so cool! I don’t mean Ive review anything similar to this before. So great to find someone with some original thoughts on this subject. realy thanks for starting this up. this website is something that is required online, somebody with a little originality. helpful work for bringing something new to the web!

Responder

I?d need to get in touch with you below. Which is not something I typically do! I appreciate checking out a blog post that will certainly make individuals think. Also, thanks for permitting me to comment!

Responder

pitkävetovihjeet

Responder

Shop; Unique Functions. Simple Product Layouts. Post with review. Advanced Product Layout. Best conversion pages. Frontend Search · Listing example …

Responder

You made some decent points there. I looked on the net for the problem and found most individuals will accompany with your web site.

Responder

Hello there I am so delighted I found your web site, I really found you by accident, while I was researching on Digg for something else, Anyways I am here now and would just like to say cheers for a tremendous post and a all round thrilling blog (I also love the theme/design), I don’t have time to read it all at the minute but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the awesome b.

Responder

you have an excellent blog site here! would certainly you like to make some welcome blog posts on my blog?

Responder

What are some remedies for opiate withdrawal

Responder

Greetings from Ohio! I’m bored to death at work so I decided to browse your site on my iphone during lunch break. I enjoy the information 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 .. Anyways, good blog!|

Responder

Love watching sunset !

Responder

Love watching sunset !

Responder

Love watching sunset !

Responder

Best view i have ever seen !

Responder

Love watching sunset !

Responder

ilmaiset videot, ilmaiset pornovideot, ilmaiset porno videot, porno video, porno videot

Responder

Body Surface Temperature Screening

Responder

You could definitely see your skills in the work you write. The world hopes for even more passionate writers like you who are not afraid to say how they believe. Always follow your heart.

Responder

cbd oil

Responder

We are building consultants. We perform roof assessments, window testing, and wall testing for quality assurance on new and existing buildings.

Responder

very nice message, i certainly like this web site, keep it

Responder

Online jewelry boutique specializing in custom gold and silver pieces. For the hippy hearted fashion lover. Known for its custom lotus flower hoop earrings and large collection of hoop earrings. Customers enjoy the classic and unique versatile everyday jewelry designs. Glamour hippy began is also known for its dainty gold jewelry pieces. Bohemian chic styles with modern fashion flare. Great prices and affordable. Everyday pieces to statement piece designs for special occasions. Glamour hippy is inspired by diverse world culture and it reflects into the ever changing designs. Its customer enjoys unique pieces and comfort in style. Glamour hippy wants to be a part of its customers wardrobe for many years and part of great memories where ever the customer takes their jewelry pieces. Glamour hippy is glamour and hippy all in one. Shiny pretty pieces, Unique, Glamorous, boho chic, one of a kind, dainty, but statement worthy.

Responder

netticasinot, netticasino, casino, kasinot, nettkasinot

Responder

You must participate in a contest for among the best blog sites on the internet. I will recommend this site!

Responder

Great work! This is the type of info that should be shared around the web. Shame on Google for not positioning this post higher! Come on over and visit my web site . Thanks =)

Responder

It is actually a great and helpful piece of info. I’m satisfied that you shared this helpful information with us. Please keep us informed like this. Thank you for sharing.

Responder

Heya i am for the first time here. I came across this board and I find It truly useful & it helped me out a lot. I hope to give something back and aid others like you helped me.

Responder

There is visibly a bundle to learn about this. I think you made sure great points in functions also.

Responder

Usually I do not learn post on blogs, however I would like to say that this write-up very compelled me to check out and do so! Your writing taste has been amazed me. Thanks, quite nice post.

Responder

I am continuously searching online for posts that can aid me. Thank you!

Responder

Aw, this was a truly great post. In suggestion I would love to place in writing such as this additionally? requiring time and actual effort to make a great article? yet what can I state? I postpone alot and never appear to get something done.

Responder

bonukset

Responder

Hi, Neat post. There is a problem along with your site in web explorer, might check this¡K IE nonetheless is the marketplace leader and a large section of other people will pass over your excellent writing due to this problem.

Responder

A powerful share, I simply given this onto a colleague who was doing a little evaluation on this. And he in fact bought me breakfast because I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to debate this, I feel strongly about it and love studying extra on this topic. If attainable, as you grow to be experience, would you mind updating your weblog with extra particulars? It’s extremely useful for me. Huge thumb up for this weblog put up!

Responder

My spouse and i ended up being very contented that Michael could finish up his investigations via the ideas he came across from your own web pages. It is now and again perplexing to just always be giving out tactics which the rest have been making money from. So we consider we need the blog owner to give thanks to for that. The illustrations you’ve made, the easy web site menu, the relationships you make it possible to promote – it is everything unbelievable, and it’s facilitating our son in addition to us reason why that content is thrilling, which is extraordinarily pressing. Thanks for everything!

Responder

Cool blog! Is your theme custom made or did you download it from somewhere? A theme like yours with a few simple adjustements would really make my blog jump out. Please let me know where you got your design. With thanks

Responder

Very interesting information!Perfect just what I was looking for!

Responder

Wow, awesome blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is excellent, as well as the content!

Responder

Your area is valueble for me. Thanks !?

Responder

will someone write my essay for me https://essayonlinehdb.com/ – write my essay the essay writer essay writer software who can i pay to write my essay

Responder

hire essay writer https://essaywriterdodo.com/ – my essay writer expert essay writers good essay writers how to be a good essay writer

Responder

Greetings from California! I’m bored to death at work so I decided to browse your website on my iphone during lunch break. I enjoy the info you provide here and can’t wait to take a look when I get home. I’m amazed at how fast your blog loaded on my phone .. I’m not even using WIFI, just 3G .. Anyways, very good site!

Responder

university essay help https://essayhelptopp.com/ – common application essay help homework essay help persuasive essay help easy essay help

Responder

You need to participate in a competition for among the best blogs on the internet. I will certainly recommend this website!

Responder

I enjoy reading through a post that can make men and women think. Also, thanks for allowing for me to comment!

Responder

payday loans online no credit check https://sapaydayiloans.com/ – online payday loans no credit check online payday loans no credit check payday loans online bad credit best payday loans

Responder

do my coursework online https://courseworkhelpvra.com/ – coursework resources coursework psychology coursework website coursework writers

Responder

online payday loans for bad credit https://ausapaydayloans.com/ – payday installment loans usa payday loans payday loans online direct lenders only 1 hour payday loans

Responder

essay customer service https://essaywritingservicenews.com/ – essay correction service do essay writing services work admission essay service essay writing services singapore

Responder

paper help writing https://papersonlinesfy.com/ – buy cheap papers need help writing a paper help writing papers for college paper writing help online

Responder

dissertation help service https://dissertationaas.com/ – dissertation binding best dissertation services doctoral dissertation writing dissertation binding

Responder

Good post. I learn something new and challenging on websites I stumbleupon everyday. It’s always exciting to read articles from other writers and use a little something from other sites.

Responder

Keep up the excellent work , I read few blog posts on this web site and I think that your website is real interesting and has got circles of good info .

Responder

I?d need to consult you here. Which is not something I generally do! I enjoy checking out a message that will certainly make individuals assume. Likewise, thanks for permitting me to comment!

Responder

generic tadalafil http://50cialmen.com/ – cialis generic tadalafil citrate cialis 20mg generic tadalafil

Responder

cialis no prescription http://aazmencial.com/ – cialis online no prescription buy cialis without prescription tadalafil reviews what is tadalafil

Responder

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

Responder

An outstanding share! I have just forwarded this onto a coworker who was doing a little homework on this. And he in fact ordered me breakfast simply because I discovered 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 website.

Responder

I have to thank you for the efforts you’ve put in writing this site. I’m hoping to check out the same high-grade blog posts from you in the future as well. In fact, your creative writing abilities has encouraged me to get my own site now 😉

Responder

viagra sample http://60lmentop.com/ – generic viagra cheap order viagra online viagra dose free viagra sample

Responder

Somebody essentially lend a hand to make critically articles I would state. This is the very first time I frequented your website page and to this point? I surprised with the analysis you made to create this actual publish incredible. Great job!

Responder

I do believe all the concepts you’ve offered to your post. They’re really convincing and will definitely work. Nonetheless, the posts are too brief for newbies. Could you please lengthen them a little from subsequent time? Thank you for the post.

Responder