Passing Variables when using the Call Function

[jsuvman58]

I created a macro to look for specific file types (file extensions) and list them in the sheet. I created one but it do not do the subfolders. Found this one online and it worked well. I modified it to fit my needs.

I am not familiar with the “Call” Function but do know what it does. After two days of looking and reading and cannot figure something out and need some help. As you see from the macro it is currently set up just to search for “xlsx” files. I need to change that to a variable that I can enter in an InputBox.

However, the InputBox appears to have to be in the Sub MainList() before the “Call” Function?

If you try to put it in the other Sub ListFilesInFolder(…..) it will except that value but the value gets wiped out after the first folder is searched so the Input Box will just keep popping up with every subfolder. So someplace in the routine the value from the InputBox gets reset.

So this brings me to my question. If I put the InputBox in the Sub MainList() how do I pass that value to the other Sub so it can be used as a variable in place of the “xlsx”.

Example: If I do the below, the iBox variable does not get passed onto the other Sub from the Call Function.

Or is there a different way to do this?

Sub MainList()

Set folder = Application.FileDialog(msoFileDialogFolderPicker)

If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)

iBox = InputBox (“Enter File Extension”)

Call ListFilesInFolder(xDir, True)

End Sub

Below is the actual macro.

Sub MainList()

Set folder = Application.FileDialog(msoFileDialogFolderPicker)

If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)

Call ListFilesInFolder(xDir, True)

End Sub
-------------------------------------------------------------------------------------------------------------
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)

Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long

Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1

For Each xFile In xFolder.Files

strFileExt = xFileSystemObject.GetExtensionName(xFile)

If strFileExt = "xlsx" Then
Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
rowIndex = rowIndex + 1

End If

Next xFile

If xIsSubfolders Then

For Each xSubFolder In xFolder.subfolders
ListFilesInFolder xSubFolder.Path, True

Next xSubFolder

End If

Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing

End Sub

 

[jsuvman58]

How about

If LCase(strFileExt) Like ext Then

and enter the extension in lower case only

Thanks. That works and is one on the things I was going to try but though I would ask while I was here.

 

[Fluff]

You're welcome.