VBA listing ".xlsx" files only from a selected folder

[Kov4n]

Hi,
I need some help with the current VBA code I am using.

The aim is first to count the .xlsx files in a folder and state the number in a defined cell, then in a table below on the same sheet, it lists files from selected folders....
Counting the files is fine but when listing .xlsx files from a selected folder it lists all the files regardless of the type....

This is currently the code I'm using, wanted to see if there is a way to list .xlsx files only?


Sub Outstanding39()

'Count files from selected folder
Dim folder_path As String
Dim strtype As String
Dim totalfiles As Variant

strtype = "*.xlsx*"

folder_path = Worksheets("Data2").Cells(83, 2).Value

If Right(folder_path, 1) <> "\" Then folder_path = folder_path & "\"
totalfiles = Dir(folder_path & strtype)

Dim i As Integer

While (totalfiles <> "")
i = i + 1
totalfiles = Dir
Wend

Worksheets("Open").Cells(15, 7).Value = i
Worksheets("Open").Cells(15, 7).Select

'List Files from selected folder
Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim objFolder As Scripting.Folder
Dim nextRow As Long

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Worksheets("Data2").Cells(83, 2).Value)

nextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

For Each objFile In objFolder.Files
Cells(nextRow, 2) = objFile.Name
Cells(nextRow, 16) = objFile.ParentFolder
nextRow = nextRow + 1

Next

End Sub



I am hoping someone is able to assist with doing this....

 

[alansidman]

Look at this video for an alternative to VBA

 

[Kov4n]

Look at this video for an alternative to VBA

Hi, Thank you for the reply but the steps involved in using power query tool would be a bit too much for the end user the process is being built for.

 

[HaHoBe]

Hi Kov4n,

maybe change

For Each objFile In objFolder.Files
Cells(nextRow, 2) = objFile.Name
Cells(nextRow, 16) = objFile.ParentFolder
nextRow = nextRow + 1

Next

to

For Each objFile In objFolder.Files
  If LCase(Right(objFile.Name, 5)) = ".xlsx" Then
    Cells(nextRow, 2) = objFile.Name
    Cells(nextRow, 16) = objFile.ParentFolder
    nextRow = nextRow + 1
  End If
Next objFile

And it should be good enough to use

strtype = "*.xlsx"

Ciao,
Holger