vba如何把表格处理到txt（VBA实例Word）

Option Explicit Public Const INPUT_PATH As String = "E:\temp\文件\word\" ' 要处理的文件所在 Public Const OUTPUT_PATH As String = "E:\temp\文件\txt\" ' 存完成文件的目录名 Public Const ERR_LOG_FILE As String = "E:\temp\文件\errLog.log" ' 错误日志 Sub Docx2txt() On Error GoTo ErrorHandler Dim currFile As Variant Dim currDoc As Document Application.ScreenUpdating = False Application.DisplayAlerts = False currFile = Dir(INPUT_PATH) ' ------------- 遍历目录中的文件 开始 ------------- Do Until currFile = "" If Right(currFile, 5) = ".docx" Or Right(currFile, 4) = ".doc" Then Set currDoc = Word.Documents.Open(FileName:=INPUT_PATH & currFile, Visible:=False) currDoc.SaveAs FileName:=OUTPUT_PATH & Split(currFile, ".")(0) & ".txt", FileFormat:=wdFormatText currDoc.Close End If NextFile: currFile = Dir() Loop Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub ErrorHandler: errlog "【错误文件】" & currFile & " " & Err.Number & ":" & Replace(Err.Description, vbLf, " vbCrLf ") Resume NextFile End Sub Sub errlog(logMsg As String) Shell "cmd.exe /c echo " & Format(Now, "YYYY-MM-DD HH:MM:SS") & " " & logMsg & " >> " & ERR_LOG_FILE, vbHide End Sub ，下面我们就来说一说关于vba如何把表格处理到txt?我们一起去了解并探讨一下这个问题吧!

vba如何把表格处理到txt

Option Explicit Public Const INPUT_PATH As String = "E:\temp\文件\word\" ' 要处理的文件所在 Public Const OUTPUT_PATH As String = "E:\temp\文件\txt\" ' 存完成文件的目录名 Public Const ERR_LOG_FILE As String = "E:\temp\文件\errLog.log" ' 错误日志 Sub Docx2txt() On Error GoTo ErrorHandler Dim currFile As Variant Dim currDoc As Document Application.ScreenUpdating = False Application.DisplayAlerts = False currFile = Dir(INPUT_PATH) ' ------------- 遍历目录中的文件 开始 ------------- Do Until currFile = "" If Right(currFile, 5) = ".docx" Or Right(currFile, 4) = ".doc" Then Set currDoc = Word.Documents.Open(FileName:=INPUT_PATH & currFile, Visible:=False) currDoc.SaveAs FileName:=OUTPUT_PATH & Split(currFile, ".")(0) & ".txt", FileFormat:=wdFormatText currDoc.Close End If NextFile: currFile = Dir() Loop Application.ScreenUpdating = True Application.DisplayAlerts = True Exit Sub ErrorHandler: errlog "【错误文件】" & currFile & " " & Err.Number & ":" & Replace(Err.Description, vbLf, " vbCrLf ") Resume NextFile End Sub Sub errlog(logMsg As String) Shell "cmd.exe /c echo " & Format(Now, "YYYY-MM-DD HH:MM:SS") & " " & logMsg & " >> " & ERR_LOG_FILE, vbHide End Sub

遇到两种错误:

1. 6296: Word 无法打开该文件，因为文件格式与文件扩展名不匹配。
2. 5792: 文件可能已经损坏。

2022-05-04 07:07:14 【错误文件】a.docx 6296:Word 无法打开该文件，因为文件格式与文件扩展名不匹配。 2022-05-04 07:14:35 【错误文件】b.docx 5792:文件可能已经损坏。

分析错误日志，先把错误的文件复制出来。Notepad 使用正则替换一下，查找 (. ).docx替换为 $1.docx $1.doc。（注意开头的空格）从当前文件夹下的 word\ 复制到 error\

echo F | xcopy %~dp0word\a.docx %~dp0error\a.doc /Y echo F | xcopy %~dp0word\b.docx %~dp0error\b.doc /Y

经过分析 WPS 可以打开。我们把所有此类文件，另存一份。就可以处理了。