使用XLSXApp对象管理XLSX文件的工作表
打开VB6工程，添加窗体和按钮
[表数]按钮
函数
GetSheetTotal -- 得到工作表数量
代码
Private Sub cmdGetSheetTotal_Click()
Dim DoXLSX As XLSXApp
Dim Error As String
Set DoXLSX = New XLSXApp
'打开XLSX文件
If DoXLSX.Open("Test1.xlsx", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
'得到表数
MsgBox "工作表数：" & DoXLSX.GetSheetTotal
'关闭XLSX文件
DoXLSX.Close
Set DoXLSX = Nothing
End Sub
运行效果
[表名]按钮
函数
GetSheetName -- 得到指定序号的工作表的表名
代码
Private Sub Command1_Click()
Dim DoXLSX As XLSXApp
Dim SheetName As String
Dim Error As String
Set DoXLSX = New XLSXApp
'打开XLSX文件
If DoXLSX.Open("Test1.xlsx", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
'得到表名
If DoXLSX.GetSheetName(1, SheetName, Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
MsgBox "第一个工作表名：" & SheetName
'关闭XLSX文件
DoXLSX.Close
Set DoXLSX = Nothing
End Sub
运行效果
[添加表]按钮
函数
AddSheet -- 添加一个工作表到最后位置
代码
Private Sub Command2_Click()
Dim DoXLSX As XLSXApp
Dim SheetName As String
Dim Error As String
Set DoXLSX = New XLSXApp
'打开XLSX文件
If DoXLSX.Open("Test1.xlsx", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
'添加表
If DoXLSX.AddSheet("NewSheet", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
DoXLSX.Save
'关闭XLSX文件
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox "操作成功！"
End Sub
运行效果
[删除表]按钮
函数
DelSheet -- 删除一个工作表
代码
Private Sub Command3_Click()
Dim DoXLSX As XLSXApp
Dim SheetName As String
Dim Error As String
Set DoXLSX = New XLSXApp
'打开XLSX文件
If DoXLSX.Open("Test1.xlsx", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
'删除表
If DoXLSX.DelSheet("NewSheet", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
DoXLSX.Save
'关闭XLSX文件
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox "操作成功！"
End Sub
运行效果
[修改表]按钮
函数
RenameSheet -- 修改一个工作表的表名
代码
Private Sub Command4_Click()
Dim DoXLSX As XLSXApp
Dim SheetName As String
Dim Error As String
Set DoXLSX = New XLSXApp
'打开XLSX文件
If DoXLSX.Open("Test1.xlsx", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
'修改表
If DoXLSX.RenameSheet("Sheet1", "First", Error) = False Then
MsgBox Error
Set DoXLSX = Nothing
Exit Sub
End If
DoXLSX.Save
'关闭XLSX文件
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox "操作成功！"
End Sub
运行效果
