CODEWITHSUNDEEP
vbaexcel
Dyhouse
Dyhouse

Reputation: 83

Remove blank lines from the end of a text file

I have written a sub routine which converts the data within an excel work sheet to a text file and saves it but it leaves me with a lot of blank lines at the end of the text file. As a different amount of data can be taken by this tab every time it is run, I assume that is what's causing the issue but I can't see that there is much I can do to change that as the data needs to be processed. Is there a way, using VBA, to remove the empty lines (white space) at the end of the text file or a better approach so it doesn't create the empty rows in the first place? I have done some searching and I can't find much on the subject using VBA. PLease help ?!

 'Selects appropriate worksheet - Non-MyPayFINAL
   Sheets("Non-MyPay FINAL").Select

 'Selects all data in column A and copies to clipboard
   Range("A1", Range("A1").End(xlDown)).Select
   Selection.Copy

  'Add a new workbook
    Workbooks.Add
  'Paste selected values from previous sheet
    Selection.PasteSpecial Paste:=xlPasteValues

  'Build SaveAs file name (for CSV file)
   MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".CSV"
 'Save template file as...(for CSV file)
   ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile), FileFormat:=xlCSV

  'Build SaveAs file name (for Txt file)
   MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".Txt"
   'Save template file as...(for Txt file)
     ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
    Limited\" & MySaveFile), FileFormat:=xlTextWindows

I have updated with the section of code which copies and creates the text file which I think is where the issue is. Any help with this would be greatly appreciated.

I have found the below code which goes a long way by allowing me to add text to the end of a txt file, can the same be used to remove text as well, apologies if this seems simple but I haven't quite got the hang of this yet.

 Sub TextFile_Create()
 'PURPOSE: Add More Text To The End Of A Text File

 Dim TextFile As Integer
 Dim FilePath As String

 'What is the file path and name for the new text file?
  FilePath = "S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\11072017MyPayFINAL.txt"

  'Determine the next file number available for use by the FileOpen 
   function
   TextFile = FreeFile

  'Open the text file
   Open FilePath For Append As TextFile

  'Write some lines of text
   Print #TextFile, "Sincerely,"
   Print #TextFile, ""
   Print #TextFile, "Chris"

  'Save & Close Text File
   Close TextFile

   End Sub

Upvotes: 0

Views: 4110

Answers (1)

user7857211
user7857211

Reputation:

1. Robust and basic way to delete "blanks" from the end of a text line:

For each line of your text file: read into string, get last character, delete if it is a "blank"; repeat until it is not a blank any more, write line to new file

2. Remove blanks with existing functions

If you have no other blanks, you can just use the 'replace' function, documentation here. Just replace the blank (e.g. " ") with "". For only deleting leading and/or trailing blanks use the trim function.

3. Parse before writing to file (recommended):

Check the output with one of the above mentioned methods before writing it to the file.

Just be careful about the characters you actually want to remove. If in doubt, view your created text file in an editor that can show you all characters.

Update

If you want to write specific data instead of the whole sheet, you need to adjust your script accordingly. This SO answer will give you a good start on how to write specific data to a file. Combined with the mentioned functions you should be good to go.

2nd Update

To remove leading and/or trailing spaces from a string, you can use the trim function as mentioned above. Here are examples and the documentation.

Upvotes: 2

Related Questions