VBA - 运行时错误 1004

Question

我正在尝试将特定工作表从关闭的工作簿复制到打开的工作簿的特定工作表，但我收到运行时错误。

代码如下:

Option Explicit

Sub START()


'Defining variables as a worksheet or workbook
Dim shBrandPivot As Worksheet
Dim shCurrentWeek As Worksheet
Dim shPriorWeek As Worksheet
Dim shPivot As Worksheet
Dim wkb As Workbook
Dim wkbFrom As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim path As String, FilePart As String
Dim TheFile
Dim loc As String

'Setting sheets to active or to recognize where to pull information
Set shBrandPivot = ActiveWorkbook.Sheets("Brand Pivot")
Set shCurrentWeek = ActiveWorkbook.Sheets("Current Week")
Set shPriorWeek = ActiveWorkbook.Sheets("Prior Week")
Set shPivot = ActiveWorkbook.Sheets("Pivot")
Set wkb = ThisWorkbook
loc = shPivot.Range("E11").Value
path = shPivot.Range("E12").Value
FilePart = Trim(shPivot.Range("E13").Value)
TheFile = Dir(path & "*" & FilePart & ".xls")
Set wkbFrom = Workbooks.Open(loc & path & TheFile)
Set wks = wkbFrom.Sheets("SUPPLIER_01_00028257_KIK CUSTOM")
Set rng = wks.Range("A2:N500")

'Copy and pastes This week number to last week number
shPivot.Range("E22:E23").Copy shPivot.Range("F22")

'Deletes necessary rows in PriorWeek tab
shPriorWeek.Range("A4:AC1000").Delete

'Copies necessary rows in CurrentWeek tab to PriorWeek tab
shCurrentWeek.Range("A4:AC1000").Copy shPriorWeek.Range("A4")

'Copies range from report generated to share drive and pastes into the current week tab of open order report
rng.Copy wkb.Sheets("Current Week").Range("A4")

'Closes the workbook in the shared drive
wkbFrom.Close False

End Sub

这分别是单元格 E11、E12 和 E13 中的内容:

E11 - S:_Supply Chain\Weekly Rpts Supplier and Buyer\
E12 - 102112
E13 - SUPPLIER_01_00028257_KIK 定制产品 GAINSVILLE_21-OCT-12.xls

我希望打开已关闭工作簿的目录是 S:_Supply Chain\Weekly Rpts Supplier and Buyer\102112\Supplier_01_00028257_KIK CUSTOM PRODUCTS GAINSVILLE_21-OCT-12.xls

Answer 1

我推荐你阅读 this

原因

This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook

如何解决

To resolve this problem, save and close the workbook periodically while the copy process is occurring

有关聊天中讨论的摘要

通过在 Set wkbFrom = Workbooks.Open(loc & path & TheFile) 上设置断点

我们意识到文件名丢失了原因 Dir(path & "*" & FilePart & ".xls")返回没有文件名的目录。

所以解决方案是添加单元格 E13 中的文件名