时间戳

首页 > 解决方案 > 如何停止重新打开工作簿？

excel - 如何停止重新打开工作簿？

问题描述

我有这个代码。

Sub movedata()
    Dim i As Long

    Dim LastRow As Long
    Dim wb As Workbook
    Dim wb2 As Workbook

    Dim L1 As Variant, L2 As Variant, L3 As Variant, L4 As Variant,l6 as variant
     Variant, L6 As Variant, L7 As Variant, L8 As Variant

    Dim sht1 As Worksheet

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("PKG Avail Days")
    LastRow = sht1.Range("D:O").Find("*", SearchDirection:=xlPrevious).Row

    For i = 5 To LastRow

        L1 = sht1.Range("D" & i).Value
        L2 = sht1.Range("E" & i).Value
        L3 = sht1.Range("F" & i).Value
        L4 = sht1.Range("K" & i).Value
        L5 = sht1.Range("L" & i).Value
        L6 = sht1.Range("M" & i).Value
        L7 = sht1.Range("N" & i).Value
        L8 = sht1.Range("O" & i).Value

        Set wb2 = Workbooks.Open("\\NMFPLPCLB130010\Users\stamarae\loglog.xlsx")

        wb2.Sheets(1).Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = L1
        wb2.Sheets(1).Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = L2
        wb2.Sheets(1).Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = L3
        wb2.Sheets(1).Range("G" & Rows.Count).End(xlUp).Offset(1, 0) = L4
        wb2.Sheets(1).Range("H" & Rows.Count).End(xlUp).Offset(1, 0) = L5
        wb2.Sheets(1).Range("I" & Rows.Count).End(xlUp).Offset(1, 0) = L6
        wb2.Sheets(1).Range("J" & Rows.Count).End(xlUp).Offset(1, 0) = L7
        wb2.Sheets(1).Range("K" & Rows.Count).End(xlUp).Offset(1, 0) = L8

    Next i

End Sub

我明白了

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

和提示信息

“xxxx”已经打开。重新打开将导致您所做的任何更改被丢弃。是否要重新打开“xxx.xlsx”？

标签： excelvba

解决方案

这就是当你放

Set wb2 = Workbooks.Open("\\NMFPLPCLB130010\Users\stamarae\loglog.xlsx")

For i = 5 To LastRow循环内。宏尝试为每一行打开相同的文件，而不关闭前一个实例。

推荐阅读