Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Crear un Archivo de Excel por Cada Registro Listado


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

El ejemplo se realizó por pedido de un suscriptor de nuestro canal de youtube, la macro muestra como crear un archivo o fichero de Excel por cada registro de un listado, es decir que partiendo de un hoja de Excel con un listado de nombres la macro a recorriendo cada fila y creando un archivo de Excel con cada registro, siendo el nombre del archivo el correspondiente al registro de cada fila.

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 entender el ejemplo de forma más fácil recomiendo descargar el archivo de ejemplo, es gratis desde el final se puede bajar, cuando se abre el Libro de Excel se podrá observar en la columna B una serie de nombres, en este caso son link a los ejemplos de macros publicados en esta Web, lo que la macro hará es recorrer fila por fila creando un archivo de Excel, cuyo nombre será el que se encuentra en la columna B y fila correspondiente que vaya recorriendo el bucle en ese momento.

En un primer momento verifica si existe la carpeta donde se guardarán los archivos, en caso de no existir la crea, la carpeta se va a ubicar en el directorio donde se encuentre el archivo con la macro adicionando o creando la carpeta en ese mismo directorio llamada «Archivos Excel», el siguiente código realiza lo mencionado:

rutadir = ActiveWorkbook.Path & «Archivos Excel»
If Dir(rutadir, vbDirectory) = «» Then
MkDir rutadir
End If

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

Quizá sea de utilidad también

Como determinar el nombre de un archivo

Como buscar palabra en un archivo de Word en un Directorio si lo encuentra crea link

Como buscar un archivo en una carpeta y determinar su ruta o path

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

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

Luego se crea un bucle For… Next, en el ejemplo va del 5 al 20, la fila con datos empieza en la fila 5 por eso comienza en 5 y 20 es un número que se puso a los fines del ejemplo, cada uno lo puede modificar incluso el bucle puede ir hasta la última fila con datos, pero se debe tener en cuenta que dependiendo de la cantidad de registros la macro tardará unos minutos en crear todos los archivos de Excel.

En cada recorrido de bucle, se determinará el nombre del archivo que va a crear, el nombre del fichero lo toma de la columna B y fila correspondiente, al nombre se le quitan los posibles caracteres que pueda contener el nombre del archivo y que son inválidos para nombrar archivos, porque son caracteres exclusivos de Windows, se hace con el siguiente código:

nomfic = a.Cells(x, 2)
nomfic = Replace(nomfic, «/», «»)
nomfic = Replace(nomfic, «,», «»)
nomfic = Replace(nomfic, «…», «»)
nomfic = Replace(nomfic, «.», «»)
nomfic = nomfic & «.xlsx»
rutaxls = rutadir & «» & nomfic

Por último crear el archivo, lo guarda con el  nombre determinado y cierra el fichero, para seguir con la creación del siguiente archivo de Excel hasta que se cumpla el bucle, el siguiente es el código usado, al final está el código completo.

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=rutaxls
Workbooks(nomfic).Close SaveChanges:=True

Código que se inserta en un módulo

Sub CreaArchivoExcel()
Dim verexi As Object, rutaxls As String, rutadir As String, nomfic As String, conta As Variant
Dim ObjExcel As Application, ObjLibro As Workbook
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set a = Sheets(«BD URL»)
If a.Range(«B5») = Empty Then
MsgBox «No existen registros para crear archivos Excel», vbCritical, «AVISO»
Exit Sub
End If
conta = 0

rutadir = ActiveWorkbook.Path & «Archivos Excel»
If Dir(rutadir, vbDirectory) = «» Then
MkDir rutadir
End If

‘uf = a.Range(«B» & Cells.Rows.Count).End(xlUp).Row
uf = 20
For x = 5 To uf
Application.StatusBar = «Creando » & x & » de » & uf & » archivos Excel»
nomfic = a.Cells(x, 2)
nomfic = Replace(nomfic, «/», «»)
nomfic = Replace(nomfic, «,», «»)
nomfic = Replace(nomfic, «…», «»)
nomfic = Replace(nomfic, «.», «»)
nomfic = nomfic & «.xlsx»
rutaxls = rutadir & «» & nomfic
conta = conta + 1
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=rutaxls
Workbooks(nomfic).Close SaveChanges:=True
Next x

