Macro to automatically add a folder name into the macro

[Hydro Man]

Macro to automatically add a folder name into the macro
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I Have the following macro below which performs well, what I would like to do is automatically add a folder name after the "\\aspen\set1\MLI_Archive\" for example: "\\aspen\set1\MLI_Archive\Folder Name" .
The folder name would be located on a worksheet in the same workbook named “Validation” in cell E13.
How do I automatically add the cell E13 folder name to the \\aspen\set1\MLI_Archive\ ?
Also how would I add a pop-up box so that a person could type in the folder name?
I may elect to run the auto population or the pop-up version, I haven’t decided yet, so two different macros would be required.
<o> </o>
Sub ListFilesInFolder()
'
' ListFilesInFolder Macro
<o> </o>
'Sub ListFilesInFolder()
Dim fs, f, f1, fc, s
a = 1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("\\aspen\set1\MLI_Archive\")
Set fc = f.Files
For Each f1 In fc
If Mid$(f1.Name, Len(f1.Name) - 3) = ".zip" Then
a = a + 1
Cells(a, 1) = f1.Name
End If
Next
End Sub
<o> </o>
<o> </o>
<o> </o>
Thank you,
<o> </o>
Hydro Man

 

[Von Pookie]

Add it onto the string:

t f = fs.GetFolder("\\aspen\set1\MLI_Archive\" & Sheets("Sheet Name Here").Range("E13"))

 

[Hydro Man]

Von Pookie,

Thank you very much it works great! I appreciate your help.

Would you know how to add a pop-up box such that a user could type in the folder and then the macro would go on from there?

Hydro Man

 

[Von Pookie]

The easiest thing to do would probably be to use the Folder Picker. That way the user can select the folder from the browse window and you don't have to worry about them misspelling. There's a pretty good example of the Folder Picker code here:

http://www.mrexcel.com/forum/showthread.php?t=294728

 

[Hydro Man]

Thank you Von Pookie for your extra efforts!

Best wishes,

Hydro Man