使用XLSXApp对象修改XLSX文件，Sheet1的某些单元格的内容

打开工程，添加按钮，标题改成修改内容

双击按钮，添加如下代码

Private Sub Command4_Click()

Dim DoXLSX As XLSXApp

Dim MaxRow As Integer

Dim MaxCol As Integer

Dim i As Integer

Dim j As Integer

Dim Data_Before As String

Dim Data_After As String

Dim Error As String

Set DoXLSX = New XLSXApp

&39;打开XLSX文件

If DoXLSX.Open(&34;Test1.xlsx&34;, Error) = False Then

MsgBox Error

Set DoXLSX = Nothing

Exit Sub

End If

&39;选择Sheet1

If DoXLSX.SelSheet(&34;Sheet1&34;, Error) = False Then

DoXLSX.Close

Set DoXLSX = Nothing

MsgBox Error

Exit Sub

End If

&39;读取Excel行和列信息

MaxRow = DoXLSX.GetRowTotal

MaxCol = DoXLSX.GetColumnCount

If MaxRow = 0 Or MaxCol = 0 Then

DoXLSX.Close

Set DoXLSX = Nothing

MsgBox &34;没有数据！&34;

Exit Sub

End If

&39;读取修改前的数据

Data_Before = &34;&34;

For i = 1 To MaxRow

If i > 1 Then Data_Before = Data_Before & vbCrLf

For j = 1 To MaxCol

If j > 1 Then Data_Before = Data_Before & vbTab

Data_Before = Data_Before & DoXLSX.Rs(j, i)

Next

Next

&39;修改数据

DoXLSX.SetCellValue 1, 1, &34;你好AAA&34;

DoXLSX.SetCellValue 2, 2, 12.25

DoXLSX.SetCellValue 1, 3, 12.25

&39;读取Excel行和列信息

MaxRow = DoXLSX.GetRowTotal

MaxCol = DoXLSX.GetColumnCount

If MaxRow = 0 Or MaxCol = 0 Then

DoXLSX.Close

Set DoXLSX = Nothing

MsgBox &34;没有数据！&34;

Exit Sub

End If

&39;读取修改后的数据

Data_After = &34;&34;

For i = 1 To MaxRow

If i > 1 Then Data_After = Data_After & vbCrLf

For j = 1 To MaxCol

If j > 1 Then Data_After = Data_After & vbTab

Data_After = Data_After & DoXLSX.Rs(j, i)

Next

Next

&39;关闭XLSX文件

DoXLSX.SaveAs &34;Test2.xlsx&34;

DoXLSX.Close

Set DoXLSX = Nothing

MsgBox &34;修改前数据&34; & vbCrLf & Data_Before & vbCrLf & vbCrLf & &34;修改后数据&34; & vbCrLf & Data_After

End Sub

运行

Test2.xlsx文件内容