MsgBox «Se crearon » & conta & » archivos de Excel», vbCritical, «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 (95)

I am the owner of JustCBD Store label (justcbdstore.com) and I am currently seeking to develop my wholesale side of business. I am hoping someone at targetdomain can help me . I considered that the most suitable way to accomplish this would be to talk to vape stores and cbd retailers. I was really hoping if anybody could suggest a reputable web site where I can get CBD Shops B2B Database I am already considering creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence 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? Ideas?

Responder

закрыть фирму с долгами посмотрите что советуют профессионалы ликвидация ООО

Responder

Nice post. I learn something totally new and challenging on sites I stumbleupon on a daily basis. It’s always helpful to read through content from other authors and practice something from their websites.

Responder

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

Responder

The very next time I read a blog, I hope that it won’t fail me as much as this particular one. After all, Yes, it was my choice to read through, nonetheless I really thought you’d have something helpful to say. All I hear is a bunch of complaining about something you could fix if you were not too busy looking for attention.

Responder

I needed to thank you for this great read!! I absolutely enjoyed every bit of it. I’ve got you book-marked to check out new stuff you post…

Responder

Aw, this was a really good post. Taking a few minutes and actual effort to produce a superb article… but what can I say… I procrastinate a lot and never manage to get nearly anything done.

Responder

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

Responder

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

Responder

Good write-up. I absolutely appreciate this site. Keep it up!

Responder

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

Responder

Hi there! I could have sworn I’ve visited this web site before but after looking at a few of the articles I realized it’s new to me. Regardless, I’m certainly pleased I found it and I’ll be bookmarking it and checking back frequently!

Responder

Hello there! This blog post couldn’t be written much better! Reading through this post reminds me of my previous roommate! He always kept preaching about this. I’ll send this post to him. Fairly certain he’s going to have a good read. Thank you for sharing!

Responder

Great site you have got here.. It’s hard to find excellent writing like yours nowadays. I seriously appreciate people like you! Take care!!

Responder

I blog often and I seriously appreciate your information. This great article has really peaked my interest. I’m going to bookmark your blog and keep checking for new details about once a week. I subscribed to your RSS feed too.

Responder

That is a very good tip particularly to those new to the blogosphere. Simple but very accurate information… Thanks for sharing this one. A must read post!

Responder

Hello, Neat post. There’s a problem as well as your website in internet explorer,
would check this? IE nonetheless may be the marketplace chief and a
huge a part of people will leave out your excellent writing
due to this concern.

Also visit my webpage … MelXArvelo

Responder

Howdy! I could have sworn I’ve visited your blog before but after going through some of the posts I realized it’s new to me. Anyways, I’m definitely delighted I found it and I’ll be bookmarking it and checking back often!

Responder

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

Responder

Howdy! This blog post could not be written any better! Going through this article reminds me of my previous roommate! He continually kept preaching about this. I’ll send this article to him. Fairly certain he’ll have a good read. Thank you for sharing!

Responder

Good day! I just wish to give you a big thumbs up for the great information you have here on this post. I am returning to your web site for more soon.

Responder

This is a topic that is near to my heart… Take care! Where are your contact details though?

Responder

Hello there, just changed into alert to your weblog via Google, and located that
it’s really informative. I’m gonna be mindful for brussels.

I will be grateful when you proceed this in future.
Many other individuals will probably be benefited out of your writing.
Cheers!

Have a look at my web page … EdisonPKazee

Responder

Good blog post. I certainly appreciate this website. Keep writing!

Responder

That is a great tip especially to those fresh to the blogosphere. Brief but very accurate information… Many thanks for sharing this one. A must read post!

Responder

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

Responder

Saved as a favorite, I like your website!

Responder

It’s hard to find knowledgeable people in this particular subject, but you seem like you know what you’re talking about! Thanks

Responder

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

Responder

Hi, I do think this is a great web site. I stumbledupon it 😉 I’m going to come back once again since i have saved as a favorite it. Money and freedom is the greatest way to change, may you be rich and continue to help other people.

Responder

naturally like your web-site however you need to take a look at the spelling on several of your posts.

Many of them are rife with spelling issues and I find
it very bothersome to inform the reality however I’ll
surely come back again. adreamoftrains web hosting companies

Responder

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

Responder

