Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Exportar Desde Excel y Guardar Archivo TXT Separado por Tabulaciones


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

En el ejemplo que a continuación se detalla, se muestra como se puede Exportar de Excel a TXT con Campos Delimitados por Tabulaciones, la macro de Excel creará un archivo TXT, recorrerá todas la filas Excel exportando los datos al fichero TXT de formato plano.

En el siguiente playlist encontrarás varios ejemplos de macros con el tema de exportar e importar de Excel a TXT y TXT a Excel.

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.

  
Suscribe a nuestro canal para que YouTube te avise cuando se suba nuevo contenido al canal, en el vídeo encontrarás una explicación gráfica y detallada del ejemplo que se muestra en este post.

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

 
Al descargar el fichero se puede observar un botón que al presionarlo ejecuta la macro exportando todos los datos contenidos en la Hoja1 a un archivo TXT creado por macro de Excel.

En primer lugar la macro de Excel determina cual es el nombre del archivo actual, es decir el archivo que contiene la macro, luego utiliza ese nombre para crear un Fichero TXT cuyo nombre es igual al del archivo con la macro, pero con extensión TXT, se usan los siguientes códigos:

nom = ActiveWorkbook.Name

pto = InStr(nom, «.»)

nomarch = Left(nom, pto – 1)

myfile = ThisWorkbook.Path & «» & nomarch & «.txt» 

Luego la macro de Excel carga en la variable «cara» cual va a ser el caracter delimitador de los datos que va a contener el TXT.
También se determinar la última fila con datos para poder realizar un bucle desde el dato inicial hasta el último dato de la base de datos, se usa la siguiente codificación:

cara = vbTab ‘tabulación para separar o delimitar caracteres

uf = a.Range(«A» & Rows.Count).End(xlUp).Row

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

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

La macro en forma posterior crea el Archivo TXT para ello se usa el siguiente código.

Open myfile For Output As #1

Luego inicia un bucle entre la primer y última fila con datos cargando en cada unas de las variables el dato correspondiente a la fila del bucle y la columna 1 a 7 que es donde está la base de datos, los códigos usados son:

C1 = Cells(i, 1)

C2 = Cells(i, 2)

C3 = Cells(i, 3)

C4 = Cells(i, 4)

C5 = Cells(i, 5)

C6 = Cells(i, 6)

C7 = Cells(i, 7)

Por último se concatenan los datos y se envía al TXT, con el siguiente código:

Print #1, C1 & cara & C2 & cara & C3 & cara & C4 & cara & C5 & cara & C6 & cara & C7

El ejemplo Como Exportar Desde Excel a TXT Delimitado por Tabulaciones, se puede descarga desde el link del final y a continuación se podrá observar toda la codificación del ejemplo de macro presentado en este post.

Código que se inserta en un módulo

‘**************https://macrosenexcel.com  **** https://youtube.com/programarexcel*********

#If VBA7 And Win64 Then

‘Si es de 64 bits

