Excel VBA导出为文本文件。需要删除空白行
问题描述:
我有一个工作簿,可以使用下面的脚本导出到文本文件。它工作正常,但是当我打开文本文件时,总会有一个空白行,导致我在生成此文本文件后运行的另一个脚本出现问题。任何帮助都可以从我的导出中删除空白行。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:\temp. 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
答
虽然我upticked从让·弗朗索瓦·科贝特您的评论可以使用下面的这个VBA删除你的txt文件的最后一行(正如你所说的,当以这种方式保存时写入一个空白行)。
该VBA基于常用的vbscript例程。它
- 读取新创建的文本文件,例如(* SNSCA_Customer_01092012.txt *)
- 分裂连成一条线
-
然后重写除了最后的所有行到一个新的txt文件(* SNSCA_Customer_01092012clean.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:\temp. 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
这可能会发生在给定的输入这么多的理由练成文档。但是,您可以在保存并删除多余的行后立即读取文件。 – 2012-01-05 18:38:56
我想说你的另一个脚本太敏感了......改变它,使它不会在尾随的空行上窒息! – 2012-01-05 18:57:09
我试图改变VBA导出活动工作表的方式,但这似乎并不奏效。我无法获得下面的变量exporttxt来保存活动工作表的所有内容,比如上面发布的saveas。 – user1132827 2012-01-05 19:30:38