Dim selectedFilePath As String
Sub SelectFileAndSetGlobalVariable()
' 弹出选择文件的对话框
selectedFilePath = Application.GetOpenFilename("Excel Files (*.xlsm;*.xlsx), *.xlsm;*.xlsx")
' 检查用户是否真的选择了一个文件(没有点击“取消”)
If selectedFilePath <> "False" Then
' 用户选择了一个文件,全局变量selectedFilePath现在包含文件路径
'MsgBox "文件已选择,路径为:" & selectedFilePath
Else
' 用户点击了“取消”,全局变量selectedFilePath不会被设置
MsgBox "operation cancelled."
End If
End Sub
Sub CopyAndCompareSheetsUnits() '双表比较
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim currentSheet As Worksheet
Dim compareSheet As Worksheet
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim cellValueSource As Variant
Dim cellValueCurrent As Variant
Dim difference As Double
Dim cellValueSourceAsDouble As Double
Dim cellValueCurrentAsDouble As Double
Dim sheetName As Variant
sheetNames = Array("Dashboard1units")
Set wb = ThisWorkbook
If selectedFilePath <> "" Then
For Each sheetName In sheetNames
' 打开所选文件
Set sourceWorkbook = Workbooks.Open(selectedFilePath)
' 设置源Sheet和当前Sheet
Set sourceSheet = sourceWorkbook.Sheets(sheetName)
Set currentSheet = wb.Sheets(sheetName)
' 在当前工作簿中复制Sheet1并重命名为Sheet_Compare
currentSheet.Copy After:=wb.Sheets(wb.Sheets.Count) ' 指定在wb工作簿的最后一个工作表之后复制
Set compareSheet = ActiveSheet ' 此时ActiveSheet是wb工作簿中的新工作表
compareSheet.Name = sheetName & "_Compare" ' 重命名新工作表
' 获取两个Sheet的行数和列数
lastRow = 1100
lastCol = 31
' 遍历每个单元格并计算差异
For i = 1 To lastRow
For j = 1 To lastCol
cellValueSource = sourceSheet.Cells(i, j).value
cellValueCurrent = currentSheet.Cells(i, j).value
If IsEmpty(cellValueSource) Or IsEmpty(cellValueCurrent) Then
' 可以选择在这里将空单元格的值设置为某个默认值,或者什么都不做,直接跳过
Else
' 如果单元格是字符串类型,则直接赋值到比较Sheet
If IsNumeric(cellValueSource) And IsNumeric(cellValueCurrent) Then
' 如果不是字符串,则进行数值计算
difference = cellValueSource - cellValueCurrent
compareSheet.Cells(i, j).value = difference
End If
End If
Next j
Next i
Next sheetName
'sourceWorkbook.Close False ' 关闭源工作簿但不保存更改
Else
MsgBox "No file was selected."
End If
End Sub
VBA 一键计算两个表差值
最新推荐文章于 2024-06-25 11:16:39 发布