Public Declare PtrSafe Function ShellExecute Lib «shell32.dll» Alias «ShellExecuteA» (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr

Public Declare PtrSafe Function FindWindow Lib «USER32» Alias «FindWindowA» (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Public Declare PtrSafe Function GetWindowLongPtr Lib «USER32» Alias «GetWindowLongPtrA» (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr

Public Declare PtrSafe Function SetWindowLongPtr Lib «USER32» Alias «SetWindowLongPtrA» (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr

Public Declare PtrSafe Function DrawMenuBar Lib «USER32» (ByVal hwnd As Long) As LongPtr

Public Declare PtrSafe Function RegOpenKeyA Lib «advapire32.dll» (ByVal hKey As LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As LongPtr

#Else

‘Si es de 32 bits

Public Declare Function ShellExecute Lib «shell32.dll» Alias «ShellExecuteA» (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Declare Function FindWindow Lib «USER32» Alias «FindWindowA» (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Declare Function GetWindowLong Lib «USER32» Alias «GetWindowLongA» (ByVal hwnd As Long, ByVal nIndex As Long) As Long

Public Declare Function SetWindowLong Lib «USER32» Alias «SetWindowLongA» (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Public Declare Function DrawMenuBar Lib «USER32» (ByVal hwnd As Long) As Long

Public Declare Function RegOpenKeyA Lib «advapire32.dll» (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long

#End If


Sub ExportaTXTDelimitadoTabulaciones()

Dim i As Double

On Error Resume Next

Set a = Sheets(«Hoja1»)

nom = ActiveWorkbook.Name

pto = InStr(nom, «.»)

nomarch = Left(nom, pto – 1)

myfile = ThisWorkbook.Path & «» & nomarch & «.txt»

cara = vbTab ‘tabulación para separar o delimitar caracteres

uf = a.Range(«A» & Rows.Count).End(xlUp).Row


Open myfile For Output As #1

For i = 2 To uf

C1 = Cells(i, 1)

C2 = Cells(i, 2)

C3 = Cells(i, 3)

C4 = Cells(i, 4)

C5 = Cells(i, 5)

C6 = Cells(i, 6)

C7 = Cells(i, 7)

Print #1, C1 & cara & C2 & cara & C3 & cara & C4 & cara & C5 & cara & C6 & cara & C7

Next i

Close

MsgBox («El archivo txt se creo con éxito»), vbInformation, «AVISO»

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

great points altogether, you just won a logo new reader.
What might you recommend about your post that you just made some days
ago? Any positive?

Responder

Everything posted made a ton of sense. But, what about this?
what if you added a little information? I am not suggesting your content
isn’t solid., however suppose you added a headline that makes people want more?
I mean Como Exportar Desde Excel y Guardar Archivo TXT Separado por Tabulaciones – PROGRAMAR
EN VBA MACROS DE EXCEL is kinda vanilla. You should look at
Yahoo’s home page and note how they write article headlines to
get people interested. You might add a video or a pic or two to grab
people excited about what you’ve got to say. In my opinion, it might make your blog a little livelier.

Responder

I’m not sure why but this web site is loading
very slow for me. Is anyone else having this problem or is it a problem on my end?
I’ll check back later on and see if the problem still exists.

Responder

Wonderful post however , I was wondering if you could write a litte more on this topic?
I’d be very grateful if you could elaborate a
little bit more. Cheers!

Responder

Hmm it looks like your blog ate my first comment (it was extremely long) so I
guess I’ll just sum it up what I wrote and say, I’m thoroughly enjoying your blog.
I too am an aspiring blog blogger but I’m still new to
everything. Do you have any recommendations
for first-time blog writers? I’d definitely appreciate it.

Responder

Usually I don’t learn article on blogs, however I would like to say that this write-up very forced me to check out and do
so! Your writing taste has been surprised me.
Thanks, quite great article.

Responder

Hi, Neat post. There’s an issue along with your site in internet explorer,
would check this? IE nonetheless is the marketplace leader and
a huge section of folks will pass ovcer your mgnificent
writing due to this problem.

Responder

I blog often and I really thank you for your information. This great article has really peaked my interest.
I will take a note of your site and keep checking for
new details about once a week. I subscribed to
your Feed as well.

Responder

I don’t know whether it’s just me or if perhaps everybody
else encountering problems with your website. It appears as though some of the written text within your content are
running off the screen. Can someone else please provide feedback and let me know if this is happening to them too?

This could be a problem with my internet browser because I’ve had this happen previously.
Cheers

Responder

Have you ever considered about including a little bit more than just your
articles? I mean, what you say is fundamental and everything.
Nevertheless just imagine if you added some great images or videos to give your
posts more, «pop»! Your content is excellent but with pics and
videos, this blog could undeniably be one of the greatest in its field.
Great blog!

Responder

What’s up friends, how is everything, and what you
wish for to say on the topic of this piece
of writing, in my view its genuinely remarkable designed for me.

Responder

When I initially commented I clicked the «Notify me when new comments are added» checkbox and now each time a comment is
added I get three e-mails with the same comment. Is there any way you
can remove people from that service? Cheers!

Responder

I think that is among the most vital information for me.
And i am happy reading your article. But should remark on some normal issues, The web site taste is great, the articles is in point
of fact excellent : D. Excellent job, cheers

Responder

Hi, I do believe this is a great website. I stumbledupon it ;
) I will return once again since i have bookmarked it.
Money and freedom is the best way to change, may you be rich and continue to help other people.

Responder

Hi there, I discovered your site by way of Google at the same time as searching for a
similar topic, your web site got here up, it seems to be good.

I’ve bookmarked it in my google bookmarks.
Hello there, just turned into alert to your blog thru Google,
and found that it is truly informative. I’m going
to watch out for brussels. I will appreciate should you proceed this
in future. A lot of other people will likely be benefited out of your
writing. Cheers!

Responder

Ahaa, its fastidious dialogue on the topic of this piece of writing
at this place at this weblog, I have read all that, so now
me also commenting at this place.

Responder

This paragraph is truly a good one it assists new web people, who are wishing for blogging.

Responder

Thank you for the good writeup. It in fact was a amusement account it.
Look advanced to more added agreeable from you!
By the way, how can we communicate?

Responder

Aw, this was an incredibly good post. Spending some time
and actual effort to create a really good article… but what can I say… I
procrastinate a whole lot and never seem to get anything done.

Responder

This website definitely has all of the info I needed about this subject and
didn’t know who to ask.

Responder

whoah this blog is great i really like reading your
posts. Keep up the great work! You understand, a lot of persons are hunting around for this information, you could help them greatly.

Responder

The other day, while I was at work, my sister stole my iPad and tested to see if it can survive a twenty five foot
drop, just so she can be a youtube sensation. My
iPad is now broken and she has 83 views. I know this is
entirely off topic but I had to share it with someone!

Responder

I was suggested this web site by my cousin. I am
not sure whether this post is written by him
as nobody else know such detailed about my problem.
You’re incredible! Thanks!

Responder

I’ll right away grab your rss as I can’t find your email subscription hyperlink or e-newsletter
service. Do you’ve any? Kindly permit me recognise so that I may subscribe.
Thanks.

Responder

Fantastic goods from you, man. I have understand your stuff
previous to and you’re just too excellent. I really like what you have acquired here,
really like what you are stating and the way in which
you say it. You make it entertaining and you still care for to keep it sensible.
I cant wait to read far more from you. This
is really a terrific web site.

Responder

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

Responder

The article is really excellent. Every time I read it, I get information again.
The best article I’ve read in a long time…

Responder

I’m gone to say to my little brother, that he should also pay a
quick visit this weblog on regular basis to get
updated from most up-to-date reports.

Responder

I am now not sure the place you’re getting your info, however good topic.
I must spend some time finding out much more or working out more.
Thanks for great information I was looking for this information for my mission.

Responder

I’m not sure where you’re getting your information, but good topic.
I needs to spend some time learning more or understanding more.
Thanks for magnificent information I was looking for this information for my mission.

Responder

I seriously love your blog.. Very nice colors & theme.

Did you build this web site yourself? Please reply back as I’m hoping to create
my very own site and want to find out where you got this from or exactly what the theme is named.

Kudos!

Responder

I don’t know if it’s just me or if perhaps everyone else encountering issues
with your website. It looks like some of
the written text in your posts are running off the screen. Can somebody
else please comment and let me know if this is happening
to them as well? This might be a problem with my web browser because I’ve had this happen previously.
Many thanks

Responder

It’s impressive that you are getting thoughts from this paragraph as well as from our dialogue made at this place.

Responder

Since the admin of this website is working, no uncertainty very soon it will
be well-known, due to its feature contents.

Responder

What’s up mates, its great article on the topic of
cultureand completely defined, keep it up all the time.

Responder

Hi there, I discovered your web site by way of Google whilst looking for a comparable matter, your site got here up, it looks great.
I’ve bookmarked it in my google bookmarks.

Hi there, simply turned into aware of your blog through Google,
and found that it is truly informative. I’m gonna watch out for brussels.

I will appreciate for those who proceed this in future.
A lot of other people will probably be benefited from your writing.
Cheers!

Responder

Informative article, just what I needed.

Responder

… [Trackback]

[…] Read More here: macrosenexcel.com/como-exportar-desde-excel-y-guardar_14/ […]

Responder