user1132827
user1132827

Reputation: 41

Excel VBA Export to text file. Need to delete blank line

I have a workbook that I export to a text file using the below script. It works fine but when I open the text file there is always a blank line at the end that caused me issues with another script I run after I generate this text file. Any help at all on how I can remove the blank line from my export.

Code:

Sub Rectangle1_Click()
     Application.DisplayAlerts = False

' Save file name and path into a variable
    template_file = ActiveWorkbook.FullName   

' Default directory would be c:\temp.  Users however will have the ability 
' to change where to save the file if need be.

      fileSaveName = Application.GetSaveAsFilename( _
        InitialFileName:="C:\users\%username%\SNSCA_Customer_" + _
        VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
        fileFilter:="Text Files (*.txt), *.txt")

    If fileSaveName = False Then
        Exit Sub
    End If

    ' Save file as .txt TAB delimited fileSaveName, FileFormat:=36,

       ActiveWorkbook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlTextWindows, _
        CreateBackup:=False

       file_name_saved = ActiveWorkbook.FullName
    MsgBox "Your SNSCA configuration upload file has been " _
       & "successfully created at: " & vbCr & vbCr & file_name_saved

End Sub

Edit...

Here is the alternate that is not working either:

Sub Rectangle1_Click()
    Dim fPath As String
    Dim exportTxt As String
    fPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Sample_" & Format(Now(), "HHNNSS") & ".txt"

    exportTxt = ActiveWorkbook.

    Open fPath For Append As #1    'write the new file
    Print #1, exportTxt;
    Close #1
End Sub

Upvotes: 4

Views: 7854

Answers (1)

brettdj
brettdj

Reputation: 55692

While I have upticked the comment from Jean-François Corbett you can use this VBA below to remove the last line of your txt file (As you stated a blank line is written when saving this way).

This VBA is based on a commonly used routine. It

  • reads in your newly created text file, for example (*SNSCA_Customer_01092012.txt*)
  • splits it line by line
  • then rewrites all the lines except the the last to a new txt file(*SNSCA_Customer_01092012clean.txt*)

    Sub Rectangle1_Click()
    Dim strTemplateFile As String
    Dim strFname As String
    Dim strFnameClean As String
    Dim FileSaveName
    
    Application.DisplayAlerts = False
    ' Save file name and path into a variable
    strTemplateFile = ActiveWorkbook.FullName
    
    ' Default directory would be c:\temp.  Users however will have the ability
    ' to change where to save the file if need be.
    
    FileSaveName = Application.GetSaveAsFilename( _
                   InitialFileName:="C:\users\%username%\SNSCA_Customer_" + _
                                    VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
                   fileFilter:="Text Files (*.txt), *.txt")
    
    If FileSaveName = False Then
        Exit Sub
    End If
    
    ' Save file as .txt TAB delimited fileSaveName, FileFormat:=36,
    ActiveWorkbook.SaveAs Filename:= _
                          FileSaveName, FileFormat:=xlTextWindows, _
                          CreateBackup:=False
    
    strFname = ActiveWorkbook.FullName
    strFnameClean = Replace(ActiveWorkbook.FullName, ".txt", "clean.txt")
    MsgBox "Your SNSCA configuration upload file has been " _
         & "successfully created at: " & vbCr & vbCr & strFname
    Call Test(strFname, strFnameClean)
    End Sub
    
    
    Sub Test(ByVal strFname, ByVal strFnameClean)
    Const ForReading = 1
    Const ForWriting = 2
    
    Dim objFSO As Object
    Dim objTF As Object
    Dim strAll As String
    Dim varTxt
    Dim lngRow As Long
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTF = objFSO.OpenTextFile(strFname, ForReading)
    strAll = objTF.readall
    objTF.Close
    Set objTF = objFSO.createTextFile(strFnameClean, ForWriting)
    varTxt = Split(strAll, vbCrLf)
    For lngRow = LBound(varTxt) To UBound(varTxt) - 1
        objTF.writeline varTxt(lngRow)
    Next
    objTF.Close
    End Sub
    

Upvotes: 1

Related Questions