Browser Functions Problem

[Chip Pearson]

What is the particular problem you are having?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

That code is verbatim from my web site
(www.cpearson.com/Excel/BrowseFolder.htm), and it works for me. I just tried
it in both Excel 2003 and Excel 2007 on both Windows XP Pro SP2 and Windows
Vista Ultimate. It works fine. Put your cursor inside the OpenMyBrowser sub
and then press the F8 key to step through the code line by line. Does the
browse folder dialog display? Do you get an error?


Just for diagnostics, change

ID = SHBrowseForFolderA(BrowseInfo)
to
ID = SHBrowseForFolderA(BrowseInfo)
MsgBox "Last DLL Error: " & CStr(Err.LastDLLError)

If the MsgBox displays anything but 0, then the call to SHBrowseForFolderA
failed and you can decode the Err.LastDLLError using other code on my web
site (http://www.cpearson.com/Excel/FormatMessage.aspx).

By the way, the line of code

Dim FName, SUBDIR, ROOTDIR As String

probably isn't doing what you think it is. It does NOT declare all these
variables as Strings. It declares FName and SUBDIR as Variants and only
ROOTDIR as a String. It is the same as

Dim FName As Variant, SUBDIR As Variant, ROOTDIR As String

However, this should not cause any problems.

Also, you should almost NEVER use the "End" statement. It is a poor
programming practice that can cause unexpected problems.

And on a final note, your computer's clock is wrong. Please fix it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Jaemun]

Hello Chip Pearson,

Thanks for the advice. I totally agree with you. Because I did tested your
code using Office 97 - 2007 on Windows 98 - Vista Ultimate. And they works
great!

I don't know what cause the system on my computer cannot run the code. Even
after I reformat my computer I still can't run the code.

Ok, now I've already diagnost the codes as you have advised me to do. Please
examine the result below:-

If ID Then '<--------the "step into" cursor stop at this line.
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If '<---------then continue at this line

In addition, the message box displayed error as "0".

Thanks in advance,
Jaemun.

 

[Chip Pearson]

If code execution resumes at the second "End If" following the "If ID Then"
line, that means that the ID variable has a value of 0. According to MSDN,
this will occur only if the user click's Cancel on the dialog. Beyond that,
I have no idea why the function would fail. The Knowledge Base has nothing
relevant on the topic.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Jaemun]

Dear people,
I used the code since last year. But today now I really had a problem with
it. Please help me.

The code begin with:-
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long


Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With

FolderName = String$(MAX_PATH, vbNullChar)

ID = SHBrowseForFolderA(BrowseInfo)

If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function

'The following code should bring the popup folder browser:-
Sub OpenMyBrowser()
Dim FName, SUBDIR, ROOTDIR As String
FName = BrowseFolder("Testing")
If FName = "" Then
End
Else
End If
'procceed next code here
End Sub

I have test the code using different operating system and works fine!
But not in my computer. I'm using WindowsXP sp2 and Office2003.

Thanks in advance.
Jaemun.

 

[Jaemun]

Hi ChipPearson.

Thanks for the respond.

My problem is that I can't open Browser using the code to browse the folder.
Probably the function code need to be modify. But I have no idea how.

Regards.

 

[Jaemun]

Hi again,
My appologize. Sorry I forget to mentions that I'm using 2 types of code
(Macro and Function).

This macro code should bring up the popup Browser:-
Sub OpenMyBrowser()
Dim FName, SUBDIR, ROOTDIR As String
FName = BrowseFolder("Testing")
If FName = "" Then
End
Else
End If
'procceed next code here
End Sub

I dont think there is a problem with "Sub OpenMyBrowser()".
Probably someone could help me to modify the functions:-

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long


Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With

FolderName = String$(MAX_PATH, vbNullChar)

ID = SHBrowseForFolderA(BrowseInfo)

If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If
End Function

Thanks in advance.
Jaemun.

 

[Jaemun]

Dear Chip Pearson,

I never thought "Kaspersky Anti-Virus Personal Pro" has blocked the macros.
It probably change the setting after an automatic update.

Since the code execute an API Function "SHBrowseForFolderA" and
"SHGetPathFromIDListA" from shell32.dll, therefore I have make a minor
changes to Kaspersky AVPP setting:-

- Realtime protection settings
- Macros
- Calling to API Function (I set to "Allow execution")

Now the code works great!
Anyway, many thanks and appreciations for your help. Your advices / briefs
really help me to understand the code better.

Kind Regard,
Jaemun,