vba - Workbook.Open 后宏停止

Question

我正在尝试复制打开一个工作簿，复制一个范围并将其粘贴到另一个工作簿中。打开工作簿并选择宏停止的范围后。

如果我稍后在代码中使用 Workbook.close，宏会在同一点停止，但工作簿甚至没有打开。

我在其他线程中看到使用 Workbook.Open 时出现“shift key”问题，但事实并非如此。

Sub importSpecialist()


Dim ws As Worksheet
Set ws = Worksheets("myworksheet")

Dim lws As Integer
lws = ws.Range("B3").End(xlDown).Row

Dim savePath As String
Dim saveName As String
Dim saveMonth As String
Dim fileExtension As String
Dim fullPath As String
Dim version As Integer

Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")

savePath = "C:\mypath\"
saveName = "myname_"
saveMonth = Format(Date, "mm")
fileExtension = ".xlsx"
fullPath = savePath & saveName & saveMonth & fileExtension

Debug.Print fullPath

If FSO.fileexists(fullPath) Then
    Dim i As Integer
    Dim wb1 As Workbook
    Set wb1 = Workbooks.Open(fullPath)
    wb1.Sheets(1).Range("C4:C18").Copy
        For i = 1 To lws
            On Error Resume Next
            If ws.Range("B" & (i + 2)) = Format(Date, "mm/yyyy") Then
                ws.Cells(Range("N" & (i + 2))).PasteSpecial
            End If
        Next
End If
End Sub

Answer 1

尝试像这样打开它:

Dim xl As New Excel.Application
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
    xl.Workbooks.Open (Worksheets("MAIN").Range("B7").Value)//your path
    xl.Visible = False //or true
   //your code here
    xl.Quit
    Set xl = Nothing

''如果你要从中选择一个范围，请使用 xl.Worksheets().Range().Select