Extra space at end of pasted range in text file?

[DAGGERGA]

Is there something inherent in saving as tab delimited text that adds an extra space at the end of the text? My code below works as I want, but the file created as text invariably has an extra space at the end. I know the answer is something incredibly simply, but I'm stumped?!

When I select/copy the dynamic range, it only selects the rows with data, and those rows have no extraneous spaces...

Can anyone help?

Sub CopyPasteTxt()
Dim Sht As Worksheet

' Unprotect Tool File

For Each Sht In ThisWorkbook.Worksheets
Sht.Unprotect Password:="password"

Next
' Dynamically select scan data from Tool File

    Sheets("Scans").Select
    Range("GarageInfo").End(xlDown).Select
    
            
' Copy dynamic range

    Range("GarageInfo").Copy
    
      
' Paste into dummy workbook for text file save

    Workbooks.Add
    Worksheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
        
' Save file name and path into a variable
   template_file = ActiveWorkbook.FullName
   
    gName = Range("A1").Value
    sName = Range("A2").Value
 
' Default directory would be c:\temp.  Users however will have the ability to change where to save the file if need be.
' Notice that i'm only allowing the save as option to be of .txt format.
   fileSaveName = Application.GetSaveAsFilename( _
    InitialFileName:="C:\temp\" & gName & "_" & sName & ".txt", _
    filefilter:="Text Files (*.txt), *.txt")
    
    If fileSaveName = False Then
        Exit Sub
    End If


' Save file as .txt TAB delimited
   ActiveWorkbook.SaveAs Filename:= _
        fileSaveName, FileFormat:=xlText, _
        CreateBackup:=False

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

' Protect Tool File

    For Each Sht In ThisWorkbook.Worksheets
    Sht.Protect Password:="password"
    Next

'Email the upload file to the Control Room
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "joe@blow.com"
        .CC = "john@doe.com"
        .BCC = "jane@smith.com"
        .Subject = "Upload file from " & gName & "_" & sName
        .Body = "Upload file attached"
        .Attachments.Add ActiveWorkbook.FullName
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing


ActiveWorkbook.Close True
' closes the active workbook and saves any changes


End Sub

 

[DAGGERGA]

From searching some other threads, I've determined that my problem child is the vbCrLf that's at the end of the pasted data, but I'm struggling with the vb to get rid of that from my dynamic selection. Anyone?

 

[xenou]

From searching some other threads, I've determined that my problem child is the vbCrLf that's at the end of the pasted data, but I'm struggling with the vb to get rid of that from my dynamic selection. Anyone?

Why did you come to this conclusion? Personally, I don't see either why you would have a carriage return in your data, or how this would lead to an extra space in the text file.

Your code is too long - hard to say if its the original data, the way you copy it, the export, or the emailing ... You must break it down into steps and identify where the issue creeps in. Start with the original data - is it good clean data to begin with. Make sure the columns to the right are cleared of all data and all formats and preferably deleted away. Same with rows below.

 

[DAGGERGA]

The data is in a single column, input from a laser scanner scanning barcodes. That scanner has carriage return functionality built in so that once you've scanned a barcode, it sends the cursor down to the next cell automatically.

As far as a I can tell, the scan itself has no extra/hidden characters other than the scanned serial number. By process of elimination, and by the fact that my saved text file allows the cursor to the start of the next row below the last serial number, I think it's a line feed, but I'm driving myself batty trying to incorporate various code I've found in this forum to eliminate that.

Some sample data from the original spreadsheet...


G519
0001
123456789
123456788
123456787
123456768
123456777

 

[xenou]

It's hard to say. It looks like normal excel data from here. If there's really carriage returns at the end of each line you should be able to trim them off.