Register
Log In
Unanswered
Home
December 12, 2016
Offline
Got it. Thanks for your further explanation.
December 12, 2016
Offline
Hi Catalin,
I got the following function which can open a browse window. If I want to implement it on your VBA, how your script can be modified? Would you like to teach me? Of course, if you can't spare your time, just forget it and forgive me bothering you so much!
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
November 8, 2013
Offline
Why would you use that? You have dedicated file and folder picker dialog:
Dim Fld as string
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folderfolder"
.Show
If .SelectedItems.Count <> 0 Then
Fld = .SelectedItems(1)
Else
MsgBox "No folder selected.", vbExclamation
Exit Sub
End If
End With
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
If you want to use your function, use it like this, but note that you have to check if the function returned a valid path, because it's set to return a Variant type (it can return a path string, or a Boolean value of False if no folder is selected:
Dim Fld As Variant
Fld = BrowseForFolder
If TypeName(fld) = "Boolean" Then MsgBox("No Folder Selected"): Exit Sub
Fld=BrowseForFolder
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
It was easier if you declared the function with a String as the return type:
Function BrowseForFolder(Optional OpenAt As Variant) As String
.....
Invalid:
'If it was determined that the selection was invalid, set to a specific text:
BrowseForFolder = "No Folder"
Now you know it's a string comparison you need to do, and you also know what text is returned in case of error - "No Folder":
Sub ListFiles()
Dim Fld As String
Fld = BrowseForFolder
If Fld Like "No Folder" Then MsgBox("No Folder Selected"): Exit Sub
Fld=BrowseForFolder
ClearData
ListMyFiles Fld, CBool(Range("A1").Text)
HyperLinks
End Sub
As I mentioned before, you have to be careful at parameter types, if you understand this, it will be easier for you to manipulate codes.
December 12, 2016
Offline
Indeed, the script of your folder picker dialog is much concise than the Function BrowseForFolder( ) I obtained from others. Besides, I don't know why it would open the browse twice and I need to select the target folder twice accordingly for further processing. Anyway I'm so lucky to learn one more lesson from you. I do appreciate it.
Most Users Ever Online: 57
Currently Online: clark shao
11 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
