Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como leer un archivo TXT separado coma con macro


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

En un ejemplo anterior se presentó una macro que lee archivos TXT, en el presente post muestro una macro que permite leer archivos TXT pero delimitados o separados por coma o punto y coma; es decir los datos que se encuentran en el archivo de texto plano, se encuentran separados por coma o punto y coma.

Antes de seguir 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 link que se encuentra al final de post permite descargar el ejemplo de macro denominada como leer un archivo TXT separado o delimitado por coma o punto y coma; consiste en un archivo comprimido conteniendo dos archivos uno de Excel con la macro y otro archivo TXT que se usa para mostrar el funcionamiento del ejemplo, pero se puede usar cualquier archivo que tenga cada lector en su PC cuyos datos se encuentren delimitados o separados por coma.

El ejemplo agrega en la variable cada una de las cadenas de texto, a través de la función de VBA Split; cuya separación entre datos este dada por punto y coma, en este caso, pero se puede ser coma, para ello se debe cambiar la siguiente parte del código:

texto = Split(cad, «;»)

por este código:

texto = Split(cad, «,»)

Luego se realiza un bucle hasta que se llegue al final de todas las lineas del archivo TXT; por cada una de las cadenas de texto separadas por punto y coma, que fueran cargadas en la variable texto a través de la función Split, las cuales se van agregando en las diferentes columnas de la hoja de Excel.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, 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 formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access 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);
}
}

 

Código que se inserta en un módulo
Sub opentxtseparadoscoma()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim myfile As Variant, cad As String, fila As Long, texto() As String
Dim h As Long, fil As Long, col As Long
ruta = ActiveWorkbook.Path
ChDir ruta
myfile = Application.GetOpenFilename(«Archivos Txt (*.txt*), *.txt*»)
If VarType(myfile) = vbBoolean Then Exit Sub
Open myfile For Input As #1
fil = 1
col = 1
Cells.Clear
While Not EOF(1)
Line Input #1, cad
texto = Split(cad, «;»)
For h = 0 To UBound(texto)
Cells(fil, col) = fil
Cells(fil, col).Value = texto(h)
col = col + 1
Next h
col = 1
fil = fil + 1
Wend
Close #1
MsgBox («Los datos se leyeron con éxito»), 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 una respuesta

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

Comentarios (33)

It’s great that you are getting ideas from this post as well as from our argument made at this place.

Responder

Hey there, I think your blog might be having browser compatibility issues.

When I look at your blog site in Opera, it looks fine but when opening in Internet Explorer, it has some
overlapping. I just wanted to give you a quick heads up!
Other then that, excellent blog!

Responder

Hello my friend! I wish to say that this post is amazing,
great written and include approximately all vital infos.
I would like to see extra posts like this .

Responder

Hello there, I discovered your website by means of Google even as
searching for a comparable topic, your website came
up, it appears good. I’ve bookmarked it in my google bookmarks.

Hello there, simply turned into aware of your weblog through Google, and found that it’s truly informative.

I am gonna watch out for brussels. I’ll be grateful if you happen to continue this in future.
Numerous folks shall be benefited out of your writing.
Cheers!

Responder

Hi, I wish for to subscribe for this weblog to get most recent updates, therefore where can i do it please assist.

Responder

What’s up Dear, are you in fact visiting this web site on a regular basis, if so
afterward you will definitely obtain nice experience.

Responder

I don’t know whether it’s just me or if perhaps everyone else encountering
problems with your website. It appears as if some of the written text on your posts are running off the screen. Can someone else please provide feedback and let
me know if this is happening to them too? This may be a issue with
my web browser because I’ve had this happen before. Kudos

Responder

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

Responder

Awesome website you have here but I was curious about if you knew
of any user discussion forums that cover the same topics talked about here?
I’d really like to be a part of community where I can get
advice from other knowledgeable individuals that share
the same interest. If you have any recommendations, please
let me know. Thanks!

