【WPS】表格使用VBA宏编程写入ini文件实现软件多语言

前言：

公司软件最近在做多语言版本，而又来一个西班牙文版本的，之前已经做过中文版本，英文版本和法文版本，之前是同事做的，现在安排我做，之前的做法，使用wps表格，翻译好，然后一个一个复制粘贴到ini文件里面，然后在软件里面读取。这种做法，后期维护起来挺麻烦的，所以自己研究了表格宏。之前也没接触过VB，所以只能自己网上学习

 

首先先下载VBA模块，下载链接 VBA 7.0

 

• 首先学习下VB语言的基本语法

定义变量

Dim i As Integer
Dim str As String

输出语句

Debug.Print "导出成功"

弹框

MsgBox "弹框"

打开视图--立即窗口查看输出

循环语句

for循环

For i = 0 To 5
    Debug.Print i
Next i

while循环

Dim i As Integer
i = 0

While i < 5
    Debug.Print i
    i = i + 1
Wend

条件语句

if

If 1 = 1 Then

End If

if else 

If 1 = 1 Then

Else

End If

if   else if    else

If 1 = 1 Then

ElseIf 2 = 2 Then

Else

End If

字符串连接

str = strA & strB

不等于使用<>

 

 

• 新建xlsm格式的表格，只有xlsm格式的文件才能保存宏

• 开发工具--宏，新建宏

 

• 编写代码

首先需要读写ini文件的接口

Private Declare Function GetPrivateProfileString Lib "Kernel32" _
    Alias "GetPrivateProfileStringA" _
    (ByVal lpApplicationName As String, _
    ByVal Key As Any, _
    ByVal Value As String, _
    ByVal ReturnedString As String, _
    ByVal Size As Long, _
    ByVal FilePath As String) As Long
Private Declare Function WritePrivateProfileString Lib "Kernel32" _
    Alias "WritePrivateProfileStringA" _
    (ByVal Section As String, _
    ByVal Key As Any, _
    ByVal Value As Any, _
    ByVal FilePath As String) As Long

进一步封装接口

Public Function ReadFromIni(ByVal Section As String, ByVal Key As String, ByVal Value As String, ByVal FilePath As String) As String
Dim str As String
str = Space(256)
Dim ln As Long
ln = GetPrivateProfileString(Section, Key, Value, str, 255, FilePath)
If ln > 0 Then
str = Trim(str)
ReadFromIni = Mid(str, 1, Len(str) - 1)
Else
ReadFromIni = Value
End If
End Function
Public Function WriteToIni(ByVal Section As String, ByVal Key As String, ByVal Value As String, ByVal FilePath As String) As String
Dim l As Long
    l = WritePrivateProfileString(Section, Key, Value, FilePath)
End Function

读取表格数据写入ini文件

Sub ExcelToIni()
'ini文件的路径
Dim strIniFile As String
strIniFile = "C:\Users\VULCAN\Desktop\Lang.ini"

'xls表格文件
Dim ExApp As New Excel.Application
'工作簿
Dim Exb As Excel.Workbook
'工作表
Dim Exsh As Excel.Worksheet

ExApp.Workbooks.Open "C:\Users\VULCAN\Desktop\Lang.xlsm"
Set Exb = ExApp.Workbooks(1)
Set Exsh = Exb.Worksheets("Sheet1")

'数据总数
Dim count As Integer
count = Exsh.UsedRange.Rows.count
Debug.Print count

'Value 5表示中文 6表示英文 7表示法文 8表示西班牙文
Dim langCol As Integer
langCol = 7

Dim i As Integer
i = 4
While i < count
    If Exsh.Cells(i, 4).Value <> "" Then
        a = WriteToIni(Exsh.Cells(i, 3).Value, Exsh.Cells(i, 4).Value, Exsh.Cells(i, langCol).Value, strIniFile)
    End If
    i = i + 1
Wend


Debug.Print "导出成功"

End Sub

上面三部分代码就是全部代码

 

下面为测试数据

中文版本

英文版本

法文版本