xml - 处理带有外语字符的文本文件或字符串变量

Question

我想使用 VBA 函数，例如 LCase$() 和下一个 UCase() 以及我的 UTF-8 编码的 test.xml 文件。下面的示例代码加载了包含 UTF-8 内容的文件:

Dim objFileSystem, objInputFile

Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objInputFile = 
objFileSystem.OpenTextFile("c:\test.xml", 1)

inputData = objInputFile.ReadAll

objInputFile.Close

现在我尝试将内容转换为小写，然后将第一个字母更改为大写

Var = inputData
Var = LCase$(Var)

Select Case Len(Var)

Case 0
CapitilizeFirstLetter = ""

Case 1
CapitilizeFirstLetter = UCase(Var)

Case Else
CapitilizeFirstLetter = Ucase(Left(Var, 1)) & mid(Var, 2)

End Select

我尝试将文件内容保存在名称 test_edited.xml 下

FileUrl = "c:\test_edited.xml"

Set objStream = CreateObject("ADODB.Stream")
With objStream
    .Open
    .Charset = "utf-8" 'rosyjski iso-8859-5
    .Position = objStream.Size
    .WriteText=Var
    .Flush
    .Position = 0
    .Type     = 1 'binary
    .Read(3)      'skip BOM
    .SaveToFile FileUrl,2
    .Close
End With
Set objStream = Nothing

结果，第一个文件的内容是:

Nejznámější ŽENY, MODELY, herečka, zpěvačka

第二个是现在

Nejznámější ŽENY, MODELY, herečka, zpěvačka

我希望它看起来像那样

Nejznámější ženy, modely, herečka, zpěvačka

我做错了什么？

我使用的是 Basic IDE 6.4 版。

所有代码应该如下所示

Sub Main

'getting variable from outside
ChanNum = DDEInitiate("MacroEngine", "MacroGetVar")
Var$ = DDERequest$(ChanNum, "vChannelOpisA")
    DDETerminate ChanNum


Var = LCase$(Var) ' converting utf-8 encoded string to lower case

'change first letter to upper case
Select Case Len(Var)

Case 0
CapitilizeFirstLetter = ""

Case 1
CapitilizeFirstLetter = UCase(Var)

Case Else
CapitilizeFirstLetter = Ucase(Left(Var, 1)) & mid(Var, 2)

End Select

'sending variable to outside of vb script
ChanNum = DDEInitiate("MacroEngine","MacroSetVar")
Var = "vChannelOpisA=" + CapitilizeFirstLetter
DDEExecute (ChanNum, Var)
DDETerminate ChanNum

End Sub

名为Var 的变量最终应该是utf-8 编码以将它们写入xml 文件。我也可以从文件中读取字符串，而不是使用 DDERequest 获取它们。

Answer 1

经过 3 天的努力和研究，我终于搞定了。如您所见，我创建宏的程序在 windows-1250 字符集下运行。所以我必须先将我的字符串转换为 utf-8，最后再转换回 windows-1250。下面给出了适合我的正确代码。

Sub Main

' Retriving variable from outside vba
ChanNum = DDEInitiate("MacroEngine", "MacroGetVar")
Var$ = DDERequest$(ChanNum, "vChannelOpisA")
DDETerminate ChanNum


Dim objStream As Object

' Converting string variable from Windows-1250 to utf-8
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Type     = 2 'Specify stream type text data.
objStream.Charset  = "WIndows-1250" 'Specify charset For the source text data.
objStream.WriteText Var
objStream.Position = 0
objStream.Charset  = "utf-8"
Var = objStream.ReadText
objStream.Close


' Processing the string characters to lower case and change first letter to upper case
Var = LCase$(Var)

Select Case Len(Var)
Case 0
CapitilizeFirstLetter = ""
Case 1
CapitilizeFirstLetter = UCase(Var)
Case Else
CapitilizeFirstLetter = Ucase(Left(Var, 1)) & mid(Var, 2)
End Select

' Converting edited string back to WIndows-1250.
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Type     = 2 'Specify stream type text data.
objStream.Charset  = "utf-8" 'Specify charset For the source text data.
objStream.WriteText CapitilizeFirstLetter
objStream.Position = 0
objStream.Charset  = "WIndows-1250"
CapitilizeFirstLetter = objStream.ReadText
objStream.Close

' Sending string variable to my Macro engine
ChanNum = DDEInitiate("MacroEngine","MacroSetVar")
Var = "vChannelOpisA=" + CapitilizeFirstLetter
DDEExecute (ChanNum, Var)
DDETerminate ChanNum

End Sub

这里有一个例子，如果你想从文件中为变量加载数据。

Sub Main

sFileToEdit = "c:\test.xml"
sFileEdited = "c:\test_edited.xml"


Dim objStream As Object

Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 2 'Specify stream type - we want To save text/string data.
objStream.Charset = "utf-8" 'Specify charset For the source text data.
objStream.Open 'Open the stream And write binary data To the object
objStream.LoadFromFile sFileToEdit
ReadFileData = objStream.ReadText
objStream.Close

ReadFileData = LCase$(ReadFileData)

Select Case Len(ReadFileData)
Case 0
CapitilizeFirstLetter = ""
Case 1
CapitilizeFirstLetter = UCase(ReadFileData)
Case Else
CapitilizeFirstLetter = Ucase(Left(ReadFileData, 1)) & mid(ReadFileData, 2)
End Select


Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 2 'Specify stream type - we want To save text/string data.
objStream.Charset = "utf-8" 'Specify charset For the source text data.
objStream.Open 'Open the stream And write binary data To the object
objStream.WriteText CapitilizeFirstLetter
objStream.SaveToFile sFileEdited, 2 'Save binary data To disk
objStream.Close

End Sub