CODEWITHSUNDEEP
excelvba
joe
joe

Reputation: 53

How to remove trailing tabs and quotation marks when converting a excel file into a .txt file?

Every time I use this code, double quotation marks appear for cell's output from beginning to ending. Also I have issue with trailing tabs. Anyway how I can remove these quotation marks and trailing tabs?

I tried using a basic VBA, where it copies data from a certain column and converts it to a txt file.

I tried using the code below but the ouput was a blank ptxt file. It did not even output anything. I am new to VBA so any help would be appreciated.

Private Sub CommandButton1_Click()

Dim s As String, FileName As String, FileNum As Integer

  ' Define full pathname of TXT file
  FileName = ThisWorkbook.Path & "\2019 NERC N1 Contingencies.txt"

  ' Copy range to the clipboard
  Range("A2", Cells(Rows.Count, "A").End(xlUp)).Copy

  ' Copy column content to the 's' variable via clipboard
  With New DataObject
     .GetFromClipboard
     s = .GetText
  End With
  Application.CutCopyMode = False

  ' Write s to TXT file
  FileNum = FreeFile
  If Len(Dir(FileName)) > 0 Then Kill FileName
  Open FileName For Binary Access Write As FileNum
  Put FileNum, , s
  Close FileNum

'-----------------------Get rid of trailing tabs
  Dim sTemp As String

    Open ThisWorkbook.Path & "\2019 NERC N1 Contingencies.txt" For Output As #1
    For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        sTemp = ""
        For Each c In r.Cells
            sTemp = sTemp & c.Text & Chr(9)
        Next c

        'Get rid of trailing tabs
        While Right(sTemp, 1) = Chr(9)
            sTemp = Left(sTemp, Len(sTemp) - 1)
        Wend
        Print #1, sTemp
    Next r
    Close #1
End Sub

Ouput: [Blank Page]

Desired Ouptut: CON=10 NO TRAILING TAB OR QUOTATION MARK

Upvotes: 0

Views: 355

Answers (1)

Warcupine
Warcupine

Reputation: 4640

I think this is what you are looking for. Not sure if you wanted each cell to be its own line in the text file, but that's how this one will print. This removes the need to copy to the clipboard aswell, which is generally not ideal.

This will also automatically overwrite the existing file so no need to check for it ahead of time.

Private Sub CommandButton1_Click()
    dim filename as string
    dim chrstr as string
    dim cellvar as variant
    dim fso as object
    dim txtfso as object

    Set fso = CreateObject("Scripting.FileSystemObject") 'Late bound, if you want early binding you will need to add in the reference
    Set txtfso = fso.CreateTextFile(ThisWorkbook.Path & "\2019 NERC N1 Contingencies.txt", True)

    chrstr = ""
    for each cellvar in Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'If you have a ton of rows you will want to load this range into an array and access the array elements over the sheet.
        chrstr = chrstr & cellvar.value & vbnewline 'I don't know what your desired output is, mess with this line to change how it looks
    next

    txtfso.writeline (chrstr)
    txtfso.close
end sub

Upvotes: 0

Related Questions