应用程序定义的或对象定义的错误或运行时错误1004 [英] Application-defined or object-defined error Or Run Time Error 1004
问题描述
```vb
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir(C:\ Users \ SJPM \Desktop \Sal \)
做Len(MyFile)> 0
如果MyFile = zmaster.xlsm然后
退出Sub
结束如果
Workbooks.Open(MyFile)
工作表(最终工资)。范围(B22:E32)。复制
ActiveWorkbook.Save
ActiveWorkbook.Close
erow = ActiveWorksheets.Cells (Rows.Count,1).End(xlUp).Offset(1,0).Row
ActiveWorksheets.Paste目的地:=工作表(Sheet1)。范围(单元格(erow,1) ,细胞(erow,4))
MyFile = Dir
循环
End Sub
```
此行中出现错误:
Workbooks.Open(MyFile)
因为MyFile只包含没有路径的文件名!
试试这个:
Sub LoopThroughDirectory()
Dim sFile 作为 字符串,sPath 作为 字符串,eRow as 整数
Dim srcWbk 作为工作簿,dstWbk as 工作簿
Dim srcWsh 作为工作表,dstWsh as 工作表
设置 dstWbk = ThisWorkbook
设置 dstWsh = dstWbk.Worksheets( Sheet1)
sPath = C:\ Users \SJPM \Desktop \Sal \
sFile = Dir(sPath)
while sFile<>
如果 MyFile = zmaster.xlsm 然后
退出
结束 如果
设置 srcWbk = Application.Workbooks。打开(sPath& MyFile)
设置 srcWsh =工作表( 最终工资)
eRow = dstwsh.Cells(Rows.Count, 1 )。 End (xlUp).Offset( 1 , 0 )。行
srcWsh。范围( B22:E32)。复制dstWsh.Range(Cells(erow, 1 ),Cells(erow, 4 ))
srcWbk.Close SaveChanges:= False
MyFile = Dir()
循环
' 关闭目标工作簿
dstWbk.Close SaveChanges:= True
结束 Sub
注意:您必须根据需要更改上述代码!
```vb
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\Users\SJPM\Desktop\Sal\")
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Workbooks.Open (MyFile)
Worksheets("Final Salary").Range("B22:E32").Copy
ActiveWorkbook.Save
ActiveWorkbook.Close
erow = ActiveWorksheets.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveWorksheets.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
MyFile = Dir
Loop
End Sub
```解决方案The error occurs in this line:
Workbooks.Open (MyFile)
because MyFile contains only file name without its path!
Try this:
Sub LoopThroughDirectory() Dim sFile As String, sPath As String, eRow as Integer Dim srcWbk As Workbook, dstWbk as Workbook Dim srcWsh As Worksheet, dstWsh as Worksheet Set dstWbk = ThisWorkbook Set dstWsh = dstWbk.Worksheets("Sheet1") sPath = "C:\Users\SJPM\Desktop\Sal\" sFile = Dir(sPath) Do While sFile<>"" If MyFile = "zmaster.xlsm" Then Exit Do End If Set srcWbk = Application.Workbooks.Open(sPath & MyFile) Set srcWsh = Worksheets("Final Salary") eRow = dstwsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row srcWsh.Range("B22:E32").Copy dstWsh.Range(Cells(erow, 1), Cells(erow, 4)) srcWbk.Close SaveChanges:=False MyFile = Dir() Loop 'close destination workbook dstWbk.Close SaveChanges:=True End Sub
Note: you have to change above code to your needs!
这篇关于应用程序定义的或对象定义的错误或运行时错误1004的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!