Good morning, afternoon or evening (depending on wherer you are),
How do I get the function below to get the folder name below to pass to the sub?
Option Explicit
Sub GetFolderName()
Dim FolderName As String
Dim sFName As String
Dim sFPath As String
FolderName = BrowseForFolder
sFName = Dir$(FolderName \ "*.txt")
Do While sFName <> ""
MsgBox (sFName)
'Get next file name
sFName = Dir$
sFPath = FolderName
sFNamePath = sFPath + sFName
Loop
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level
Dim ShellApp As Object
'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
'Destroy the Shell Application
Set ShellApp = Nothing
'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False
End Function
Right now it gives me nothing. I keep getting Run-time error '13': Type mismatch. When I remove the loop and put FolderName in the MsgBox it gives me the correct folder that I chose, so the function works, but I can't get it to work with the loop. Can anyone help me out?
Thanks.
abousetta
Bookmarks