将excel文件转换为UTF-8.TSV

Question

每周我都要将25个或更多的excel文件转换为UTF-8.tsv文件。到目前为止，我使用Editplus应用程序手动执行此操作。

我在谷歌和其他网站上搜索，我只得到UTF-8.csv转换。

有没有一种自动化的方法？

Answer 1

Try，

Sub test()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim rng As Range
Dim myfile As String

'If you need a loop, start here
Set Wb = ActiveWorkbook
Set Ws = Wb.Sheets(1)
Set rng = Ws.UsedRange
myfile = "testTab.txt"

TransToTab myfile, rng
'Loop end
End Sub
Sub TransToTab(myfile As String, rng As Range)
Dim vDB, vR() As String, vTxt()
Dim i As Long, n As Long, j As Integer
Dim objStream
Dim strTxt As String, strHeader As String
Set objStream = CreateObject("ADODB.Stream")
vDB = rng
For i = 1 To UBound(vDB, 1)
n = n + 1
ReDim vR(1 To UBound(vDB, 2))
For j = 1 To UBound(vDB, 2)
vR(j) = vDB(i, j)
Next j
ReDim Preserve vTxt(1 To n)
vTxt(n) = Join(vR, vbTab)
Next i
strTxt = Join(vTxt, vbCrLf)
With objStream
.Charset = "utf-8"
.Open
.WriteText strTxt
.SaveToFile myfile, 2
.Close
End With
Set objStream = Nothing
End Sub