Excell vba实现对IOS和Android多国语言本地化的统一管理

excel 添加按钮 


 视图==>工具栏==>Visual Basic 在出现的工具栏上,点"控件工具箱" 的图标。
 
 
 
 excell 获取单元格内容
 最好在range()后面加上“.value”，例如range("a1").value
 
 
 判定字符串是否为空 
if len(T) then 




VBA添加引用
VBA编辑工具中，选择"工具“=》引用






Private Sub Éú³ÉAndroidÓïÑÔ°ü_Click()
   RootPath = ThisWorkbook.path
   FilePath = Trim(TextBox2.Text) + "/res/"
   ExeclXmlWrite "B", RootPath, FilePath + "values", "strings"
   ExeclXmlWrite "C", RootPath, FilePath + "values-zh-rtw", "strings"
   ExeclXmlWrite "D", RootPath, FilePath + "values-zh-rcn", "strings"
   MsgBox "Result For Android Path:" + FilePath
   
End Sub


Private Sub Éú³ÉIOSÓïÑÔ°ü_Click()
    RootPath = ThisWorkbook.path
    FilePath = Trim(TextBox1.Text) + "/local-string"
    ExeclXmlWrite "B", RootPath, FilePath, "strings-en"
    ExeclXmlWrite "C", RootPath, FilePath, "strings-tw"
    ExeclXmlWrite "D", RootPath, FilePath, "strings-cn"
    MsgBox "Result For IOS Path:" + FilePath
    
End Sub


Private Sub CreateFolder(ByVal RootPath As String, ByVal path As String)
    Dim fs As New FileSystemObject
    Dim PathStr As String
    Dim Val() As String
    PathStr = Replace(path, "\", "/")
    Val = Split(PathStr, "/")
    NewPath = RootPath + "/"
    For n = LBound(Val) To UBound(Val)
        NewPath = NewPath + Val(n) + "/"
        If Not fs.FolderExists(NewPath) Then
         fs.CreateFolder NewPath
        End If
    Next
    
End Sub


Public Sub ExeclXmlWrite(ByVal ColName As String, ByVal RootPath As String, ByVal FilePath As String, ByVal FileName As String)
    CreateFolder RootPath, FilePath
    Dim xmlDoc As New MSXML2.DOMDocument
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set rootNode = xmlDoc.createElement("resources")
    Set xmlDoc.documentElement = rootNode
    rootNode.appendChild xmlDoc.createTextNode(vbCrLf)
    rootNode.appendChild xmlDoc.createTextNode(Space$(4))
    Set Header = xmlDoc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
    xmlDoc.insertBefore Header, xmlDoc.childNodes(0)
    For Row = 1 To 2000
        RowStr = Trim(Str(Row))
        PosKey = Trim("A" + RowStr)
        PosValue = Trim(ColName + RowStr)
        key = Range(PosKey).Value
        Value = Range(PosValue).Value
       
        If Len(key) > 0 And Len(Value) > 0 Then
           
             Set Tag = xmlDoc.createElement("string")
            Tag.setAttribute "name", key
            Tag.Text = Value
            rootNode.appendChild Tag
           
           rootNode.appendChild xmlDoc.createTextNode(vbCrLf)
           rootNode.appendChild xmlDoc.createTextNode(Space$(4))
        Else
             If Len(key) Or Len(Value) Then
                 Debug.Print "Key:" + key + "     Value:" + Value + "       Len(Key):" + Str(Len(key)) + "     Len(Value):" + Str(Len(Value))
             End If
           
        End If
    
    Next
    rootNode.appendChild xmlDoc.createTextNode(vbCrLf)
    xmlDoc.Save (RootPath + FilePath + "/" + FileName + ".xml")
End Sub