Solved: COMDLG32.OCX call from vba source

[michelle]

Dear VBA users,



We have the possibility to put on a vba form a common dialog object. (COMDLG32.OCX)

If we don?t use a form but only code, how can we use this object? Is there a way to call this object? Can someone give source example?



We want to use this object only for choosing a directory and we don?t want to see the parts files / file types. With which property can we handle this?



Nice regards,



Michelle.

[MOS MASTER]

Hi Michelle,

You need the browseforfolder dialog.

With this code you can call it:[VBA]
Option Explicit

Private Const MAX_PATH = 260

Private Type ****EMID
cb As Long
abID() As Byte
End Type


Private Type SHFILEINFO
hIcon As Long
iIcon As Long
dwAttributes As Long
szDisplayName As String * MAX_PATH
szTypeName As String * 80
End Type

Private Type ITEMIDLIST
mkid As ****EMID
End Type

Private Declare Function SHGetPathFromIDList Lib "Shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, ByVal pszPath As String) As Long

Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)


Private Declare Function SHBrowseForFolder Lib "Shell32.dll" Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lparam As Long
iImage As Long
End Type

Sub BrowseForFolderTest()
Dim sFolder As String
sFolder = basBrowseForFolder.SelectFolder _
("Use this dialog to browse to the folder you want")

If sFolder = "" Then
MsgBox "You must choose a folder to proceed", vbExclamation, "Check for folder"
Exit Sub
Else
MsgBox sFolder
End If
End Sub

Public Function SelectFolder(sCaption As String) As String
Dim BI As BROWSEINFO
Dim nFolder As Long
Dim IDL As ITEMIDLIST
Dim pidl As Long
Dim sPath As String
Dim SHFI As SHFILEINFO

Dim sSelectedFolder As String


On Error Resume Next

sSelectedFolder = ""

With BI
.pszDisplayName = String$(MAX_PATH, 0)
.lpszTitle = sCaption
End With
pidl = SHBrowseForFolder(BI)

If pidl > 0 Then

sPath = String$(MAX_PATH, 0)
SHGetPathFromIDList ByVal pidl, ByVal sPath

sSelectedFolder = Left$(sPath, InStr(sPath, vbNullChar) - 1)

End If
CoTaskMemFree pidl
SelectFolder = sSelectedFolder
End Function
[/VBA]

You can execute the sub: BrowseForFolderTest

I've attached the complete code module which you can import in the VBE (Rightclick project/Import)

Enjoy!

[MOS MASTER]

Hmmz...Please use the provided code module because the forum changed some of the letters to **** because its like a known cursing word!

[michelle]

Hello Mos Master,

Thanks for your quick information.

As I said before: you are great!

Michelle.

[MOS MASTER]

Hi...Love right back at yah!...You're Welcome!

Please don't forget to mark your post solved?

[Bob Phillips]

We want to use this object only for choosing a directory and we don?t want to see the parts files / file types. With which property can we handle this?

If you have Excel XP (2002) or 2003 you can use the much simpler

[VBA]Public Sub GetFolder()
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
Else
MsgBox .SelectedItems(.SelectedItems.Count)
End If
End With
End Sub[/VBA]

[MOS MASTER]

If you have Excel XP (2002) or 2003 you can use the much simpler

[VBA]Public Sub GetFolder()
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
Else
MsgBox .SelectedItems(.SelectedItems.Count)
End If
End With
End Sub[/VBA]

Hi,

Simpler? Not in Outlook....

If this was Excel >2002 yes of course the filedialog object would be the way to go.

But this is Outlook and the FileDialog is not Implemented in Outlook (although its in the helpfile)

For your code to work you'd have to automate an existing Excel Instance or create a new one to use the dialog of Excel (or for instance Word) for the use of the FolderPicker.

Enjoy!

[Bob Phillips]

If this was Excel >2002 yes of course the filedialog object would be the way to go.

That was a slip of the keyboard, I didn't mean to say Excel. I meant Office XP.

But this is Outlook and the FileDialog is not Implemented in Outlook (although its in the helpfile)

It's VBA, and it is within the Office component, so I assumed it would apply across the board. Sometimes MS amaze me with their inanity, add it to Office then only apply in some Office components.

For your code to work you'd have to automate an existing Excel Instance or create a new one to use the dialog of Excel (or for instance Word) for the use of the FolderPicker.

I don't think I would wish that on anyone. I would also use the 'ugly' API method [/QUOTE]

[MOS MASTER]

Hi Bob,

I've been searching for the reason of this disfunctioning Office Object from within Outlook and I've read tonn's of material and never found out if it trully is a known bug! (But like you I also think it is...)

I've been used to programming all different dialogs in Outlook by means of API's because the easy Office stuff isn't there to grab...

But like I said sometimes if you're running automation on let's say Excel then I like to grab the one Excel has. (For easy code reading)

O well can't have it all....

[Bob Phillips]

Hi Joost,

I've been searching for the reason of this disfunctioning Office Object from within Outlook and I've read tonn's of material and never found out if it trully is a known bug! (But like you I also think it is...)

I've been used to programming all different dialogs in Outlook by means of API's because the easy Office stuff isn't there to grab...

Outlook isn't really my forte, so that is good info to have. I will be more careful in future.

But like I said sometimes if you're running automation on let's say Excel then I like to grab the one Excel has. (For easy code reading)

I think I would shy away from that myself, what with the diffulties of fully qualifying and binding when explaining to less experienced users

Regards

Bob

[MOS MASTER]

Hi Bob,

Outlook isn't really my forte, so that is good info to have. I will be more careful in future.

O well you'd probably beat me in Excel!
(I'm sure you'll teach me some tricks one of these days....I didn't know of...I love to learn...and hope to love it for a long time...and share aswell!)

I think I would shy away from that myself, what with the diffulties of fully qualifying and binding when explaining to less experienced users

Yepz..totally agree on that point!

Early or late binding a application does require a bit of programming experience to allocate all your resources in the way you should....(So let's not advice this for a beginner...just use the hard to read API...)