Howdy! This blog post couldn’t be written much better! Looking at this article reminds me of my previous roommate! He continually kept preaching about this. I most certainly will send this article to him. Fairly certain he’s going to have a great read. Thanks for sharing!

Responder

This is a topic that is close to my heart… Many thanks! Where are your contact details though?

Responder

Excellent article. I’m experiencing some of these issues as well..

Responder

bookmarked!!, I like your blog!

Responder

After looking into a number of the articles on your site, I really appreciate your way of writing a blog. I saved it to my bookmark site list and will be checking back in the near future. Take a look at my website as well and let me know what you think.

Responder

I was pretty pleased to discover this great site. I need to to thank you for your time due to this fantastic read!! I definitely savored every little bit of it and i also have you book-marked to see new stuff in your site.

Responder

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

Responder

I want to to thank you for this good read!! I certainly enjoyed every little bit of it. I’ve got you book marked to look at new things you post…

Responder

There is definately a lot to find out about this topic. I really like all of the points you have made.

Responder

Aw, this was a very nice post. Finding the time and actual effort to generate a good article… but what can I say… I procrastinate a lot and never manage to get anything done.

Responder

Spot on with this write-up, I seriously believe this web site needs far more attention. I’ll probably be returning to read through more, thanks for the advice!

Responder

You’re so interesting! I do not suppose I have read anything like this before. So nice to discover another person with unique thoughts on this topic. Seriously.. thank you for starting this up. This website is one thing that’s needed on the internet, someone with some originality!

Responder

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

Responder

After checking out a number of the articles on your website, I seriously appreciate your technique of blogging. I book marked it to my bookmark webpage list and will be checking back soon. Take a look at my website as well and let me know your opinion.

Responder

This is a very good tip especially to those new to the blogosphere. Simple but very precise information… Appreciate your sharing this one. A must read article!

Responder

I blog quite often and I really thank you for your content. The article has really peaked my interest. I am going to book mark your website and keep checking for new information about once per week. I subscribed to your RSS feed too.

Responder

Very good post. I definitely love this site. Keep it up!

Responder

An outstanding share! I’ve just forwarded this onto a friend who has been doing a little research on this. And he actually ordered me dinner because 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 topic here on your web page.

Responder

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

Responder

Hey there just wanted to give you a quick heads up. The text in your post seem to be running off the screen in Firefox. I’m not sure if this is a format issue or something to do with web browser compatibility but I thought I’d post to let you know. The design look great though! Hope you get the problem resolved soon. Kudos|

Responder

I could not resist commenting. Perfectly written!

Responder

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

Responder

Hi, i think that i saw you visited my web site so i came to ìreturn the favorî.I’m trying to find things to improve my website!I suppose its ok to use some of your ideas!!|

Responder

Thankfulness to my father who told me concerning this webpage, this blog is truly amazing.

my site … DeonCSones

Responder

I truly love your website.. Very nice colors & theme. Did you build this website yourself? Please reply back as I’m wanting to create my own blog and would love to find out where you got this from or exactly what the theme is called. Thank you!|

Responder

Nice post. I was checking constantly this blog and I am impressed!

Very helpful info particularly the last part 🙂 I care for such info much.

I was seeking this certain info for a very long time.
Thank you and good luck. y2yxvvfw cheap flights

Responder

Whoa! This blog looks just like my old one! It’s on a entirely different topic but it has pretty much the same layout and design. Outstanding choice of colors!|

Responder

Your article makes perfect sense. Writing that is worth reading. oh yeah btw also visit my website at http://big2.poker. Thanks

Responder

whoah this weblog is wonderful i love studying your posts.
Keep up the great work! You recognize, a lot of individuals are looking round for
this info, you could help them greatly.

Responder

I’am amazed

Responder

Hey there! This post could not be written any better! Reading through this post reminds me of my good old room mate! He always kept talking about this. I will forward this article to him. Pretty sure he will have a good read. Thank you for sharing!|

Responder

I got this web site from my buddy who shared with me about
this website and at the moment this time I am visiting this web page and reading very informative content here.

Responder

I’m impressed, I have to admit. Rarely do I come across a blog that’s both equally educative and entertaining, and let me tell you, you’ve
hit the nail on the head. The problem is something not enough people are speaking intelligently
about. Now i’m very happy that I found this in my hunt for
something regarding this.

