Export a range of data in excel to ascii plain text formatted table csv or txt file using vba - Stack Overflow
Importing ASCII text files with extensions other than .txt or .csv
Importing a huge ASCII file that doesn't have delimiters?
Fixed Width Format for ASCII/Text File based on column positions.
Videos
If I'm not mistaken, a plain text file won't justify a column at all.
That said, give this a try: http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
Well, I got it working.
Sub Convert2Text()
Dim i As Integer, k As Integer, n As Integer, m As Integer
Dim Rng As Range, Dpath As String
Dim FSO As Scripting.FileSystemObject
Dim TxtFile As TextStream
Dim MaxLen As Integer
Set Rng = Application.InputBox("Please Select range.", "-Convert to Text-", , -Range("R2").Left, Range("R2").Top, , , 8)
If Rng Is Nothing Then
Exit Sub
End If
Dpath = Environ("USERPROFILE") & "\Documents\"
Set FSO = New FileSystemObject
Set TxtFile = FSO.CreateTextFile(Dpath & "Outputfile.txt", True)
MaxLen = 1
For i = 1 To Rng.Columns.Count
For k = 1 To Rng.Rows.Count
If MaxLen < Len(Rng.Cells(i, k)) Then
MaxLen = Len(Rng.Cells(i, k))
End If
Next k
Next i
MaxLen = MaxLen + 4
For i = 1 To Rng.Columns.Count
TxtFile.Write "+"
For m = 1 To MaxLen
TxtFile.Write "="
Next m
Next i
TxtFile.Write "+"
TxtFile.WriteLine
For n = 1 To Rng.Rows.Count
For i = 1 To Rng.Columns.Count
TxtFile.Write "|"
TxtFile.Write " "
TxtFile.Write " "
TxtFile.Write Rng.Cells(n, i)
For k = 1 To MaxLen - (Len(Rng.Cells(n, i))) - 2
TxtFile.Write " "
Next k
Next i
TxtFile.Write "|"
TxtFile.WriteLine
For i = 1 To Rng.Columns.Count
TxtFile.Write "+"
For m = 1 To MaxLen
TxtFile.Write "-"
Next m
Next i
TxtFile.Write "+"
TxtFile.WriteLine
Next n
MsgBox "Done!"
Shell "explorer.exe " & Dpath, vbNormalFocus
End Sub
Now, I have to focus on getting the numbers right aligned. Thank you all for your suggestions.
Of course, I can do that - but to rename files is a source of error as it is easy to forget to rename them back. And I want to spend my time doing science, not programming around the sloppy way Microsoft treats Office for Mac.
The problem is not that the file format is not supported - These are all plain vanilla ASCII text files. My problem is the obligatory link between ASCII text files and the extension *.txt.
I am working in structural bioinformatics, and specialized programs I use to generate these files require different extensions: e.g. *.pdb for atom coordinates, *.seq for nucleotide sequences,*.aln for multisequence alignments, *.rsa for residue accessibility, *.ddG for energy changes upon mutation ...
While I can import all of these programmatically through home-spun vba macros, no longer being able to just specify that I want to open one of these files e.g. as a fixed format or delimited text file independent of the file extension is a bloody nuisance.
I have some data from the 1970s that is only saved as a massive ASCII file with 3600 columns. There are no delimiters in the file (no commas, no spaces that have meaning, no semi-colons, literally just long strings of numbers). I have an associated codebook file that tells me that certain columns are associated with certain elements of the data (for instance, telling me that "Col 1-4" correspond to one piece of data, while "Col 5" corresponds to another piece of data, etc.).
My question is: Is there some software or feature of Excel that will allow me to import the data and specify these columns without me having to use the "fixed width" option to hand delimit 3600 columns?
As an additional point of reference, I am proficient with Stata. If anyone has an alternative using Stata, I'm all ears.
Thank you so much for anyone who can offer help! This is killing me.