Hi
I'm trying to import 3 files from a folder called "ExcelFilesToImport" that sits within in the Documents path.
But I get a run time error that says "Sorry, we couldn't find Apples.xlsx. It is possible it was moved, renamed or deleted." (see first screenshot attached).
However, the file is definitely an xlsx file and hasn't been moved, renamed or deleted (see second screenshot attached). When you right-click on it and click on Properties, the type is "Microsoft Excel Worksheet (.xlsx)"
Does anyone know what needs to be corrected in the code at the bottom of this message, please?
The files I'm importing are simple:
File 1 is called Apples and has the text 'Apples' in B3 and the number 1 in B4.
File 2 is called Bananas and has the text 'Bananas' in B3, the number 2 in B4, and the number 3 in B5.
File 3 is called Pears and has the text 'Pears' in B3, the number 3 in B4, and the number 4 in B5 and the number 5 in B6.
I'd like to import all of them, so that the data from each file is pasted into one tab in the active file with the macro.
But I'd like to ensure there is a blank row between the data from each file.
Eg if data from File 1 populates cells B3 and B4 in the imported data tab, then data from File 2 would populate cells B5 (row 4 would be blank) and B6.
TIA
I'm trying to import 3 files from a folder called "ExcelFilesToImport" that sits within in the Documents path.
But I get a run time error that says "Sorry, we couldn't find Apples.xlsx. It is possible it was moved, renamed or deleted." (see first screenshot attached).
However, the file is definitely an xlsx file and hasn't been moved, renamed or deleted (see second screenshot attached). When you right-click on it and click on Properties, the type is "Microsoft Excel Worksheet (.xlsx)"
Does anyone know what needs to be corrected in the code at the bottom of this message, please?
The files I'm importing are simple:
File 1 is called Apples and has the text 'Apples' in B3 and the number 1 in B4.
File 2 is called Bananas and has the text 'Bananas' in B3, the number 2 in B4, and the number 3 in B5.
File 3 is called Pears and has the text 'Pears' in B3, the number 3 in B4, and the number 4 in B5 and the number 5 in B6.
I'd like to import all of them, so that the data from each file is pasted into one tab in the active file with the macro.
But I'd like to ensure there is a blank row between the data from each file.
Eg if data from File 1 populates cells B3 and B4 in the imported data tab, then data from File 2 would populate cells B5 (row 4 would be blank) and B6.
VBA Code:
Sub ImportInto1Tab()
Dim FolderPath As String, Filename As String, Sheet As Worksheet, sh As Worksheet
Dim lr As Long, lc As Long, lr1 As Long
Application.ScreenUpdating = False
Path = "C:\Users\" & Environ("UserName") & "\Documents\ExcelFilesToImport\"
'FolderPath = Environ("userprofile") & "\Desktop\Test\"
'Filename = Dir(FolderPath & "*.xls*")
Filename = Dir(Path & "*.xls")
Set sh = Sheets.Add(before:=Sheets(1))
Do While Filename <> ""
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
lr = Sheet.UsedRange.Rows(Sheet.UsedRange.Rows.Count).Row
lc = Sheet.UsedRange.Columns(Sheet.UsedRange.Columns.Count).Column
lr1 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1
Sheet.Range("A1", Sheet.Cells(lr, lc)).Copy sh.Range("A" & lr1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub
TIA