excel - VBA Open方法仅在崩溃后才有效

Question

我的代码基本上执行以下操作：

• 复制范围
• 创建一个新的工作簿
• 将范围粘贴到新工作簿中

• 打开另一个（现有）工作簿

  设置 YestWb = Workbooks.Open(arq_path & yest_name & ".xlsx")

• 等等

当它尝试打开另一个工作簿时，有时会引发以下错误：

运行时错误“1004”-对象“工作簿”的“打开”方法失败。

但是，当我再次单击“调试”和“运行”时，从它暂停的地方，它工作正常。知道为什么它只在崩溃后才起作用以及为什么这个错误有时只发生？

我搜索了有关此错误的类似主题，但找不到任何类似情况。任何帮助将不胜感激！

（编辑）这是代码：

Sub excel_ger(Optional auto As Boolean)

Application.ScreenUpdating = False

Set capa = Sheets("P&L_R")
Set fff = Application.WorksheetFunction
capa.Activate

'inputs
ult_est = "NDF_USD" 
colini = "B" 
colfim = "M" 
nome_ger_mae = "GerencialV34.xlsm"

'find last row
lRow = capa.Cells(capa.Rows.Count, "B").End(xlUp).Row

'define range to be copied
For i = 1 To lRow
    If capa.Range("G" & i).Value = "Dia" Then
        linini = i
        Exit For
    End If
Next i
For i = linini + 1 To lRow
    If capa.Range("B" & i).Value = ult_est Then
        linfim = i
    End If
Next i

'copy range
capa.Range(Cells(linini, colini), Cells(linfim, colfim)).Copy

'create new workbook
arq_path = "\\viseu\Gestao Tesouraria\Operadores\Lucca Cerf\3. Gerencial\Gerencial PropTrading\"
arq_name = "Gerencial PropTrading - " _
            & Year(Sheets("Painel").Range("dtref").Value) _
            & Format(Month(Sheets("Painel").Range("dtref").Value), "00") _
            & Format(Day(Sheets("Painel").Range("dtref").Value), "00")
            'nome da planilha D0 a ser criada
yest_name = "Gerencial PropTrading - " _
            & Year(Sheets("Painel").Range("dtrefd_1").Value) _
            & Format(Month(Sheets("Painel").Range("dtrefd_1").Value), "00") _
            & Format(Day(Sheets("Painel").Range("dtrefd_1").Value), "00")
            'nome da planilha de D-1 para procurar o StD depois
Set NewWb = Workbooks.Add

'Paste into the new workbook
NewWb.Sheets("Sheet1").Range("A1").PasteSpecial xlValues
NewWb.Sheets("Sheet1").Range("A1").PasteSpecial xlFormats

'hide columns
NewWb.Sheets("Sheet1").Columns("G:H").Hidden = True
NewWb.Sheets("Sheet1").Columns("J:K").Hidden = True

'no gridlines
NewWb.Sheets("Sheet1").Activate
ActiveWindow.DisplayGridlines = False

'zoom - 70%
ActiveWindow.Zoom = 70

'column width
With NewWb.Sheets("Sheet1").Columns("C:F")
 .ColumnWidth = .ColumnWidth * 1
End With
NewWb.Sheets("Sheet1").Columns("A").ColumnWidth = NewWb.Sheets("Sheet1").Columns("A").ColumnWidth * 2
'NewWb.Sheets("Sheet1").Columns("I").ColumnWidth = NewWb.Sheets("Sheet1").Columns("I").ColumnWidth * 1
'NewWb.Sheets("Sheet1").Columns("L").ColumnWidth = NewWb.Sheets("Sheet1").Columns("L").ColumnWidth * 1
NewWb.Sheets("Sheet1").Columns("N").ColumnWidth = NewWb.Sheets("Sheet1").Columns("L").ColumnWidth
NewWb.Sheets("Sheet1").Columns("O").ColumnWidth = NewWb.Sheets("Sheet1").Columns("L").ColumnWidth


'IT WORKS FINE UNTIL THIS LINE
'open last saved file
Set YestWb = Workbooks.Open(arq_path & yest_name & ".xlsx") 'sometimes raises error.

我已检查该文件确实存在于文件夹中。代码在我单击“调试”并从它暂停的位置再次按 F5 后运行。

标签： excelvba