Welcome to Excel Avon
Import Multiple Files using VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
Today we will teach you how to Import multiple files in excel with the help of VBA. In the image below you can see that there are many excel files which we will import in excel with the help of VBA. To import files, we will write code which will be written in new sheet.
we will look at ways to Import Multiple Files from a folder using Excel VBA.
Import Multiple Files from a folder using Excel VBA
In excel worksheet we have option to Import multiple files manually but how we do it in VBA we will learn in this article. As we know it is very easy to do manual import in excel, similarly it is easy to do in VBE also, let’s understand.
Through this post, we will learn how to Import multiple files into excel with the help of VBA, so we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as Shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine as Import Multiple Files.
Sub ImportMultipleFiles() End Sub
For import multiple files we need to activate ‘MicroSoft Scripting Runtime‘ library Go to Tools tab, then go to reference option. We will now activate the MicroSoft Scripting Runtime library.
And then Define variable for FileSystemObject, File, and FileDialog.
Sub ImportMultipleFiles() Dim FSO As New FileSystemObject Dim xlFile As File Dim FD As FileDialog End Sub
Now we will set File Diolog as ‘FileDiologFolderPicker‘. ‘WITH‘ statement is used in VBA to access all the properties and methods of the specified object.
Sub ImportMultipleFiles() Dim FSO As New FileSystemObject Dim xlFile As File Dim FD As FileDialog Set FD = Application.FileDialog(msoFileDialogFolderPicker) End Sub
Here we will name the title With (Statement) file dialog. And then Give the Button Name.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have Excel files"
.ButtonName = "Choose"
If .Show = True Then
End If
End With
End Sub
Create condition with if statement for show, creating another condition with if statement (If the number of select item is more than 0 even 1).
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have Excel files"
.ButtonName = "Choose"
If .Show = True Then
If .SelectedItems.Count > 0 Then
End If
End if
End With
End Sub
Another variable defines for string, and we should have 1 in the select item of FolderPath.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
End Sub
Condition with if statement on the condition that FolderPath is not blank. and ‘For Each’ signifies that for each entity in an array or the range repeat the process in for loop. We will use the ‘next’ keyword to move forward in the range.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If.Show = True Then
If.SelectedItems.Count > 0 Then
FolPath =.SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath). Files
Next xlFile
End If
End Sub
Here we are creating condition for file type, here we have xl?? and xl? writing to filetype
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
End If
Next xlFile
End If
End Sub
Another variable defined for workbook and worksheet.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
End If
Next xlFile
End If
End Sub
Set workbook and add string to use open function parameter. false in UpdateLink and set Worksheet.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If .SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
End If
Next xlFile
End If
End Sub
Now we will declare the last row. Copy the range of the worksheet.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If.SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Range("A2:C" & Lr).Copy
End If
Next xlFile
End If
End Sub
Another we will define variable aWS as worksheet, Store activesheet in aws.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If.SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Range("A2:C" & Lr).Copy
End If
Next xlFile
End If
End Sub
Add the last row once more aWS.range which is the second range defined. Paste WS.Range into aWS.Range.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If.SelectedItems.Count > 0 Then
FolPath = .SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Range("A2:C" & Lr).Copy
Lr = aWS.Range("A" & Rows.Count).End(xlUp).Row + 1
aWS.Range("A" & Lr).PasteSpecial xlPasteAll
End If
Next xlFile
End If
End Sub
Application.CutCopyMode = false This line of code “clears” the clipboard*. If you’ve copied an Excel cell. To close an Excel file, you need to use the “Close” method.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If.SelectedItems.Count > 0 Then
FolPath =.SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Range("A2:C" & Lr).Copy
Lr = aWS.Range("A" & Rows.Count).End(xlUp).Row + 1
aWS.Range("A" & Lr).PasteSpecial xlPasteAll
Application.CutCopyMode = False
WB.Close False
End If
Next xlFile
End If
End Sub
Now we will use the messagebox function which will open a messagebox after the files are imported. Added vbInformation button.
Sub ImportMultipleFiles()
Dim FSO As New FileSystemObject
Dim xlFile As File
Dim FD As FileDialog
Dim FolPath As String
Dim WB As Workbook
Dim WS As Worksheet
Dim aWS As Worksheet
Set aWS = ActiveSheet
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Folder where you have excel files"
.ButtonName = "Choose"
If .Show = True Then
If.SelectedItems.Count > 0 Then
FolPath =.SelectedItems(1)
End If
End If
End With
If FolPath <> "" Then
For Each xlFile In FSO.GetFolder(FolPath).Files
If xlFile.Name Like "*.xl??" Or xlFile.Name Like "*.xl?" Then
Set WB = Workbooks.Open(xlFile.Path, False)
Set WS = WB.Sheets(1)
Lr = WS.Range("A" & Rows.Count).End(xlUp).Row
WS.Range("A2:C" & Lr).Copy
Lr = aWS.Range("A" & Rows.Count).End(xlUp).Row + 1
aWS.Range("A" & Lr).PasteSpecial xlPasteAll
Application.CutCopyMode = False
WB.Close False
End If
Next xlFile
MsgBox "All Files Imported successfully!", vbInformation
End If
End Sub
After writing the code we will go to the worksheet and then assign the button to run the macro. And renamed button ‘Import Files’.
Click the ‘import Files’ button. After clicking the assign button, the location of the files will open where you will choose the excel files.
All the files have been imported in excel and after importing we will get this message.
So, in this way we can Import Multiple files in Excel worksheet.
So, I hope you have understood How to Import Multiple Files from a folder using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Import Multiple Files from a folder using Excel VBA
