Set wb2 = Workbooks.Open("C:\\Users\\HP\\Desktop\\已处理新增.xlsm")报错为对象未定义

如果在 VBA 中出现“对象未定义”的错误，通常是因为代码中使用了未声明或未赋值的变量。在这段代码中，如果变量 `wb2` 在此之前没有声明或赋值，就会出现该错误。

为了解决这个问题，你可以在使用变量 `wb2` 之前声明它，并且在使用 `Workbooks.Open` 方法之前确保 Excel 应用程序处于活动状态。你可以将代码修改为以下内容：

Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Users\HP\Desktop\已处理新增.xlsm")

在这段修改后的代码中，变量 `wb2` 在使用之前被声明，并且使用了完整的文件路径，而不是使用两个反斜杠来表示一个。此外，你需要确保在使用 `Workbooks.Open` 方法之前使用 `Application.Activate` 方法将 Excel 应用程序激活。

相关问题

Set WB = Workbooks.Open(Path & File)

这行代码是VBA（Visual Basic for Applications）中用于打开Excel工作簿的语句。让我来详细解释一下：

1. `Set` 关键字：用于将对象引用赋给一个变量。

2. `WB`：这是变量名，用于存储打开的工作簿对象。

3. `Workbooks.Open`：这是一个方法，用于打开一个Excel工作簿。

4. `Path & File`：这是一个字符串组合，`Path` 和 `File` 是两个变量，分别表示文件路径和文件名。`&` 符号用于将这两个字符串连接起来，形成完整的文件路径。

完整的解释：
这行代码的作用是使用指定路径和文件名打开一个Excel工作簿，并将打开的工作簿对象赋值给变量 `WB`。这样，我们就可以通过 `WB` 变量来操作这个打开的工作簿了。

例如：

Dim WB As Workbook
Dim Path As String
Dim File As String

Path = "C:\Users\Example\Documents\"
File = "Sample.xlsx"

Set WB = Workbooks.Open(Path & File)

' 现在可以使用 WB 变量来操作打开的工作簿
WB.Sheets(1).Range("A1").Value = "Hello, World!"

WB.Close SaveChanges:=True

优化以下代码，使他在对比数据只有一行的情况下就按代码逻辑复制并粘贴那一行数据Sub CopySameDay() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim copyRange As Range Dim pasteRange As Range Dim wb As Workbook Dim folderPath As String Dim fileName As String Dim asteRange As Range Set ws = ActiveSheet lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = 2 To lastRow If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then If copyRange Is Nothing Then Set copyRange = ws.Range("A" & i - 1) End If Set pasteRange = ws.Range("A" & i) Else If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set asteRange = ws.Range("B" & i - 1) fileName = asteRange.Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A1").PasteSpecial xlPasteValues wb.Save wb.Close Set copyRange = Nothing End If End If Next i If Not copyRange Is Nothing Then folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) Set asteRange = ws.Range("B" & i - 1) fileName = asteRange.Value & ".xlsx" If Dir(folderPath & fileName) = "" Then Set wb = Workbooks.Add wb.SaveAs folderPath & fileName Else Set wb = Workbooks.Open(folderPath & fileName) End If wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy wb.Sheets(wb.Sheets.Count).Range("A1").PasteSpecial xlPasteValues wb.Save wb.Close End If End Sub

Sub CopySameDay()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim copyRange As Range
Dim pasteRange As Range
Dim wb As Workbook
Dim folderPath As String
Dim fileName As String
Dim pasteSheet As Worksheet

Set ws = ActiveSheet
lastRow = ws.Cells(Rows.Count, "D").End(xlUp).Row

For i = 2 To lastRow
If Format(ws.Range("D" & i).Value, "yyyy-mm-dd") = Format(ws.Range("D" & i - 1).Value, "yyyy-mm-dd") And ws.Range("B" & i).Value = ws.Range("B" & i - 1).Value Then
If copyRange Is Nothing Then
Set copyRange = ws.Range("A" & i - 1)
End If
Set pasteRange = ws.Range("A" & i)
Else
If Not copyRange Is Nothing Then
folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "\" ' Add "\" at the end of folder path

Set pasteSheet = Nothing ' Set default value for pasteSheet
On Error Resume Next ' Try to set pasteSheet as the worksheet with the same date
Set pasteSheet = wb.Sheets(Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd"))
On Error GoTo 0 ' Reset error handling

If pasteSheet Is Nothing Then ' If the worksheet with the same date doesn't exist, create one
Set wb = Workbooks.Add
wb.SaveAs folderPath & Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") & ".xlsx"
Set pasteSheet = wb.Sheets(1)
Else ' If the worksheet with the same date exists, use it
Set wb = pasteSheet.Parent
End If

copyRange.Resize(i - copyRange.Row, ws.Columns.Count).Copy
pasteSheet.Range("A" & pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
wb.Save
wb.Close
Set copyRange = Nothing
End If
End If
Next i

If Not copyRange Is Nothing Then
folderPath = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, "\")) & "\" ' Add "\" at the end of folder path

Set pasteSheet = Nothing ' Set default value for pasteSheet
On Error Resume Next ' Try to set pasteSheet as the worksheet with the same date
Set pasteSheet = wb.Sheets(Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd"))
On Error GoTo 0 ' Reset error handling

If pasteSheet Is Nothing Then ' If the worksheet with the same date doesn't exist, create one
Set wb = Workbooks.Add
wb.SaveAs folderPath & Format(copyRange.Offset(0, 3).Value, "yyyy-mm-dd") & ".xlsx"
Set pasteSheet = wb.Sheets(1)
Else ' If the worksheet with the same date exists, use it
Set wb = pasteSheet.Parent
End If

copyRange.Resize(lastRow - copyRange.Row + 1, ws.Columns.Count).Copy
pasteSheet.Range("A" & pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
wb.Save
wb.Close
End If
End Sub