scan for only excel files in folder

[jordanburch]

Hey Guys,

I am trying to get this code to scan and open only .XLS or .XLSX files. It is opening PDF as well and putting them in their own excel file which i dont want. Any help is greatly appreciated! See below.


Filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\" & fn2 & fn2 & " " & "DRP" & "\" & fn2 & fn2 & "-" & fn3 & " " & "Reporting Cycle" & "\"
'K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\DRP\2020 DRP\2020-05 Reporting Cycle
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0 And MyFile <> "suspense automation.xlsm"

erow = wb1.Sheets("DRP").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(Filepath & MyFile)
With wb2


Dim ShtName As String
ShtName = "Sheet 1"
If Evaluate("isref('" & ShtName & "'!A1)") Then
'sheet exists do something
Else
'sheet doesn't exist do something else
End If
If Evaluate("isref('" & ShtName1 & "'!A1)") Then
.Sheets("Details").AutoFilterMode = False
.Sheets("Details").Range("d2:af1000").Copy Destination:=wb1.Worksheets("DRP").Cells(erow, 1)
.Close savechanges:=False
ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
.Sheets("Detail - DRP").AutoFilterMode = False
.Sheets("Detail - DRP").Range("c2:af1000").Copy Destination:=wb1.Worksheets("DRP").Cells(erow, 1)
.Close savechanges:=False

ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
.Sheets("Detail").AutoFilterMode = False
.Sheets("Detail").Range("c2:af1000").Copy Destination:=wb1.Worksheets("DRP").Cells(erow, 1)
.Close savechanges:=False

End If

 

[Fluff]

How about

MyFile = Dir(Filepath & "*.xls*")

 

[jordanburch]

How about

MyFile = Dir(Filepath & "*.xls*")

thanks fluff! brilliant as always!

J

 

[Fluff]

You're welcome & thanks for the feedback.