0

I have an Excel file that looks as follows:

https://i.stack.imgur.com/AMi3c.jpg

(sorry for not embedding the image but I am not allowed to do that at the moment because I just registered here)

I want to export this to a TXT file where the number of characters of each column is given.

For example: Every cell from Column A should have exactly 3 characters in my textfile, Column B should have 5. If my cell does not contain the exact number of characters then the TXT file should be filled up with spaces.

So the TXT file should look like this:

https://i.stack.imgur.com/5U7AM.jpg

I worked with Excel Macros before but I'm not really sure how to get started on this one. Thanks for any help, very much appreciated

Community
  • 1
  • 1
Martin
  • 11
  • 1
  • You might want to have a look at the following VBA code: http://stackoverflow.com/questions/35434220/vba-replacing-commas-in-csv-not-inside-qoutes/35440236#35440236. It is working with text-based files and edits them. With that as a starting-point it should be easy for you to figure out the rest (given the fact that you have worked with Excel marcos before). – Ralph Jun 02 '16 at 16:32

1 Answers1

1

This is the code that worked for me:

Sub Export_File()

Dim myFile As String, r As Integer, mypath As String, dline As String

mypath = "C:\RESULTS\" 'path for file
myFile = "file.txt" 'file name
dline = ""

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

A = Cells(r, "A") & Application.WorksheetFunction.Rept(" ", 3 - Len(Cells(r, "A")))
B = Cells(r, "B") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "B")))
C = Cells(r, "C") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "C")))
D = Cells(r, "D") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "D")))
E = Cells(r, "E") & Application.WorksheetFunction.Rept(" ", 7 - Len(Cells(r, "E")))
F = Cells(r, "F") & Application.WorksheetFunction.Rept(" ", 7 - Len(Cells(r, "F")))
G = Cells(r, "G") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "G")))
H = Cells(r, "H") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "H")))
I = Cells(r, "I") & Application.WorksheetFunction.Rept(" ", 5 - Len(Cells(r, "I")))


dline = dline & A & B & C & D & E & F & G & H & I & vbLf & vbCr

Next r

Open mypath & myFile For Output As #1 'Replaces existing file
Print #1, dline
Close #1

MsgBox ("File Created " & mypath & myFile)


End Sub
Martin
  • 11
  • 1