Copy Data from Multiple Workbook to main file

Question

I have this formula and Im getting Error 1004, saying that file was possible removed or renamed or deleted, but in reality file is just there.

Im getting the error on this line : Workbooks.Open (MyFile)

Sub LoopThroughDirectory()
Dim MyFile As String 
Dim erow MyFile = Dir("C:\Users\congresojr.5\Desktop\Puller Sample\sample file\")

Do While Len(MyFile) > 0
If MyFile = "zzzPuller.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile) Range("B11:J31").Copy ActiveWorkbook.Close

erow = RawPuller.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste
Destination:=Worksheets("RawPuller").Range(Cells(erow, 2),
Cells(erow, 10))

MyFile = Dir

Loop

End Sub

Try `Worksheets("RawPuller").Range(Worksheets("RawPuller").Cells(erow, 2), Worksheets("RawPuller").Cells(erow, 10))`? — BruceWayne, Dec 20 '19 at 22:17
Hi, im getting the error on this line : Workbooks.Open (MyFile) — nathaniel Congreso, Dec 20 '19 at 22:17
Before `Workbooks.Open`, put `Debug.Print myFile`, what's the output? — BruceWayne, Dec 20 '19 at 22:31
@BigBen yes, full path is indicated. Yet im trying to understand how the filename works since im trying to get the data from all of the workbook in the folder — nathaniel Congreso, Dec 20 '19 at 22:39
@nathanielCongreso - see my answer. You need the full path in the `Workbooks.Open` call. — BigBen, Dec 20 '19 at 22:39
You need to clarify if you are trying to open all the workbooks in the path or just one workbook "zzzPuller". — GMalc, Dec 20 '19 at 23:45
@GMalc what im trying to do is open the workbooks on a specific folder one by one, copy a range and paste it to zzzpuller — nathaniel Congreso, Dec 20 '19 at 23:49
You will need to loop through the folder, open each file, and copy the range and paste to the destination worksheet. I have a question is `Worksheets("RawPuller")` `in Workbooks("zzzPuller.xlsm")`? — GMalc, Dec 21 '19 at 00:01
There are plenty of examples on how to accomplish this; either do a SO search or Google search for "excel vba open all files in folder and copy range", I got About 1,760,000 results in Google — GMalc, Dec 21 '19 at 00:06

score 0 · Answer 1 · answered Dec 20 '19 at 22:35

Dir only returns the file name. You need both the path and the filename in the Workbooks.Open call to avoid the error you're getting.

Additionally, avoid relying on an implicit or explicit ActiveWorkbook.

Dim wb as Workbook
Set wb = Workbooks.Open("C:\Users\congresojr.5\Desktop\Puller Sample\sample file\" & MyFile)

Now you can work with wb:

wb.Sheets(1).Range("B11:J31").Copy
...
wb.Close SaveChanges:=False

Also, see this question for why

Worksheets("RawPuller").Range(Cells(erow, 2), Cells(erow, 10))

is problematic.

BigBen, as i thought, the OP wants to open the folder and loop through all(?) workbook files. He obviously doesn't understand how to loop through each file, open it, and copy the same range to `Worksheets("RawPuller")` — GMalc, Dec 20 '19 at 23:58

Copy Data from Multiple Workbook to main file

1 Answers1