我有这个代码:
Sub test()
Dim wb As String
Dim wbb As Workbook
wb = "C:xyz.xlsx"
Set wbb = Workbooks.Open(wb)
MsgBox ("testing")
wbb.Close
End Sub
以上内容只完成了好的工作。工作簿按预期关闭
然而,这段代码似乎不起作用。但对我来说,它看起来与workbooks.close函数完全相同。有人能告诉我们为什么会出现错误"运行时错误‘1004’:应用程序定义或对象定义错误"吗
Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4
wb = "xyz.xlsm"
For Each file In folder.Files
If file.Name Like "*.xlsm" Then
If InStr(file.Name, "~$") = 0 Then
'sendFile (file.Name)
Set wbb = Workbooks.Open(file)
Worksheets("Sheet 1").Select
dat = Range("F11")
Worksheets("Sheet 2").Select
dat2 = Range("C54")
dat3 = Range("D54")
dat4 = Range("E54")
wbb.Close
Workbooks(wb).Activate
Range("B" & dex) = dat
Range("C" & dex) = dat2
Range("D" & dex) = dat3
Range("E" & dex) = dat4
End If
End If
Next
End Sub
我曾试图移动wbb.close的位置,但这似乎一点帮助都没有。
我还尝试使用另一种"Activeworkbook.close-false"及其变体,并显示完全相同的错误消息。
我还确认了Set wbb=Workbooks.Open(文件)<--文件变量具有与测试块中的代码相同的格式
任何帮助都将不胜感激,
谢谢。
这就是解决方案:
Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4
wb = "xyz.xlsm"
For Each file In folder.Files
If file.Name Like "*.xlsm" Then
If InStr(file.Name, "~$") = 0 Then
'sendFile (file.Name)
application.enableevents = false
Set wbb = Workbooks.Open(file)
Worksheets("Sheet 1").Select
dat = Range("F11")
Worksheets("Sheet 2").Select
dat2 = Range("C54")
dat3 = Range("D54")
dat4 = Range("E54")
wbb.Close
application.enableevents = true
Workbooks(wb).Activate
Range("B" & dex) = dat
Range("C" & dex) = dat2
Range("D" & dex) = dat3
Range("E" & dex) = dat4
End If
End If
Next
End Sub
这可能无法解决问题,但我认为问题的一部分是多个工作簿,在Range
中没有显式引用。值得一提的是,篇幅太长,无法发表评论。
尝试使用以下方法:
Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat, dat2, dat3, dat4
wb = "xyz.xlsm"
For Each file In folder.Files
If file.Name Like "*.xlsm" Then
If InStr(file.Name, "~$") = 0 Then
'sendFile (file.Name)
Set wbb = Workbooks.Open(file)
With wbb
dat = .Worksheets("Sheet 1").Range("F11")
dat2 = .Worksheets("Sheet 2").Range("C54")
dat3 = .Worksheets("Sheet 2").Range("D54")
dat4 = .Worksheets("Sheet 2").Range("E54")
.Close
End With
With Workbooks(wb)
.Range("B" & dex) = dat
.Range("C" & dex) = dat2
.Range("D" & dex) = dat3
.Range("E" & dex) = dat4
End With
End If
End If
Next
End Sub
我所做的是使用With
来帮助明确说明正在使用的范围在哪里。这可能会有所帮助,但如果没有,请告诉我。
打开事件和/或工作表激活事件中似乎运行了事件代码,因此可以使用EnableEvents
禁用该代码。还有很多不必要的选择:
Sub filesTest(folder, dex)
Dim wb As String
Dim wbb As Workbook
Dim dat
Dim dat2
Dim dat3
Dim dat4
On Error Goto clean_up
wb = "xyz.xlsm"
Application.Enableevents = False
For Each file In folder.Files
If file.Name Like "*.xlsm" Then
If InStr(file.Name, "~$") = 0 Then
'sendFile (file.Name)
Set wbb = Workbooks.Open(file)
dat = wbb.Worksheets("Sheet 1").Range("F11").Value
With wbb.Worksheets("Sheet 2")
dat2 = .Range("C54").Value
dat3 = .Range("D54").Value
dat4 = .Range("E54").Value
End With
wbb.Close
Workbooks(wb).Activate
Range("B" & dex) = dat
Range("C" & dex) = dat2
Range("D" & dex) = dat3
Range("E" & dex) = dat4
End If
End If
Next
clean_up:
Application.Enableevents = True
End Sub