[已解决]VBA更改txt文件“编码格式”为UTF-8？18楼附答谢文件

winland

这是一个转文本为UTF-8文件的例子。在另一个帖子里说过的。

Public Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByRef lpMultiByteStr As Any, ByVal cchMultiByte As Long, ByVal lpDefaultChar As String, ByVal lpUsedDefaultChar As Long) As Long
Public Const CP_UTF8 = 65001
Sub writetoUTF8()
    Dim strstr As String
    Dim bByte As Byte
    Dim lBufSize As Long
    Dim lRest As Long
    Dim bUTF8() As Byte
    Dim TLen As Long
   
    strstr = "大家好,这里是转换Unicode和UTF-8的代码."
   
    TLen = Len(strstr)
    lBufSize = TLen * 3 + 1
    ReDim bUTF8(lBufSize - 1)
    lRest = WideCharToMultiByte(CP_UTF8, 0, StrPtr(strstr), TLen, bUTF8(0), lBufSize, vbNullString, 0)
    If lRest Then
        lRest = lRest - 1
        ReDim Preserve bUTF8(lRest)
        Open "c:\xfwen\excelhelp\UTF8try.txt" For Binary As #1
        bByte = 239
        Put #1, , bByte
        bByte = 187
        Put #1, , bByte
        bByte = 191
        Put #1, , bByte
        Put #1, , bUTF8
        Close #1
    End If
End Sub

sjzhouah

还是复杂问题简单化吧：

已经使用excelVBA创建了一个文件"d:\mydata.txt"

但是这个文件是使用ANSI编码的

我需要将其改变成为使用UTF8编码的文件，文件名、内容都不变

请问如何写VBA代码？

Excel在导入txt文件时可选择打开文件的编码方式。

qdofficer

实际上卧美吻花和我遇到的是同一个问题——不是从文本文件导入EXCEL，而是根据EXCE文件中的数据写一个xml文件。要求UTF-8编码。
winland兄的代码是管用的，虽然我还看不懂 ，仍要多谢。

mrqq18

呵呵,旧社会的贴子,仍然有看头!

x-xx-xx

stanleypan 发表于 2006-11-8 16:45
用API就可以解决.就象是内码转换一样的道理.Private Declare Function MultiByteToWideChar Lib "kernel32" ...

亲测可用
2018年04月18日12时17分25秒

x-xx-xx

northwolves 发表于 2006-11-7 11:15
Application.DefaultWebOptions.Encoding = msoEncodingUTF8

2018年04月18日12时21分54秒
亲测无效

x-xx-xx

lotustower 发表于 2006-11-7 11:28
試試 轉化 UTF-8 Private Function GetUTF8String(s As String) As String    Dim i As Int ...

2018年04月18日12时25分36秒
亲测无用

x-xx-xx

2018年04月18日12时26分25秒
完整代码

1. Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
   
2. Private Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
   
3. Private Const CP_UTF8 = 65001
   
4. 
   
5. Sub lksf()
   
6.     Dim 文件名 As String
   
7.     文件名 = "C:\Users\Administrator.USER-20171209CD\Desktop\新建文本文档.txt"
   
8.     Const ForReading = 1, ForWriting = 2, ForAppending = 8
   
9.     Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   
10.     Dim 对象_文件_1, 文件, 记事本
    
11.     Set 对象_文件_1 = CreateObject("Scripting.FileSystemObject")
    
12.     对象_文件_1.CreateTextFile 文件名     ' Create a file.
    
13.     Set 文件 = 对象_文件_1.GetFile(文件名)
    
14.     Set 记事本 = 文件.OpenAsTextStream(ForWriting, TristateUseDefault)     '以系统默认编码写入文件
    
15.     For i = 1 To 1
    
16.         记事本.Write UTF8_Encode("汉字a")
    
17.     Next i
    
18.     记事本.Close
    
19. End Sub
    
20. 
    
21. Public Function UTF8_Encode(ByVal Text As String) As String
    
22.     Dim sBuffer As String
    
23.     Dim lLength As Long
    
24.     If Text <> "" Then
    
25.         lLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(Text), -1, 0, 0, 0, 0)
    
26.         sBuffer = Space$(lLength)
    
27.         lLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(Text), -1, StrPtr(sBuffer), Len(sBuffer), 0, 0)
    
28.         sBuffer = StrConv(sBuffer, vbUnicode)
    
29.         UTF8_Encode = Left$(sBuffer, lLength - 1)
    
30.     Else
    
31.         UTF8_Encode = ""
    
32.     End If
    
33. End Function

复制代码

a19410593

x-xx-xx 发表于 2018-4-18 12:26
2018年04月18日12时26分25秒
完整代码

在win10+office2010的环境下代码不能用。
Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
Private Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
Private Const CP_UTF8 = 65001


请问怎么解决上面的定义？

a19410593

sjzhouah 发表于 2006-12-17 11:07
还是复杂问题简单化吧：已经使用excelVBA创建了一个文件"d:\mydata.txt"但是这个文件是使用ANSI编码的我需 ...

楼主的问题有解决了吗？麻烦分享一下，谢谢。