0

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
Dyhouse
  • 83
  • 1
  • 5
  • 15
  • to be honest, it would probably be better to amend your existing code to only copy over the number of lines that are currently in the Excel file. This shouldn't add too much to your code but will remove the need to remove lines from the text file – danl Jul 11 '17 at 08:50

1 Answers1

2

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.

Graham
  • 7,431
  • 18
  • 59
  • 84
  • could you give an example of what this code might look like please? I am pretty new to VBA and the hyperlink in your answer takes me to some coding with Visual Basic that I don't really recognise too much. – Dyhouse Jul 11 '17 at 09:09
  • In fact it doesn't look like VBA at all!! – Dyhouse Jul 11 '17 at 09:21
  • 1
    I am slowly getting there, using the below code, I can now get the file I need open and I can overwrite with any string or text but how would I combine the replace so that it only removes spaces at the end of the text within the file, NOT remove spaces in the middle of text strings... 'Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dim Fileout As Object Set Fileout = fso.CreateTextFile("C:\your_path\vba.txt", True, True) Fileout.Write "your string goes here" Fileout.Close' – Dyhouse Jul 11 '17 at 10:49
  • Don't paste code in comments, update your question with your code instead. See my updated answer. –  Jul 11 '17 at 10:54