使用XLSXApp对象修改和读取XLSX文件单元格的公式
设置公式
添加按钮
双击添加代码
Private Sub Command6_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
【操作XLSX文件单元格的公式-SystemExcel库使用教程】End If
'选择Sheet1
If DoXLSX.SelSheet("Sheet1", Error) = False Then
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox Error
Exit Sub
End If
'设置公式
DoXLSX.SetCellFormula 2, 1, "=A1"
'关闭XLSX文件
DoXLSX.Save
DoXLSX.Close
End Sub
按单元格名称设置公式
添加按钮
双击按钮输入代码
Private Sub Command7_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
'选择Sheet1
If DoXLSX.SelSheet("Sheet1", Error) = False Then
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox Error
Exit Sub
End If
'设置公式
DoXLSX.SetCellFormula2 "B2", "=A2"
'关闭XLSX文件
DoXLSX.Save
DoXLSX.Close
End Sub
读取公式
添加按钮
双击输入代码
Private Sub Command8_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
'选择Sheet1
If DoXLSX.SelSheet("Sheet1", Error) = False Then
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox Error
Exit Sub
End If
'读取公式
MsgBox "B1单元格的公式是：" & DoXLSX.GetCellFormula(2, 1)
'关闭XLSX文件
DoXLSX.Close
End Sub
按单元格名称读取公式
添加按钮
双击输入代码
Private Sub Command9_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
'选择Sheet1
If DoXLSX.SelSheet("Sheet1", Error) = False Then
DoXLSX.Close
Set DoXLSX = Nothing
MsgBox Error
Exit Sub
End If
'读取公式
MsgBox "B2单元格的公式是：" & DoXLSX.GetCellFormula2("B2")
'关闭XLSX文件
DoXLSX.Close
End Sub
