Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Conectar Excel con Excel Conectar Con Otro Libro Excel


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

Anteriormente vimos como conectar con el mismo libro de Excel, es decir la conexión se realizaba con mismo libro y se podía consultar datos de cualquier hoja de el mismo libro, en este ejemplo se muestra como conectar Excel con otro libro de Excel para poder extraer información.

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

 

Con Excel 2016 o 365 la conexión que se usa para poder vincular con otro libro de Excel es la siguiente:

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

En el post anterior se mostraron las distintas conexiones ADODB OLEDB que se usan con libros más viejos o mejor dicho realizado con versiones anteriores de 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

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

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

Se debe tener presente que al Conectar con otro libro de Excel, las hojas representan tablas de base de datos, es por ello que la primer fila se usa para nombrar a cada columna, necesaria para que las sentencias SQL hagan referencia a ellas, se debe fijar en HDR= yes si existe encabezado de columna y HDR=No si las columnas no tiene encabezado, en ese caso la hoja de Excel se tomará como una tabla de Base de Datos y cuyas columnas se denomnaran F1, F2, etc..

En la parte siguiente del código, cuando se requiere conectar con otro libro de Excel , es donde se debe poner la dirección del libro con el que se desea vincular:

Data Source=» & mybook 

Recordemos que cuando se vinculaba con el mismo libro Excel, la parte del código donde se escribe la dirección de la base de datos era:

Data Source=» & ThisWorkbook.FullName

Luego de vincular el libro se podrán usar las sentencias SQL para extraer información y los datos necearios de la base de datos o tablas que en este caso son cada una de las hojas de los Libros de Excel, en ejemplos siguientes veremos como crear sentencias SQL en Excel y como usarlas para extraer datos y sobre todo como usar los datos que se guardan en el recordset.

Código que se inserta en un módulo

Sub ConectarExcelconExcel()
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 & «Conectar Excel con Excel Consulta SQL Base Datos.xlsx»

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

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

‘Excel 2000 /XP 2003 / 2007
‘cn.Open «Provider=Microsoft.ACE.OLEDB.4.0;» & «Data Source=» & mybook & «;Extended Properties=»»Excel 8.0;HDR=Yes;»»»

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

MsgBox («La conexión se realió con exito»), vbInformation, «AVISO»
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 (110)

I’m the proprietor of JustCBD Store label (justcbdstore.com) and I’m presently trying to expand 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 shops and cbd stores. I was hoping if anybody at all could recommend a trustworthy web site where I can purchase CBD Shops B2B Data List I am presently considering creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence 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

You ought to take part in a contest for one of the highest quality blogs on the internet. I’m going to recommend this site!

Responder

Aw, this was a very good post. Taking the time and actual effort to generate a top notch article… but what can I say… I hesitate a lot and don’t seem to get nearly anything done.

Responder

Hi, I do think this is a great site. I stumbledupon it 😉 I will revisit once again since i have book marked it. Money and freedom is the greatest way to change, may you be rich and continue to help others.

Responder

Great article! We are linking to this particularly great content on our website. Keep up the great writing.

Responder

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

Responder

May I simply just say what a comfort to uncover a person that really understands what they’re talking about on the web. You certainly know how to bring an issue to light and make it important. More and more people ought to check this out and understand this side of the story. I was surprised you’re not more popular because you certainly have the gift.

Responder

Truly when someone doesn’t be aware of after that its up to other viewers that they will help, so here it happens.

Responder

What’s up, its nice post about media print, we all be aware of media is
a enormous source of information.

Responder

Hello! I know this is kinda off topic but I was wondering which blog platform are you using for this site?
I’m getting tired of WordPress because I’ve had issues
with hackers and I’m looking at alternatives for another platform.
I would be awesome if you could point me in the direction of a good
platform.

Responder

I just want to say I am all new to blogging and actually savored your page. Probably I’m planning to bookmark your blog . You certainly have perfect well written articles. Appreciate it for revealing your blog.

Responder

I just want to say I am just very new to blogging and certainly enjoyed your web site. Almost certainly I’m want to bookmark your blog . You certainly come with exceptional stories. Appreciate it for revealing your web-site.

Responder

Good reading, but I need to read 1 more time. Thanks! Will be good for this month SEO Service project.

Responder

An interesting conversation is worth comment. I think that you must create much more on this topic, it might not be a taboo subject however normally people are inadequate to talk on such topics. To the next. Thanks

Responder

Consider our Keto quiz to be able to configure your own personalised Keto meal plan. Consider the KETO quiz… Acquire everything you need to achieve success with the Keto diet.

Responder

pitkävetovihjeet

Responder

