VBA存储文件为UTF-8编码

将生产日报的EXCEL数据读出并利用EXCEL读取数据并生成SQL文件,但遇到编码问题,数据库使用的是UTF8编码,而EXCEL生成的文件是根据系统生成的ASICII格式。

'函数名称:ReadTextFile
'作用:利用Adodb.Stream对象来读取UTF-8编码的文件
'示例:Call ReadFromTextFile("File/FileName.htm","UTF-8")
Function ReadFromTextFile(FileUrl,CharSet)
    dim str
    set stm=server.CreateObject("adodb.stream")
    stm.Type=2            '2-文本模式读取,1-二进制模式
    stm.mode=3            '3-读写,1-读,2-写
    stm.charset=CharSet   ‘unicode|utf-8;Ascii; gb2312; big5; gbk
    stm.open
    stm.loadfromfile server.MapPath(FileUrl)
    str=stm.readtext
    stm.Close
    set stm=nothing
    ReadFromTextFile=str
End Function

'函数名称:WriteToTextFile
'作用:利用Adodb.Stream对象来写入UTF-8编码的文件
'示例:Call WriteToTextFile("File/FileName.htm",Content,"UTF-8")
Sub WriteToTextFile(FileUrl,byval Str,CharSet)
    set stm=server.CreateObject("adodb.stream")
    stm.Type=2             '2-文本模式读取,1-二进制模式
    stm.mode=3             '3-读写,1-读,2-写
    stm.charset=CharSet    'unicode|utf-8;Ascii; gb2312; big5; gbk;
    stm.open
    stm.WriteText str
    stm.SaveToFile server.MapPath(FileUrl),2 '2可省略,adSaveCreateNotExist =1 , adSaveCreateOverWrite =2
    stm.flush
    stm.Close
    set stm=nothing
End Sub

文件转换格式的代码

Private Sub ConvFile(InputFile As String, OutputFile As String)
    Dim ReadStream As Object
    Set ReadStream = CreateObject("ADODB.Stream")
    Dim FileContent As String
    With ReadStream
        .Type = 2               'adTypeText
        .Charset = "UNICODE"
        .Open
        .LoadFromFile InputFile
        FileContent = .ReadText
        .Close
    End With

    Set ReadStream = Nothing
    Dim WriteStream As Object
    Set WriteStream = CreateObject("ADODB.Stream")
    With WriteStream
        .Type = 2               'adTypeText
        .Charset = "UTF-8"
        .Open
        .WriteText FileContent
        .SaveToFile OutputFile, 2  'adSaveCreateOverWrite
        .Flush
        .Close
    End With
    Set WriteStream = Nothing
End Sub

 

赞(0) 打赏
未经允许,不得转载本站任何文章:不忘初心的知与行 » VBA存储文件为UTF-8编码

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