前言:
公司软件最近在做多语言版本,而又来一个西班牙文版本的,之前已经做过中文版本,英文版本和法文版本,之前是同事做的,现在安排我做,之前的做法,使用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
上面三部分代码就是全部代码
下面为测试数据
中文版本
英文版本
法文版本
---------------------