folder selection dlgbox 2

[jbpelletier]

hi

i need something like this
: Application.GetOpenFilename
but to select a folder with office 97/
in access and excel vba

jb

 

[PHV]

This what I personally use:

Function PickFolder(strStartDir As Variant) As String
    Dim SA As Object, f As Object
    Set SA = CreateObject("Shell.Application")
    Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not f Is Nothing) Then
        PickFolder = f.Items.Item.Path
    End If
    Set f = Nothing
    Set SA = Nothing
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Check out this Call the standard Windows File Open/Save dialog box or BrowseFolder Dialog.

Roy-Vidar

 

[jbpelletier]

PHV : when i try your code and it says error: an active x control cannot create an object

do i miss something? do i need to add a reference?

RoyVidar: i tried the "BrowseFolder Dialog" link and under excel it works but it open access and after i can't close it excep with ctrl+alt+grrr . Havent try it under acess but i guess it will work.

tanx
jb

 

[jbpelletier]

nv i got it

change
.hOwner = hWndAccessApp
with
.hOwner = hWndexcelApp

tanx
jb

 

[PHV]

an active x control cannot create an object
And which line of code is highlighted in debug mode ?
From the official site (

http://msdn.microsoft.com/library/e.../reference/objects/shell/browseforfolder.asp)

Minimum DLL version shell32.dll version 4.71 or later
Minimum operating systems Windows 2000, Windows NT 4.0 with Internet Explorer 4.0, Windows 98, Windows 95 with Internet Explorer 4.0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jbpelletier]

PHV: it was this line
>> Set SA = CreateObject("Shell.Application")

btw i havent tryied under acces so far, i have only tried under excel 97


jb

 

[PHV]

For me, works on all the VBA containers.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244