WanSheng Gaming is also the first company in Asia to develop and implemented a complete AI(Artifical Intelligence) system for all programs, WanSheng Gaming is a developer providing a variety of online gaming products such as Sports Betting, Virtual Game, Casino, Lottery, and many more. Beside, WanSheng Gaming also specializing in Sportsbook White Label Solution and Sportsbook API Service.

Responder

KAISERSLUST Plattform der Wiener Sex Scene Sex in Wien – Girls in Wien – Escorte Wien – Transen Wien – Hostessen Wien Escort Wien – Massagen Wien – Studios Wien – Laufhaus und Bordelle Wien Asiatinnen Wien – Sex in Wien – Kaiserslust – Der Kaiser hat Lust – Sex

Responder

It’s amazing to pay a quick visit this site and reading the views of all colleagues concerning this paragraph,
while I am also keen of getting know-how. 31muvXS cheap flights

Responder

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

Responder

Hello, constantly i used to check web site posts here in the early hours in the dawn,
because i enjoy to gain knowledge of more and more.
yynxznuh cheap flights

Responder

netticasinot pikakasinot parhaat netticasinot

Responder

I loved as much as you’ll receive carried
out right here. The sketch is tasteful, your authored subject matter stylish.
nonetheless, you command get got an edginess over
that you wish be delivering the following.

unwell unquestionably come more formerly again as exactly the
same nearly a lot often inside case you shield this hike.

Responder

I discovered your blog website on google as well as inspect a few of your very early articles. Remain to maintain the excellent operate. I just added up your RSS feed to my MSN Information Viewers. Looking for onward to learning more from you later!?

Responder

Welcome to iBeautys Best, iBeautys Best is the number one destination for Latest Beauty Products. Here you can easily buy your desired product from all over the world with very reasonable price.

Responder

Welcome to iBeautys Best, iBeautys Best is the number one destination for Latest Beauty Products. Here you can easily buy your desired product from all over the world with very reasonable price.

Responder

I am sure this paragraph has touched all the internet
viewers, its really really fastidious post on building
up new blog.

Responder

There is visibly a package to understand about this. I presume you ensured nice points in attributes additionally.

Responder

David-Home Northeast is your premier source for all of your Real Estate investing needs. We provide a variety of solutions for sellers looking to sell fast and investors looking to grow their real estate investment portfolios for better returns. At David-Home Northeast, we are dedicated to improving and redeveloping the areas that we operate in. We specialize in helping sellers of distressed properties (single and multi-family homes, commercial, retail, bank REOs, etc.) find buyers looking to invest. Let us put our network of cash home buyers and private money lenders to work for you so you can say “David” to that old unwanted property.

Responder

David-Home Northeast is your premier source for all of your Real Estate investing needs. We provide a variety of solutions for sellers looking to sell fast and investors looking to grow their real estate investment portfolios for better returns. At David-Home Northeast, we are dedicated to improving and redeveloping the areas that we operate in. We specialize in helping sellers of distressed properties (single and multi-family homes, commercial, retail, bank REOs, etc.) find buyers looking to invest. Let us put our network of cash home buyers and private money lenders to work for you so you can say “David” to that old unwanted property.

Responder

Oh my benefits! an impressive short article dude. Thanks Nonetheless I am experiencing problem with ur rss. Don?t know why Not able to subscribe to it. Is there anyone getting identical rss problem? Anybody that recognizes kindly react. Thnkx

Responder

I have fun with, cause I discovered exactly what I used to be taking a look for. You’ve ended my four day lengthy hunt! God Bless you man. Have a great day. Bye

Responder

Natural Releaf CBD | New York CBD Store with locations in Murray Hill, NY | Upper East Side, NY | White Plains, NY Serving New York City, NY and Westchester, NY High Quality CBD Products With Lab Test

Responder

parrilli emerging female abstract artist

Responder

Love watching sunset !

Responder

I loved as much as you’ll receive carried out right here. The sketch is attractive, your authored material stylish. nonetheless, you command get bought an edginess over that you wish be delivering the following. unwell unquestionably come more formerly again since exactly the same nearly very often inside case you shield this increase.

Responder

Best view i have ever seen !

Responder

Löydä aito seksi chatti kaveri jo tänään! Rekisteröidy ilmaiseksi ja aloita keskustelut jo tänään! Seksi chat ei jätä ketään kylmäksi edes tammikuussa!

Responder

There is noticeably a bundle to find out about this. I think you ensured wonderful factors in attributes additionally.

Responder

xxx pornoa, xxx porn, free xxx porn, tube free, xxx pornoa

Responder

ilmaiset pornovideot, pornovideot, porno videot, pornovideoita, porn videos, free pornvideos, porno video

