Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Exportar Desde Excel a TXT con Ancho Fijo Rellenando Campos con Ceros


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

Macro de Excel que muestra Como Exportar a TXT con ancho fijo rellenando espacios en blanco con ceros, la macro exporta los datos de la hoja de Excel a TXT delimitados con un Ancho Fijo, en caso que los caracteres del campo sean menores al ancho establecido rellena los valores faltantes con cero.

Si estás trabajando con listbox quizás quieras aprender más sobre este objeto de VBA para Excel, en el link encontrarás muchos ejemplos que serán de utilidad relacionados con listbox de Excel.

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

 
La macro que exporta de Excel a TXT con ancho fijo, en primer lugar determina el nombre del archivo con el que se va a guardar el TXT, que será el nombre del archivo con la macro y la extensión TXT, de la siguiente forma:

nom = ActiveWorkbook.Name
pto = InStr(nom, «.»)
nomarch = Left(nom, pto – 1)
myfile = ThisWorkbook.Path & «» & nomarch & «.txt»

Posteriormente se establece el ancho de las columnas, es decir que cantidad de caracteres debe contener cada campo, en este caso se establece el ancho fijo de casa columna, usando el siguiente código:

‘largo de campos
larC1 = 5
larC2 = 10
larC3 = 50
larC4 = 50
larC5 = 15
larC6 = 10
larC7 = 15

Luego se establece el caracter con el cual se va a rellenar los espacios faltantes en caso que el dato contenido en cada campo sea menor al ancho establecido, así:

cara = «0» ‘caracter para completar el espacio

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

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 recorre con un bucle hasta la última fila con datos todas las filas de la hoja de Excel que se requiere exportar a TXT, concatenando el valor de cada campo con la cantidad de caracteres necesarios para llenar el ancho fijo establecido, de la siguiente forma:

C1 = String(larC1 – Len(Cells(i, 1)), cara) & Cells(i, 1)

Por último se concatenan todas las columnas y exporta a TXT con el siguiente código:
Print #1, C1 & C2 & C3 & C4 & C5 & C6 & C7
El ejemplo que permite exportar de Excel a TXT con ancho fijo rellenando espacios con cero se puede descargar del link del final, a continuación se muestra la codificación completa, sugiero ver el vídeo asociado para entender en forma más fácil el ejemplo.

Código que se inserta en un Módulo de Excel

‘**************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 ExportaTXTAnchoFijoRellenoCeros()
Dim i As Double
On Error Resume Next
Set a = Sheets(«Hoja1»)

‘largo de campos
larC1 = 5
larC2 = 10
larC3 = 50
larC4 = 50
larC5 = 15
larC6 = 10
larC7 = 15
cara = «0» ‘caracter para completar el espacio
uf = a.Range(«A» & Rows.Count).End(xlUp).Row

Open myfile For Output As #1
For i = 2 To uf
C1 = String(larC1 – Len(Cells(i, 1)), cara) & Cells(i, 1)
C2 = String(larC2 – Len(Cells(i, 2)), cara) & Cells(i, 2)
C3 = Cells(i, 3) & String(larC3 – Len(Cells(i, 3)), cara)
C4 = Cells(i, 4) & String(larC4 – Len(Cells(i, 4)), cara)
C5 = String(larC5 – Len(Cells(i, 5)), cara) & Cells(i, 5)
C6 = String(larC6 – Len(Cells(i, 6)), cara) & Cells(i, 6)
C7 = String(larC7 – Len(Cells(i, 7)), cara) & Cells(i, 7)
Print #1, C1 & C2 & C3 & C4 & C5 & C6 & 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 un comentario

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

Comentarios (31)

Thanks a lot for the post.Really thank you! Much obliged.online doctor prescription for viagra

Responder

I am the manager of JustCBD Store brand (justcbdstore.com) and I’m presently trying to grow my wholesale side of business. I am hoping someone at targetdomain can help me ! I thought that the most ideal way to do this would be to talk to vape shops and cbd retailers. I was really hoping if anyone could suggest a trustworthy site where I can buy Vape Shop International Sales Leads I am currently examining creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not sure which one would be the best option and would appreciate any assistance on this. Or would it be much simpler for me to scrape my own leads? Suggestions?

