In fact there is a limitation in Excel up to 240 for columns, but Microsoft itself gives us a light.
Below is a model adapted from Microsoft to export all data from the active spreadsheet to a text file:
Sub ExportText()
Dim delimiter As String
Dim quotes As Integer
Dim Returned As String
delimiter = " "
quotes = MsgBox("Coloca informações das células com aspas?", vbYesNo)
' Chama a função WriteFile
Returned = WriteFile(delimiter, quotes)
' Mensagem Final
Select Case Returned
Case "Canceled"
MsgBox "A operação foi cancelada"
Case "Exported"
MsgBox "O arquivo foi exportado com sucesso!"
End Select
End Sub
Function WriteFile(delimiter As String, quotes As Integer) As String
Dim CurFile As String
Dim SaveFileName
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalRows As Double
Dim TotalCols As Double
' Indica local e arquivo a ser exportado
If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
Else
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Text Delimited Exporter")
End If
' Verifica se pressionou cancelar
If SaveFileName = False Then
WriteFile = "Canceled"
Exit Function
End If
' Inicia processo de escrita no arquivo
FNum = FreeFile()
Open SaveFileName For Output As #FNum
' Seleciona na planilha ativa as células utilizada
ActiveSheet.usedrange.select
' Busca o total de linhas e colunas selecionadas
TotalRows = Selection.Rows.Count
TotalCols = Selection.Columns.Count
' Inicia Loop para salvar cada célula no arquivo
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
Dim ColWidth as Integer
ColWidth=Application.RoundUp(.ColumnWidth, 0)
' Grava o conteúdo da célula na variável
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
Case xlCenter
CellText = Space(Abs(ColWidth - Len(.Text))/2) & .Text & _
Space(Abs(ColWidth - Len(.Text))/2)
Case Else
CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
End Select
End With
' Grava o arquvio
Select Case quotes
Case vbYes
CellText = Chr(34) & CellText & Chr(34) & delimiter
Case vbNo
CellText = CellText & delimiter
End Select
Print #FNum, CellText;
' Atualiza a barra de status conforme progresso
Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
+ ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
' Próxima coluna no Loop
Next ColNum
' Adiciona quebra de linha no final de cada linha
If RowNum <> TotalRows Then Print #FNum, ""
' Proxima linha no Loop
Next RowNum
' Fecha o arquivo
Close #FNum
' Reseta a barra de status
Application.StatusBar = False
WriteFile = "Exported"
End Function
Source: https://support.microsoft.com/pt-br/help/249885/formatted-text-.prn-is-limited-to-240-characters-per-line-in-excel
Good morning! It’s not a limitation of .prn, test here with 1000 lines and worked perfectly. The space separation, when saving Excel follows the size set in the column, use the Notepad++ to open your file .prn and run tests.
– Evert
Good Morning! Actually I referred to the limitation of columns and not rows, in fact the lines have no limit. Were you able to generate a . prn file from Excel with the 33 columns and all following the spacing? I do this and open in Notepad++ and column 27 it breaks the line. Thanks for the return.
– Diego Santos
Truth to column has limitation of 240... I made an adaptation in the code below but should work with active spreadsheet!
– Evert
In the Microsoft example, to remedy this limitation, they put only the selected cells, I made a small adaptation to export the active spreadsheet, but can do as you think best there. If you have trouble with the code I am available friend. Good luck!
– Evert