Rishabh Jangada
New Member
- Joined
- Jan 6, 2020
- Messages
- 1
- Office Version
- 2016
- 2013
- 2011
- Platform
- Windows
I am collating or appending data one below other using the below code to copy paste data in a sheet from different excels with same format stored in a folder
Sub LoopThroughFolder()
Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
MyDir = "Z:\MIS & ANALYTICS\RPA\"
MyFile = Dir(MyDir)
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("Sheet1")
Rws = Cells(Rows.Count, "A").End(x1Up).Row
Set Rng = Range(Cells(2, 1), .Cells(Rws, 27))
Rng.Copy Wb.Worksheets("Sheets1").Cells(Rows.Count, "A").End(x1Up).Offset(1, 0)
ActiveWorkbook.Close True
End With
MyFile = Dir()
Loop
End Sub
Here MyFile = Dir(MyDir) is searching file name as "xyz.xls" whereas in folder its only "xyz" hence its giving me an error stating file not found
Sub LoopThroughFolder()
Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
MyDir = "Z:\MIS & ANALYTICS\RPA\"
MyFile = Dir(MyDir)
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("Sheet1")
Rws = Cells(Rows.Count, "A").End(x1Up).Row
Set Rng = Range(Cells(2, 1), .Cells(Rws, 27))
Rng.Copy Wb.Worksheets("Sheets1").Cells(Rows.Count, "A").End(x1Up).Offset(1, 0)
ActiveWorkbook.Close True
End With
MyFile = Dir()
Loop
End Sub
Here MyFile = Dir(MyDir) is searching file name as "xyz.xls" whereas in folder its only "xyz" hence its giving me an error stating file not found