Vba code to pick a specific file from a folder and then run to append the data.

[Dinesh_Excel]

Hi Friends,

I have a folder in C drive (Temp) where in I have a file name called " Extract".
Now I was able to run the below code sucessfully where I had only one file in the Temp folder.

Need your help where the macro would select only the file name " Extract" and run the rest of the code.


Challenege : The code needs to pick file name " Extract" and ignore the rest of the files in temp folder, and run the rest of the code


Appreciate your help on the same.

Sub simpleXlsMerger()

Worksheets("Base").Range("A2:G500").Select
Selection.ClearContents

Application.DisplayAlerts = False ' donot display alerts


Dim topform As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here

Set dirObj = mergeObj.Getfolder("C:\temp")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set topform = Workbooks.Open(everyObj)


Range("A2:V" & Range("A27000").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate


Range("A27000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
topform.Close

Next


End Sub

 

[GraH - Guido]

Then you do not need the for each... next
and perhaps..

 Set topform = Workbooks.Open("Extract.xlsx")

would do.

or... more basic code

dim strPath, strWbSource as string
strPath = "C:\temp\"
strWbSource = "Extract.xlsx"

'from initial code
Worksheets("Base").Range("A2:G500").Select
Selection.ClearContents
Application.DisplayAlerts = False ' do not display alerts

'add
Workbooks.Open(StrPath & StrWbSource)

'from initial code
Range("A2:V" & Range("A27000").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

Range("A27000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'add
Workbooks(StrWbSource).Close
Application.DisplayAlerts = True

 

[Dinesh_Excel]

Thank you Grah .....appreciate your help