Adding a wildcard into macro?

[Lee.]

Hi,
I've found a VBA macro code on the forum, which lists all the files within a folder. The code works fine when I have the folder location as a static one.

However, I would like to add a wildcard into this line

strTopFolderName = "\\Shareddrive\Admin\Letters\2014\*\Customer Letters"

The reason being, is that I only want the macro to list the file names within the customer letters folder, otherwise the macro takes ages to run and crashes Excel most of the time.

The * what I thought would be ok to be the wildcard, is a folder which is the month name.

Is it possible to have a wildcard like this in a folder path?

Thanks
Lee

 

[ranman256]

nope. cant do that.
If you know the bottom folder 'customer letters' then you should know the one above it.
You'd have to cycle thru the upper folder to get all lower ones.

 

[NeonRedSharpie]

Maybe try:

strTopFolderName = "\\Shareddrive\Admin\Letters\2014\" & * & "\Customer Letters"

 

[Lee.]

That's a shame, I'll just cycle through instead then like

Sub file_looper()On Error Resume Next
Dim y, i, m, x As String
Dim objFSO As Scripting.FileSystemObject
Dim objTopFolder As Scripting.Folder
Dim strTopFolderName As String


    Range("A1").Value = "File Name"
    Range("B1").Value = "File Size"
    Range("C1").Value = "File Type"
    Range("D1").Value = "Date Created"
    Range("E1").Value = "Date Last Accessed"
    Range("F1").Value = "Date Last Modified"
    Range("G1").Value = "Attributes"
    Range("H1").Value = "Path"
    Range("I1").Value = "Drive"
    Range("J1").Value = "Short Name"
    Range("K1").Value = "Parent"
For y = 1 To 12
    m = Format(y, "MMMM")
        For i = 1 To 31
            If i >= 4 And i <= 20 Then
            x = i & "th"
            End If
            If i >= 24 And i <= 30 Then
            x = i & "th"
            End If
            If i = 1 Or i = 21 Or i = 31 Then
            x = i & "st"
            End If
            If i = 2 Or i = 22 Then
            x = i & "nd"
            End If
            If i = 3 Or i = 23 Then
            x = i & "rd"
            End If


'Assign the top folder to a variable
            strTopFolderName = "\\Shareddrive\Admin\Letters\2014\" & m & "\" & x & "\Customer Letters"


'Create an instance of the FileSystemObject
            Set objFSO = CreateObject("Scripting.FileSystemObject")


'Get the top folder
            Set objTopFolder = objFSO.GetFolder(strTopFolderName)


'Call the RecursiveFolder routine
            Call RecursiveFolder(objTopFolder, True)


'Change the width of the columns to achieve the best fit
            Columns.AutoFit


        Next i
Next y


End Sub