I have workbooks of the same file name but in different folders. currently I have the code to combine ALL workbooks in subfolders and it is working. however, I tried to combine only specific filename form folders but without success.
any help would be highly appreciated
first code
second code
any help would be highly appreciated
first code
Code:
Sub Import_All_CD_2()
Dim vaFileName As Variant
Const MyDir As String = "D:\Sales\JUN 17\" 'workbook location with trailing \
With ApplicationFileSearch
.NewSearch
.LookIn = MyDir
'the directory To search In
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
'workbooks found
Application.ScreenUpdating = False
For Each vaFileName In .FoundFiles
' loop through each found workbook
ProcessData vaFileName
'pass workbook fullname To process routine (see below)
Next
Else
MsgBox "There were no Excel files found."
End If
Application.ScreenUpdating = False
End With
End Sub
second code
Code:
Sub ProcessData(ByVal FName As String)
On Error Resume Next
Dim WBK As Workbook
Dim MCDrow As Long
MCDrow = ThisWorkbook.Sheets("Sheet1").Range("B65536").End(xlUp).Row
'here is my attempt to find only the workbooks I want but it is not working if I remove it will combine all workbooks in subfolders
FName = "sqlsales.xls"
'------
Set WBK = Workbooks.Open(FileName:=FName)
'open the target workbook
'do your stuff below
'WBK.Close savechanges:=True
'Exit Sub
'find row before next blank cell in column A
ilastrow = Range("A1").End(xlDown).Row
Range("A1:fj" & ilastrow).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Cells(MCDrow + 1, 2)
MCDrow = ThisWorkbook.Sheets("Sheet1").Range("B65536").End(xlUp).Row
'CLEAR COLUMNS
Sheets("Sheet1").Range("A1:C1").EntireColumn.ClearContents
Call DeleteColumns
Application.ScreenUpdating = True
'MsgBox wbk.Name
'
WBK.Close savechanges:=False
'close the workbook without saving any changes
End Sub