CODEWITHSUNDEEP
excelvba
MrDominikku
MrDominikku

Reputation: 96

VBA Using Wildcard in folder name

So far I found many guides and job aid how to use wildcard in files names(which I know how to use) but when it come to use it in folder path I am in little trouble. This is my code:

    myFolder = Cells(k, 7)

    Archive_Path_0 = "C:\New Folder\" & myFolder & "* \" 'wildcard in folder name

    myFileName = Cells(r, 8)

    CountName = Len(myFileName)

    Windows(myFileName).Activate

    ActiveWorkbook.SaveAs Archive_Path_0 & Left(myFileName, CountName - 4) & " AFTER.xls"

myFolder is variable which can be obtain from excel spreadsheet and its constant. I want to save myFileName in myFolder location but its just partial name of full name which can be changed or modified by others. Ideas to solve this?

Upvotes: 0

Views: 2906

Answers (1)

MrDominikku
MrDominikku

Reputation: 96

This is code which will helped me and it works!

Sub ShowFolderList()
Dim fs, f, f1, s, sf As Object
folderspec = "C:\windows\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set sf = f.SubFolders
NextRow = 2
i = 2
Do While Cells(i, 2) <> ""
    Fund = Cells(i, 2)
    For Each f1 In sf
        s = s & f1.Name
        If Left(s, 4) = Fund Then
            Cells(i, 3).Value = s
        End If
        s = ""
        NextRow = NextRow + 1
    Next f1
    i = i + 1
    NextRow = 2
Loop
MsgBox ("Done!")

End Sub

Upvotes: 0

Related Questions