VBA – Tips & Tricks

VBA – Check Extension of File

When looping through all files in a folder, it’s often necessary to check the extension of each file, so you only mess with Excel files, for example.

The code below shows how to retrieve the extension of a file, define an array with “allowed” extensions, and match the extension of the file to the array.

Sub KørImport()

Dim MinExtensionX
Dim Arr() As Variant
Dim lngLoc As Variant

'Retrieve extension of file
MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)

Arr = Array("xls", "xlsx") 'define which extensions you want to allow
On Error Resume Next
lngLoc = Application.WorksheetFunction.Match(MinExtensionX, Arr(), 0)
If Not IsEmpty(lngLoc) Then ' 

'DO STUFF if it's an xls/xlsx file. Otherwise, chose next file in folder

End If