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

 

[Tom Urtis]

My take is that there are 2 different ways to accomplish this.

One way is to declare your folder variable as Public. I do not even see where you declare it at all, which means you probably have not established the Option Explicit statement at the top of the modules, which you really should do. To do that, from the VBE menu click Tools > Options and on the Editor tab select (put a checkmark next to) Require Variable Declaration and click OK. From that point on, you will see the Option Explicit statement at the top of every module.

But getting back to the first method, in some new standard module enter just this:
Public folder as Variant

My other suggestion is to ask you why you have 2 separate macros in the first place. If you put everything into a single macro your code should work as it is.

 

[Fluff]

How about

Sub MainList()
Dim ext As String
ext = InputBox("what extension")

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

Call ListFilesInFolder(xDir, True, ext)

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

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 Like ext 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, ext

Next xSubFolder

End If

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

End Sub

 

[jsuvman58]

How about

Sub MainList()
Dim ext As String
ext = InputBox("what extension")

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

Call ListFilesInFolder(xDir, True, ext)

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

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 Like ext 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, ext

Next xSubFolder

End If

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

End Sub

I tried that yesterday. It fails here. See pic.

 

[Fluff]

Did you use both sets of code I posted?

 

[jsuvman58]

My take is that there are 2 different ways to accomplish this.

One way is to declare your folder variable as Public. I do not even see where you declare it at all, which means you probably have not established the Option Explicit statement at the top of the modules, which you really should do. To do that, from the VBE menu click Tools > Options and on the Editor tab select (put a checkmark next to) Require Variable Declaration and click OK. From that point on, you will see the Option Explicit statement at the top of every module.

But getting back to the first method, in some new standard module enter just this:
Public folder as Variant

My other suggestion is to ask you why you have 2 separate macros in the first place. If you put everything into a single macro your code should work as it is.

Thanks for the reply.

Working backwards with your reply, the reason for two was that was the way the macro was written that I received. When I try to combine the top into the bottom one, it will no long show us as a macro in this list when you go to run it. From what I found out it had something to do with the list of stuff after the Sub Name?

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

I tried putting the code under Options Explicit and I get another error. See pic.

Its been awhile since I worked on these, Not sure where you mean to declare the folder variable as Public?

 

[jsuvman58]

Did you use both sets of code I posted?

So sorry about that. Yes what you posted seems to work now. Put in in a new Module instead of pasting over mine. Thank You. Let me check a few things and I will mark as solved.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jsuvman58]

How about

Sub MainList()
Dim ext As String
ext = InputBox("what extension")

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

Call ListFilesInFolder(xDir, True, ext)

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

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 Like ext 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, ext

Next xSubFolder

End If

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

End Sub

Thanks again. I see that part I missed at the very end when I tried this the other day. I did not put the ext in this statement.

ListFilesInFolder xSubFolder.Path, True, ext

While we are here any ideas on how to make the search not case sensitive?

Example, have files from some old programs that used all caps such a JPG or PDF. Naturally if I search jpg or pdf (Lower Case) it will not find them. I would like for it to find them all just by typing in jpg or JPG once in the inputBox.

Was going to play around with UCase, LCase, or srtComp (CompareMethod.Text) which I have never done.

 

[Fluff]

How about

If LCase(strFileExt) Like ext Then

and enter the extension in lower case only