Responder

entrepreneurs

Responder

Love watching sunset !

Responder

Love watching sunset !

Responder

Best view i have ever seen !

Responder

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

Responder

Real nice design and style and great articles , very little else we want : D.

Responder

This truly addressed my problem, thank you!

Responder

Its actually a great and helpful piece of information. I am satisfied that you shared this useful information with us. Please keep us informed like this. Thank you for sharing.

Responder

Airport Temperature Screening

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

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

The following time I check out a blog, I wish that it doesn’t dissatisfy me as high as this set. I suggest, I know it was my option to check out, yet I in fact thought youd have something interesting to say. All I hear is a number of grumbling concerning something that you can repair if you werent also active looking for focus.

Responder

netticasinot, netticasino, casino, kasinot, nettkasinot

Responder

Fantastic web site. Plenty of useful info here. I am sending it to several pals ans additionally sharing in delicious. And obviously, thanks in your effort!

Responder

GEM-CAR auto repair shop management software is arguably the most comprehensive software of its kind currently available. Lauded for its easy to use modules including CRM, digital inspection and electronic punch clock: it provides the repair shop operator with a complete system to run the business. GEM-CAR puts the shop operator firmly in the driver’s seat. And if you are a fleet operator look for GEM-FLEET our fleet management software.

Responder

Hey! This post couldn’t be written any better! Reading this post reminds me of my previous room mate! He always kept chatting about this. I will forward this page to him. Pretty sure he will have a good read. Thank you for sharing!

Responder

This website is really a walk-through for every one of the information you wanted regarding this and didn?t know who to ask. Peek below, and you?ll definitely discover it.

Responder

netticasinot, parhaat netticasinot, netticasino

Responder

I intended to put you one little observation just to give thanks over again over the pretty guidelines you have provided on this site. It was really seriously generous of people like you to allow freely exactly what a number of people could have made available as an e book to earn some money for their own end, certainly given that you might well have tried it in case you wanted. Those strategies as well acted to be a fantastic way to know that most people have a similar zeal much like my own to know whole lot more concerning this problem. I am sure there are many more pleasurable occasions ahead for those who start reading your website.

Responder

Great goods from you, man. I’ve understand your stuff previous to and you are just extremely wonderful. I really like what you’ve acquired here, really like what you’re saying and the way in which you say it. You make it enjoyable and you still care for to keep it smart. I can not wait to read far more from you. This is actually a terrific web site.

Responder

The following time I review a blog site, I wish that it does not dissatisfy me as long as this one. I imply, I understand it was my selection to check out, however I actually assumed youd have something fascinating to claim. All I hear is a bunch of whining concerning something that you can deal with if you werent too active looking for attention.

Responder

SHOP ALL BIKE mission is to provide only the finest & high quality Bike Accessories. Products available, backed by fast, efficient and accurate service. SHOP ALL BIKE is committed to doing all of this, and still maintain very competitive pricing

Responder

pikakasinot, kasinot ilman rekisteröitymistä

Responder

pikakasinot, kasinot ilman rekisteröitymistä

Responder

buy drugs online legally

Responder

Your location is valueble for me. Thanks !?

Responder

vedonlyöntibonukset

Responder

Hello.This article was extremely fascinating, especially since I was looking for thoughts on this topic last Monday.

Responder

I’ve been exploring for a little for any high quality articles or weblog posts on this kind of house . Exploring in Yahoo I ultimately stumbled upon this web site. Reading this info So i am happy to show that I’ve a very just right uncanny feeling I found out exactly what I needed. I such a lot definitely will make certain to do not omit this web site and give it a glance on a continuing basis.

Responder

electronics

Responder

Find The Top Rated Web Hosting

Responder

local hook up

Responder

You made some decent factors there. I looked on the internet for the issue as well as found most individuals will support with your site.

Responder

buy prescription drugs online

Responder

Outstanding post but I was wanting to know if you could write a litte more on this topic? I’d be very grateful if you could elaborate a little bit further. Kudos!

Responder

I found your blog site on google and check a few of your very early messages. Continue to maintain the very good run. I just added up your RSS feed to my MSN News Reader. Seeking onward to learning more from you later!?

Responder

online essay writer https://essaywriterdodo.com/ – essay writers toronto us essay writers magic essay writer top 10 essay writers

Responder

This website is really a walk-through for all of the info you wanted concerning this and also didn?t know who to ask. Glance here, as well as you?ll absolutely discover it.

Responder

coursework help https://courseworkhelpvra.com/ – coursework questions design technology coursework coursework history coursework masters

Responder

