PDA

View Full Version : Merging multiple excel file into one



shan
07-28-2015, 01:24 AM
Hello All,

Request your help for macro to merge multiple excel file into one file. The format of all file is same.

Without putting the path...

1. Merge multiple excel file into one file. (column A to column AV)
2. Apply filter and check for blank rows in column H and delete the blank rows.

Aussiebear
07-28-2015, 03:52 PM
Try the following; Please note that Filepath is the variable for the full path name


Sub Test()
Dim Filepath As String
Dim FileSpec As String
Dim FileName As String
Dim ShtCount As Long
Dim RowCount As Long
Dim Merged As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Filepath = "C:\Users\agravney\Desktop" 'set the folder to the correct path
FileSpec = FileFold & Application.PathSeparator & "*.xl*"
FileName = Dir(FileSpec)
'Exit if no files found
If FileName = vbNullString Then
MsgBox Prompt:="No files were found that match " & FileSpec, Buttons:=vbCritical, Title:="Error"
Exit Sub
End If
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
ShtCount = 0
RowCount = 1
Set Merged = Workbooks.Add
Do While FileName <> vbNullString
ShtCount = ShtCount + 1
Set wb = Workbooks.Open(FileName:=Filepath & Application.PathSeparator & FileName, UpdateLinks:=False)
Set ws = wb.Worksheets("Sheet1")
With ws
If .FilterMode Then .ShowAllData
If ShtCount > 1 Then .Rows(1).EntireRow.Delete Shift:=xlUp
.Range("A1").CurrentRegion.Copy Destination:=Merged.Worksheets(1).Cells(RowCount, 1)
End With
wb.Close SaveChanges:=False
RowCount = Application.WorksheetFunction.CountA(Merged.Worksheets(1).Columns("A:A")) + 1
FileName = Dir
Loop
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
MsgBox Prompt:="Finished merging.", Buttons:=vbInformation, Title:="Success"
End Sub

shan
07-28-2015, 10:56 PM
Hello Sir,

Thank you for code..

But even though excel files are available in folder the code is prompting " No files found" ... I have all files in .xlsx format.

Bob Phillips
07-29-2015, 08:29 AM
Did you change the FilePath in Bear's code?

shan
07-29-2015, 09:13 PM
Yes I have changed the File Path!!