Extra space at end of pasted range in text file?

DAGGERGA

New Member
Joined
Aug 12, 2005
Messages
14
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?

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DAGGERGA

New Member
Joined
Aug 12, 2005
Messages
14
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2005
Messages
14
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top