从文本文件的末尾删除空白行

问题描述

我已经写了一个子例程，将excel工作表中的数据转换为文本文件并保存，但在文本文件末尾留下了很多空白行。由于此标签每次运行时可以采用不同数量的数据，我认为是导致问题的原因，但是我不能看到，随着数据需要处理，我可以做更多的改变。有没有办法使用VBA来删除文本文件末尾的空行（空格）或更好的方法，因此首先不会创建空行？我做了一些搜索，我使用VBA找不到很多关于这个问题。选择合适的工作表 - 非MyPayFINAL
表单（非MyPay FINAL）。选择

'选择列A中的所有数据并复制到剪贴板
范围（A1，范围（A1）。结束（xlDown））。选择
选择。复制

'添加新的工作簿
Workbooks.Add
'从上一张表中粘贴所选值
Selection.PasteSpecial粘贴：= xlPasteValues

'Build SaveAs文件名（CSV文件）
MySaveFile = Format（Now（），DDMMYYYY）& NonMyPayFINAL& .CSV
'将模板文件保存为...（用于CSV文件）
ActiveWorkbook.SaveAs（S：\MERIT OUTPUTS FOLDER \MSI Recruitment
Limited\& ; MySaveFile），FileFormat：= xlCSV

'构建SaveAs文件名（用于Txt文件）
MySaveFile = Format（Now（），DDMMYYYY）& NonMyPayFINAL& .Txt
'将模板文件保存为...（用于Txt文件）
ActiveWorkbook.SaveAs（S：\MERIT OUTPUTS FOLDER\MSI Recruitment
Limited\& ; MySaveFile），FileFormat：= xlTextWindows

我已经更新了代码段，我认为是问题的文本文件。对此的任何帮助将不胜感激。

我发现以下代码，通过允许我将文本添加到txt文件的末尾，可以很长的路要走，可以同样用于删除文本，道歉，如果这看起来很简单，但我还没有得到这个悬念。

  Sub TextFile_Create（）
'目的：添加更多文本到文本文件的结尾
 
 Dim TextFile As Integer 
 Dim FilePath As String 
 
'新文本文件的文件路径和名称是什么？ 
 FilePath =S：\MERIT OUTPUTS FOLDER\MSI Recruitment 
 Limited\11072017MyPayFINAL.txt
 
'确定FileOpen $可用的下一个文件编号b $ b函数
 TextFile = FreeFile 
 
'打开文本文件
打开FilePath作为TextFile添加
 
'写一些文本行
打印#TextFile，真的，
打印#TextFile
打印#TextFile，Chris
 
'保存&关闭文本文件
关闭TextFile 
 
 End Sub 
  

解决方案

1。从文本行结尾删除空白的鲁棒和基本的方式：

对于文本文件的每一行：读入字符串，最后字符，如果是空白，则删除;重复，直到它不再是空白，写入新文件

2。删除具有现有功能的空白

如果没有其他空格，您可以使用'replace'功能，文档。只需用替换空白（例如）。仅使用 trim 函数删除前导和/或尾随空格。

3。在写入文件之前解析（推荐）：

在将其写入文件之前，请使用上述方法之一检查输出。

请注意您实际想要删除的字符。如果有疑问，请在编辑器中查看您创建的文本文件，以显示所有字符。另请阅读并且相应地改善你的代码。

更新

数据而不是整个工作表，您需要相应地调整脚本。 答案将为您提供如何将特定数据写入文件的良好开端。结合上述功能，您应该很好去。

第二次更新

要从字符串中删除前导和/或尾部空格，可以使用如上所述的 trim 函数。以下是和。

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

解决方案

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. Also read through the best practice section and improve your code accordingly.

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.

这篇关于从文本文件的末尾删除空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！