Responder

constantly i used to read smaller posts that as well clear their motive,
and that is also happening with this article which I am reading at
this time.

Responder

Do you mind if I quote a few of your posts as long as I provide credit and sources back to your website?
My website is in the exact same niche as yours and my visitors would truly benefit from
some of the information you present here. Please let me know if this alright with you.
Thank you!

Responder

Very engaging theme and presentation, and also terrific material. Great job! 😀

Responder

Hi, after reading this remarkable post i am too glad to share my familiarity here with friends.

Responder

Normally I don’t learn article on blogs, however I wish
to say that this write-up very compelled me to take a look at and do so!

Your writing taste has been surprised me. Thank you, quite nice article.

Responder

If you are going for best contents like myself, just pay a quick visit this website all the time because it
gives quality contents, thanks

Responder

Nice post. I learn something totally new and challenging on sites I stumbleupon every day.

It will always be interesting to read content from other writers and practice something from other web sites.

Responder

Hi there i am kavin, its my first occasion to commenting anyplace, when i read this article
i thought i could also make comment due to this sensible
post.

Responder

I was very happy to discover this page. I need to to thank you for ones time due to this fantastic
read!! I definitely enjoyed every little bit of it and I have you book-marked to see new things in your website.

Responder

Magnificent site. Lots of helpful info here. I am sending it to
a few pals ans also sharing in delicious. And obviously, thank you in your effort!

Responder

Hey! I could have sworn I’ve been to this website before but after reading through
some of the post I realized it’s new to me. Anyways, I’m definitely delighted I found
it and I’ll be bookmarking and checking back often!

Responder

When some one searches for his vital thing, therefore he/she needs to be available
that in detail, therefore that thing is maintained over here.

Responder

What i do not understood is in fact how you are no longer
really much more smartly-preferred than you might be right now.
You’re very intelligent. You realize therefore significantly
when it comes to this subject, produced me in my view consider it
from a lot of varied angles. Its like women and men are not interested until it is one thing
to accomplish with Woman gaga! Your personal stuffs excellent.

At all times care for it up!

Responder

Everything is very open with a very clear description of the challenges.

It was truly informative. Your website is useful.
Thanks for sharing!

Responder

I was recommended this website by way of my cousin. I’m no longer positive whether or not this publish is written by him as nobody else realize
such particular approximately my trouble. You are wonderful!
Thanks!

Responder

Hi there i am kavin, its my first occasion to commenting
anyplace, when i read this article i thought i could also
make comment due to this sensible article.

Responder

Hi there, I enjoy reading through your article post. I wanted to write a little comment to
support you.

Responder

Hey there would you mind stating which blog platform you’re using?

I’m going to start my own blog in the near future but I’m
having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your design and style seems different then most blogs and I’m
looking for something unique. P.S Sorry for getting off-topic but I had to ask!

Responder

Hey I know this is off topic but I was wondering if you knew of
any widgets I could add to my blog that automatically tweet my newest twitter
updates. I’ve been looking for a plug-in like this for quite some time and
was hoping maybe you would have some experience with something like this.
Please let me know if you run into anything.
I truly enjoy reading your blog and I look forward to your new updates.

Responder

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

Responder

What’s up it’s me, I am also visiting this web page daily, this web
site is genuinely fastidious and the viewers are in fact sharing fastidious thoughts.

Responder

Most companies rate the duties/responsibilities resume template, as snooze-worthy and of no value. Instead, employers have to learn what places you above the other candidates, with on-the-job examples of contributions you’ve made for prior work.

Responder

Hello! I just wanted to ask if you ever have any issues with hackers?
My last blog (wordpress) was hacked and I ended up losing a few
months of hard work due to no backup. Do you have any methods to protect against hackers?

Responder

thanks for the above content: I must complete a presentation I am up to my ears dealing with, and I’ve been gathering information like the above. thanks for the help!

Responder