Read/Write between Excel and Text Files

[nmk34]

I am writing a table of 8 columns from Excel 360 to Text file and reading the same columns back to same range in excel. the data in excel left justify to get the data columns in the TextFile to line up. when i read the data back, the data looks way off(possibly like each column separated by the TAB) and the commas show up in excel. when writing to the text file i use the commas to know the end of each field.
is it possible to write to a text file for example sheet range ("A2:D14") as table or columns where the top row is the headers, and the data read back into excel using other sub will look lined up as we started with before writing to the text file.?

 

[Vogateer]

It sounds like you're making a CSV file on export, but with extra spaces or tabs to make it look a bit nicer as plain monospaced text. If it's a good CSV file, then maybe you could use Power Query (Get Data -> CSV) to get the data back in properly.

 

[nmk34]

 

[Vogateer]

Do you need that final comma on each line in the text file? It seems unnecessary and it's causing you problems when you bring it back in.

 

[Vogateer]

Even if you leave that final comma on, you should be able to address it like this:

Public Sub ReadDataFromTextFile()

    Dim i As Long, j As Long, LineText As String, CellValue As String
    'Open "C:\mytxtfile.txt" For Input As #1
    Open ThisWorkbook.Path & "\ProductTable1.txt" For Input As #1
    'Open ThisWorkbook.Path & "\mytextfile.txt" For Input As #1
    i = 2
    While Not EOF(1)
        Line Input #1, LineText
        Dim arr As Variant
        
        arr = Split(CStr(LineText), ", ") ' splitting text based on commas
        ' str = VBA.Split(ActiveCell.Value, vbCrLf) splitting text based on cariage return
        
        For j = 1 To 8 ' max number of column in the excel sheet that will have data from text file
            CellValue = Trim(arr(j - 1)) ' Trim to get rid of extra spaces
            If Right(CellValue, 1) = "," Then
                CellValue = Mid(CellValue, 1, Len(CellValue) - 1) ' Remove final comma
                ActiveSheet.Cells(i, j).Value = CellValue
            Else
                ActiveSheet.Cells(i, j).Value = CellValue
                End If
            Next j
            i = i + 1
        Wend
    Close #1

    End Sub