Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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) "Jaemun" <x wrote in message ... 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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" <x wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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" <x wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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. "Chip Pearson" wrote in message ... 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) "Jaemun" <x wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Browser Functions Problem
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible problem with PMT and FV functions | Excel Worksheet Functions | |||
Problem with Functions and Formulas | Excel Worksheet Functions | |||
Problem with functions working | New Users to Excel | |||
formatting problem with functions | Excel Worksheet Functions | |||
Problem using VBA functions.... | New Users to Excel |