Responder

Быстрый результат. комментарии адвокатов банкротство юридических лиц а так же субсидиарная ответственность директора

Responder

Howdy, I believe your website could be having internet browser compatibility issues. Whenever I take a look at your blog in Safari, it looks fine however when opening in Internet Explorer, it has some overlapping issues. I simply wanted to give you a quick heads up! Besides that, excellent site!

Responder

I was very happy to find this page. I want to to thank you for ones time due to this fantastic read!! I definitely liked every bit of it and I have you saved as a favorite to check out new things in your web site.

Responder

Spot on with this write-up, I really believe this site needs a great deal more attention. I’ll probably be returning to read through more, thanks for the information!

Responder

I could not refrain from commenting. Well written!

Responder

I couldn’t resist commenting. Exceptionally well written!

Responder

I’d like to thank you for the efforts you’ve put in writing this blog. I am hoping to check out the same high-grade content by you in the future as well. In truth, your creative writing abilities has inspired me to get my own site now 😉

Responder

I like it when folks come together and share views. Great site, stick with it!

Responder

You should take part in a contest for one of the finest blogs on the web. I will highly recommend this web site!

Responder

Hello there, I believe your site may be having internet browser compatibility issues. When I take a look at your web site in Safari, it looks fine however, if opening in I.E., it’s got some overlapping issues. I simply wanted to provide you with a quick heads up! Other than that, excellent website!

Responder

Aw, this was an incredibly good post. Taking a few minutes and actual effort to produce a good article… but what can I say… I put things off a whole lot and don’t manage to get nearly anything done.

Responder

I was able to find good advice from your articles.

Responder

Good site you’ve got here.. It’s difficult to find excellent writing like yours these days. I really appreciate individuals like you! Take care!!

Responder

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

Responder

It’s difficult to find experienced people on this subject, however, you sound like you know what you’re talking about! Thanks

Responder

Saved as a favorite, I love your blog!

Responder

An impressive share! I have just forwarded this onto a coworker who was doing a little research on this. And he actually bought me breakfast because I discovered it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending the time to talk about this subject here on your web page.

Responder

Way cool! Some extremely valid points! I appreciate you penning this write-up and the rest of the site is very good.

Responder

Right here is the right webpage for anyone who wants to understand this topic. You know so much its almost hard to argue with you (not that I really will need to…HaHa). You certainly put a brand new spin on a subject which has been written about for a long time. Excellent stuff, just excellent!

Responder

Good article. I’m dealing with some of these issues as well..

Responder

That is a really good tip especially to those fresh to the blogosphere. Short but very precise information… Appreciate your sharing this one. A must read article!

Responder

Hi there! This article couldn’t be written any better! Looking through this post reminds me of my previous roommate! He continually kept talking about this. I will forward this post to him. Pretty sure he’s going to have a great read. Many thanks for sharing!

Responder

An outstanding share! I’ve just forwarded this onto a coworker who had been conducting a little homework on this. And he actually ordered me dinner simply because I stumbled upon it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanx for spending time to discuss this issue here on your web site.

Responder

I’d like to thank you for the efforts you’ve put in penning this blog. I really hope to view the same high-grade blog posts from you later on as well. In truth, your creative writing abilities has motivated me to get my own, personal blog now 😉

Responder

You are so awesome! I do not suppose I’ve read through anything like this before. So wonderful to discover another person with some genuine thoughts on this issue. Seriously.. thank you for starting this up. This web site is one thing that’s needed on the internet, someone with a bit of originality!

Responder

Great delivery. Sound arguments. Continue the amazing work.

Stop by my page … JaredINikkel

Responder

I would like to thank you for the efforts you have put in writing this site. I am hoping to see the same high-grade content by you in the future as well. In fact, your creative writing abilities has motivated me to get my own site now 😉

Responder

Good info. Lucky me I found your site by accident (stumbleupon). I’ve bookmarked it for later!

Responder

Having read this I thought it was rather enlightening. I appreciate you finding the time and effort to put this informative article together. I once again find myself spending a lot of time both reading and commenting. But so what, it was still worthwhile!

Responder