Our Mushroomburger The signature Mushroomburger patty was created back in 1979. Still to everyone’s surprise, it is approximately made up of 50 mushrooms and 50 real beef.

Responder

dissertation research https://dissertationaas.com/ – dissertation writing service dissertation writing tips doctoral dissertation writing writing a dissertation

Responder

I want to to thank you for this wonderful read!! I definitely enjoyed every bit of it. I’ve got you saved as a favorite to check out new things you post…

Responder

An outstanding share, I simply provided this onto a coworker that was doing a little evaluation on this. As well as he in fact bought me morning meal since I located it for him. smile. So let me rephrase that: Thnx for the reward! However yeah Thnkx for investing the moment to discuss this, I feel strongly regarding it and love finding out more on this topic. When possible, as you become competence, would certainly you mind upgrading your blog site with even more details? It is extremely handy for me. Large thumb up for this blog post!

Responder

essay 123 help https://essayhelptopp.com/ – college essay help service help 123 essay help me write essay need help with essay

Responder

book hotels, find hotels, discount hotels, hotel deals, compare hotels, search for hotels, best hotels, hotel deals, hotel packages, city packages

Responder

buy tadalafil online http://50cialmen.com/ – generic tadalafil buy cheap cialis cheap tadalafil cialis coupon

Responder

help essay 123 https://essaywriterslop.com/ – cambridge essay service п»їneed help in writing an essay essay on service cheap essay writing services

Responder

1 hour payday loans https://ausapaydayloans.com/ – payday loans no credit check online payday loans no credit check payday loans same day payday loans

Responder

professional custom essays https://customessayjojo.com/ – academic custom essays write custom essays custom application essay cheapest custom essay writing

Responder

I think this web site has got some very great information for everyone. «Loving someone is easy but losing someone is hard.» by Shelby Harthcock.

Responder

cheap essay papers https://essayonlinehdb.com/ – essays custom urgent custom essays cheap essay help pay to write my essay

Responder

Woah! I’m really enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that «perfect balance» between usability and visual appearance. I must say you’ve done a great job with this. Additionally, the blog loads very fast for me on Opera. Superb Blog!

Responder

thesis research proposal https://termpaperlow.com/ – good thesis statements phd thesis help buy a thesis writing thesis service

Responder

best paper writing service https://papersonlinesfy.com/ – write my paper online college papers writing service write my sociology paper write my math paper

Responder

I just could not depart your web site before suggesting that I actually enjoyed the standard information a person provide for your visitors? Is gonna be back often to check up on new posts

Responder

There is significantly a bundle to learn about this. I assume you made sure wonderful points in attributes also.

Responder

tadalafil cialis http://aazmencial.com/ – non prescription cialis cialis tadalafil 20mg cialis online no prescription

Responder

Site Rank Services offers affordable premium SEO packages & helps increase the visibility of your site in the search engines. We help you generate more profits

Responder

generic viagra http://45menvia.com/ – viagra cost viagra order cheap viagra from india viagra

Responder

I dugg some of you post as I thought they were very helpful very beneficial

Responder

Wow! This could be one particular of the most useful blogs We’ve ever arrive across on this subject. Basically Wonderful. I am also a specialist in this topic so I can understand your effort.

Responder

Good post. I learn something totally new and challenging on blogs I stumbleupon every day.It will always be useful to read content from other writers and usea little something from other websites.

Responder

Very nice post. I just stumbled upon your blog and wanted to say that I have truly enjoyed browsing your blog posts. After all I will be subscribing to your feed and I hope you write again very soon!

Responder

I blog quite often and I seriously appreciate your information. Your article has really peaked my interest. I will bookmark your blog and keep checking for new information about once per week. I subscribed to your Feed too.

Responder

Greetings! Very useful advice in this particular article! It’s the little changes that will make the greatest changes. Many thanks for sharing!

Responder

adult toys near me ,where to buy adult toys near me,male sex toys,gay male sex toys,best gay male sex toys,female sex toys,best female sex toys,homemade female sex toys,female adult toys,female masturbation toys,diy female sex toys,female pleasure toys,female transformers toys,female masturbation with toys,

Responder

Very excellent info can be found on web site. «Time discovers truth.» by Lucius Annaeus Seneca.

Responder

I truly wanted to compose a simple remark in order to say thanks to you for those nice ways you are giving out on this site. My rather long internet look up has finally been recognized with beneficial knowledge to share with my family members. I ‘d express that many of us visitors are undoubtedly endowed to dwell in a fantastic website with so many marvellous individuals with valuable techniques. I feel rather lucky to have discovered your entire site and look forward to so many more excellent minutes reading here. Thanks a lot once more for all the details.

Responder