Responder

I couldn’t refrain from commenting. Well written!|

Responder

It’s perfect time to make some plans for the future and it is time to be happy. I have read this post and if I could I want to suggest you some interesting things or advice. Perhaps you could write next articles referring to this article. I want to read even more things about it!|

Responder

Howdy! I could have sworn I’ve been to this website before but after looking at many of the articles I realized it’s new to me. Regardless, I’m certainly delighted I came across it and I’ll be book-marking it and checking back frequently!|

Responder

I have read several excellent stuff here. Certainly value bookmarking for revisiting.
I surprise how a lot attempt you place to create one
of these wonderful informative website.

Responder

I am really impressed with your writing skills as well as with the layout on your weblog. Is this a paid theme or did you customize it yourself? Anyway keep up the nice quality writing, it’s rare to see a great blog like this one nowadays.|

Responder

I have read several excellent stuff here. Certainly value bookmarking for revisiting. I wonder how so much attempt you set to make any such excellent informative site.|

Responder

quite interesting article. however, in some cases it still needs improvement.

Responder

your writing really helped me I know what steps to take.

Responder

Hi, There’s no doubt that your site may be having browser compatibility problems. Whenever I look at your site in Safari, it looks fine however, when opening in I.E., it has some overlapping issues. I simply wanted to give you a quick heads up! Other than that, wonderful blog!

Responder

When I initially commented I seem to have clicked the -Notify me when new comments are added- checkbox and now every time a comment is added I receive 4 emails with the same comment. There has to be an easy method you can remove me from that service? Thanks a lot!

Responder

Hi, I do believe your blog could be having web browser compatibility problems. When I look at your blog in Safari, it looks fine but when opening in I.E., it’s got some overlapping issues. I simply wanted to provide you with a quick heads up! Aside from that, wonderful blog!

Responder

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

Responder

Good post. I learn something new and challenging on websites I stumbleupon on a daily basis. It will always be interesting to read through content from other writers and practice a little something from other sites.

Responder

I’am amazed

Responder

I have been browsing on-line more than three hours today, but I never discovered any fascinating article like yours. It is lovely worth sufficient for me. Personally, if all website owners and bloggers made good content as you did, the internet can be a lot more helpful than ever before.|

Responder

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

Responder

Awesome blog! Do you have any tips for aspiring writers? I’m hoping to start my own blog soon but I’m a little lost on everything. Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m completely confused .. Any tips? Many thanks!|

Responder

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

Responder

I truly love your site.. Pleasant colors & theme. Did you create this website yourself? Please reply back as I’m trying to create my very own site and would like to learn where you got this from or what the theme is named. Kudos!

Responder

My spouse and that i stumbled right here coming from a
different page and thought I might check things out.
I love what I see so now i’m following you.
Anticipate looking over your online page for a second time.

Have a look at my web page VondaVEscoto

Responder

Hey there! I just wish to offer you a big thumbs up for the excellent information you’ve got right here on this post. I’ll be returning to your web site for more soon.

Responder

Everything is very open with a very clear description of the issues. It was really informative. Your site is very useful. Many thanks for sharing!

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 ordered me dinner because I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending time to talk about this issue here on your web page.

Responder

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

Responder

A fascinating discussion is worth comment. I believe that you ought to write more on this issue, it might not be a taboo subject but typically people do not speak about such topics. To the next! All the best!!

Responder

Hi there! This blog post couldn’t be written any better! Looking through this post reminds me of my previous roommate! He constantly kept preaching about this. I will forward this information to him. Fairly certain he’ll have a great read. Many thanks for sharing!

Responder

May I simply say what a comfort to uncover someone who really understands what they are discussing on the web. You definitely realize how to bring a problem to light and make it important. More people need to check this out and understand this side of the story. I can’t believe you are not more popular since you most certainly have the gift.

Responder

Books, magazines, articles. you name it. We have by no means played with Real On line casino Poker Chips before. They can perform many video games as feasible for them. Even if you pay you will not be allowed to enter.

Responder

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

Responder

I cherished as much as you will obtain performed proper here. The caricature is tasteful, your authored subject matter stylish. nonetheless, you command get got an impatience over that you would like be handing over the following. in poor health no doubt come more beforehand again as exactly the same just about a lot regularly within case you shield this increase.

Responder