Excel VBA导出到文本文件。需要删除空行 [英] Excel VBA Export to text file. Need to delete blank line
本文介绍了Excel VBA导出到文本文件。需要删除空行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个工作簿,我使用以下脚本将其导出为文本文件。它工作得很好,但是当我打开文本文件时,末尾总是有一个空行,这会导致我在生成此文本文件后运行的另一个脚本出现问题。有关如何从导出中删除空行的任何帮助。
编码:
Sub Rectangle1_Click()
Application.DisplayAlerts = False
' Save file name and path into a variable
template_file = ActiveWorkbook.FullName
' Default directory would be c: emp. Users however will have the ability
' to change where to save the file if need be.
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:users\%username%SNSCA_Customer_" + _
VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
Exit Sub
End If
' Save file as .txt TAB delimited fileSaveName, FileFormat:=36,
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, FileFormat:=xlTextWindows, _
CreateBackup:=False
file_name_saved = ActiveWorkbook.FullName
MsgBox "Your SNSCA configuration upload file has been " _
& "successfully created at: " & vbCr & vbCr & file_name_saved
End Sub
编辑.
以下是也不起作用的备用方案:
Sub Rectangle1_Click()
Dim fPath As String
Dim exportTxt As String
fPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "Sample_" & Format(Now(), "HHNNSS") & ".txt"
exportTxt = ActiveWorkbook.
Open fPath For Append As #1 'write the new file
Print #1, exportTxt;
Close #1
End Sub
VBA
虽然我已经增加了Jean-François Corbett的注释,但是您可以使用下面的推荐答案删除txt文件的最后一行(正如您所说的,以这种方式保存时会写入一个空行)。
此VBA基于常用的vbscript例程。IT- 读取新创建的文本文件,例如(*SNSCA_CUSTOMER_01092012.txt*)
- 逐行拆分
然后将除最后一行以外的所有行重写到新的txt文件(*SNSCA_CUSTOMER_01092012lean.txt*)
Sub Rectangle1_Click() Dim strTemplateFile As String Dim strFname As String Dim strFnameClean As String Dim FileSaveName Application.DisplayAlerts = False ' Save file name and path into a variable strTemplateFile = ActiveWorkbook.FullName ' Default directory would be c: emp. Users however will have the ability ' to change where to save the file if need be. FileSaveName = Application.GetSaveAsFilename( _ InitialFileName:="C:users\%username%SNSCA_Customer_" + _ VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _ fileFilter:="Text Files (*.txt), *.txt") If FileSaveName = False Then Exit Sub End If ' Save file as .txt TAB delimited fileSaveName, FileFormat:=36, ActiveWorkbook.SaveAs Filename:= _ FileSaveName, FileFormat:=xlTextWindows, _ CreateBackup:=False strFname = ActiveWorkbook.FullName strFnameClean = Replace(ActiveWorkbook.FullName, ".txt", "clean.txt") MsgBox "Your SNSCA configuration upload file has been " _ & "successfully created at: " & vbCr & vbCr & strFname Call Test(strFname, strFnameClean) End Sub Sub Test(ByVal strFname, ByVal strFnameClean) Const ForReading = 1 Const ForWriting = 2 Dim objFSO As Object Dim objTF As Object Dim strAll As String Dim varTxt Dim lngRow As Long Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTF = objFSO.OpenTextFile(strFname, ForReading) strAll = objTF.readall objTF.Close Set objTF = objFSO.createTextFile(strFnameClean, ForWriting) varTxt = Split(strAll, vbCrLf) For lngRow = LBound(varTxt) To UBound(varTxt) - 1 objTF.writeline varTxt(lngRow) Next objTF.Close End Sub
这篇关于Excel VBA导出到文本文件。需要删除空行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文