VBA: Keep column formatting when exporting spreadsheet to .txt file

[iand5]

Hi, I have currently the following code to export an Excel Spreadsheet to a .txt file.

'Saves the Activesheet to the Desktop as a text file with a different extension
Dim newFile As String, fName As String, gName, fileSaveName
    fName = "TEST"
    newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "Text (MS-DOS) (*.txt), *.txt")
    ThisWorkbook.SaveAs fileSaveName, xlTextMSDOS
End Sub

The problem is that the .txt file does not have the correct spacing.The columns are not aligned with their corresponding value.

Any help on how to keep formatting the same on the .txt file as in the excel spreadsheet?

 

[Joe4]

If you want it to maintain the spacing based on the columns widths in Excel, try using/saving with the following option:
Formatted Text (Space Delimited) (*.prn)

Note that you can use any file extension with this (does not need to be .prn).

If that does not work for you, please provide small examples of:
- what your data looks like in Excel
- how you want it to look in the text file
- how it is appearing in the text file

And let us know what program you are using to view the text file.

 

[iand5]

This is how my data looks in excel and how I want it displayed in .txt file(Sorry for some reason the spacing is not taken into account when I post it here):

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sched Start Sched End Number(s) Equipment
09/17/2015 0715 02/28/2018 1600 12 C10

</code></code>How it looks in .txt file:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sched Start Sched End Number(s) Equipment
09/17/20150715 02/28/20181600 12 C10
</code>
</code>The columns are not correctly aligned with their corresponding values in the .txt file. Also, where would I place this statement in the code: Formatted Text (Space Delimited) (*.prn) ?

Thanks

 

[Joe4]

Try this:

    Dim newFile As String, fName As String, gName, fileSaveName
    fName = "TEST"
    newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "Text (*.txt), *.txt")
    ThisWorkbook.SaveAs fileSaveName, [B][COLOR=#ff0000]xlTextPrinter[/COLOR][/B]
    
End Sub

 

[iand5]

Try this:

    Dim newFile As String, fName As String, gName, fileSaveName
    fName = "TEST"
    newFile = gName & " " & fName & " " & Format$(Date, "dd-mm-yyyy")
    fileSaveName = Application.GetSaveAsFilename(newFile, "Text (*.txt), *.txt")
    ThisWorkbook.SaveAs fileSaveName, [B][COLOR=#ff0000]xlTextPrinter[/COLOR][/B]
    
End Sub

That didn't seem to work.

I discovered that when the values are copied between spreadsheets, the third column text is right-aligned. Where as all other columns are left-aligned. All columns are left-aligned on the source spreadsheet.

I think this is why when exporting as a .txt file the alignment is not correct.

Thanks for your help @Joe4

 

[Joe4]

From the example you posted in post #3 , I cannot tell where one cell ends, and the next starts.
For example, if there is a space between two values, is the space at the end of the first cell, or at the beginning of the second cell?

We really need to know:
- the width of each field
- which fields are right-aligned and which fields are left-aligned

 

[iand5]

[COLOR=#303336][FONT=Consolas][FONT=inherit]Sched Start      Sched [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End       [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Number[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]s[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Equipment
[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]09[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]/[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]17[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]/[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]2015 [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]0715  [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]02[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]/[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]28[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]/[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]2018 [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1600 [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]12[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]        C10
[/FONT][/FONT][/COLOR]

This is how it should be aligned. Everything is left aligned in the source file.

When I copy the values from the Spreadsheet1(Source file) to Spreadsheet2, the third column is right-aligned.

Third column is "Number(s)"

 

[Joe4]

Third column is "Number(s)"

Try changing the format of the column to "Text", and see if that resolves the issue.
Text files are file without formatting. So I don't know that you can capture the manual left/right/center alignment that you choose (just like text files can have bolding, italics, or underlining - unless it is a Rich Text File).
However, text fields are automatically left-justified and numeric columns are automatically right-justified. So by changing the column's data type to "Text", it may do what you want.