时间戳

首页 > 解决方案 > 宏工作簿在执行过程中自动关闭

excel - 宏工作簿在执行过程中自动关闭

问题描述

我有一个代码，它将从定义的路径读取文件数据并将数据复制到我的宏工作簿的工作表中。当我逐行运行代码时，它工作得很好。但是当我运行整个代码时，它会在未经我许可的情况下自动关闭。下面是我之前的代码。

Set thisWB = ThisWorkbook
'Open File and Copy Data
Set thatWB1 = Workbooks.Open(TimFilePath)
TFPLR = Cells(Rows.Count, "A").End(xlUp).Row
TFPLC = Cells(1, Columns.Count).End(xlToLeft).Column
TFPLCLTR = Split(Cells(1, TFPLC).Address(True, False), "$")(0)
'MsgBox TFPLCLTR
Range("A2:" & TFPLCLTR & TFPLR).Select
Selection.Copy

'Paste Selected Data in Time Ranges Sheet
'thisWB.Activate
thisWB.Sheets(TimSheet).Activate
If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
End If
Range("A2").PasteSpecial xlPasteValues

Application.CutCopyMode = False

'Close the File
thatWB1.Close SaveChanges:=False

在我进行以下更新后，工作簿仍在关闭。

Set thisWB = ThisWorkbook
'Open Time Range File and Copy Data
Set thatWB1 = Workbooks.Open(TimFilePath)
TFPLR = Cells(Rows.Count, "A").End(xlUp).Row
TFPLC = Cells(1, Columns.Count).End(xlToLeft).Column
TFPLCLTR = Split(Cells(1, TFPLC).Address(True, False), "$")(0)
'MsgBox TFPLCLTR
Range("A2:" & TFPLCLTR & TFPLR).Copy
'Selection.Copy

'Paste Selected Data in Time Ranges Sheet
thisWB.Sheets(TimSheet).Activate
If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
End If
thisWB.Sheets(TimSheet).Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

'Close the Time ranges File
thatWB1.Close SaveChanges:=False

标签： excelvba

解决方案

解决这个问题的最好方法是声明一个变量来完全控制打开的工作簿，就像你对 thisWB 一样，例如：

Dim thatWB As Workbook

Set thatWB = Workbooks.Open(TimFilePath)
'do the work
thatWB.Close SaveChanges:=False

推荐阅读