macro to convert excel file to space delimited txt file I\'m looking for a macro

Question

macro to convert excel file to space delimited txt file

I'm looking for a macro to save data that exists in a specific excel sheet into a .txt file with specific layout.

There are no headers in the file and specifications should be as per below.

Input from excel file looks like below but I can change this if needed

Output in txt format should look like below:

79432827       20140826ABCD0001         3.000        6.96ABACZZ  

     79433325       20140826EFGH0001        10.000       17.30ACAZYXW

Thanks in advance for any help offfered.

content position length content format EXAMPLE 1 EXAMPLE 2 FIELD 1 1 13 => number but length can vary from 1 to 9, leading spaces should be added to make sure field length is exactly 13 79418311 79421862 FIELD 2 2 7 => empty text field but spaces should be added to makes sure field length is exactly 7 FIELD 3 3 8 => number and length is always exactly 8 20140826 20140829 FIELD 4 4 9 => alphanumeric and length is always exactly 8 and 1 space shoudld be addded afterwards to have field length of 9 ABCD0001 EFGH9999 FIELD 5 5 13 => decimal number (style comma #.000), leading spaces should be added to make sure field length is exactly 13 so spaces are variable 8.000 126.000 FIELD 6 6 12 => decimal number (style comma #.00), leading spaces should be added to make sure field length is exactly 12 so spaces are variable 15.57 1.00 FIELD 7 7 3 => text and always consists of 3 characters ABA ACA FIELD 8 8 4 => text bbut length can vary from 2 to 4 characters, spaces should be added afterwards to have field length of 4 ZZ ZYXW

Explanation / Answer

Hi

This should do it ; but I only added the current field lenght yp get to 69.

Normally,, I think, you should add a space characterfor each field to delimit the columns correctly.

Sub ExportText()
Dim TempLine As String
Dim fileNum As Integer
Dim strFileName As String
Dim cl As Range
Dim POS As Integer
strFileName = ThisWorkbook.Path & "Demo.txt"

fileNum = FreeFile 'get a freefile number
Open strFileName For Output As #fileNum

'define the length of a line by adding up all the fields length
    TempLine = Space(69))

For Each cl In Range("A1", Range("A1").End(xlDown))
TempLine = Space(69)
'write to specific position in string

   Mid(TempLine, 13 - Len(cl.Cells(1, 1))) = cl.Cells(1, 1)
   Mid(TempLine, 20 - Len(cl.Cells(1, 2))) = cl.Cells(1, 2)
   Mid(TempLine, 28 - Len(cl.Cells(1, 3))) = cl.Cells(1, 3)
   Mid(TempLine, 37 - Len(cl.Cells(1, 4))) = cl.Cells(1, 4)
   Mid(TempLine, 50 - Len(cl.Cells(1, 5))) = cl.Cells(1, 5)
   Mid(TempLine, 62 - Len(cl.Ceells(1, 6))) = cl.Cells(1, 6)
   Mid(TempLine, 65 - Len(cl.Ceells(1, 7))) = cl.Cells(1, 7)
   Mid(TempLine, 69 - Len(cl.Cells(1, 8))) = cl.Cells(1, 8)
Print #fileNum, TempLine
Next cl
Close #fileNum
End Subbb

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.