Browse [Find a directory or file] (1 Viewer)

[mveijndh]

I've found some code to find a directory with a browse function. It comes down to the next line:

sBrowsingPath = strDefaultFolder
Set objShell = CreateObject("Shell.Application")
Set objfolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder for exporting your file:", OPTIONS, "C:\Users\")
Where "C:\Users\" is the default directory.

I've tried to replase the string with a variable, mentioned above sBrowsingPath = strDefaultFolder
I get this form my default settings table.

Problem the variable is not handled or processed so I start a t the root.
Any Ideas??

 

[sneuberg]

The file dialog https://msdn.microsoft.com/en-us/library/office/ff196794.aspx has a InitialFileName property that will do what you want. If your version of Access supports it I suggest you try using it rather than going outside access. Note that you need to add the reference to Microsoft Office 11.0 Object Library for this.

 

[sneuberg]

On the other hand here's some code I found at https://msdn.microsoft.com/en-us/library/windows/desktop/bb774065(v=vs.85).aspx modified to start at "C:\Users\" and it seems to work. Maybe this will help.

Function ShowOpenFolder()
 Const MY_COMPUTER = &H11&
 Const WINDOW_HANDLE = 0
 Const OPTIONS = 0
 Dim objShell
 Dim objFolder
 Dim objFolderItem
 Dim strPath
 Dim objPath
 

'Refer this Link [url]http://msdn.microsoft.com/en-us/library/bb774085(VS.85).aspx[/url]
 Set objShell = CreateObject("Shell.Application")
 Set objFolder = objShell.Namespace(MY_COMPUTER)
 Set objFolderItem = objFolder.Self
 'strPath = objFolderItem.Path
 strPath = "C:\Users\"

'This for all - [url]http://msdn.microsoft.com/en-us/library/ff521729(v=VS.85).aspx[/url]

 Set objShell = CreateObject("Shell.Application")
 Set objFolder = objShell.BrowseForFolder _
 (WINDOW_HANDLE, "Select folder to Save File:", OPTIONS, strPath)
 

 If objFolder Is Nothing Then
  ShowOpenFolder = "C:"
  Exit Function
 End If
 Set objFolderItem = objFolder.Self
 objPath = objFolderItem.Path
 ShowOpenFolder = objPath
End Function

 

[mveijndh]

This is still the fixes string solution, I was looking to use a variable I can change over time without having to enter the code.

 

[sneuberg]

I guess I'm don't understand. If you change the function so that the path is a parameter as I have done below, doesn't that do what you want?

Function ShowOpenFolder(strPath As String)
 Const MY_COMPUTER = &H11&
 Const WINDOW_HANDLE = 0
 Const OPTIONS = 0
 Dim objShell
 Dim objFolder
 Dim objFolderItem
  Dim objPath
 

'Refer this Link [url]http://msdn.microsoft.com/en-us/library/bb774085(VS.85).aspx[/url]
 Set objShell = CreateObject("Shell.Application")
 Set objFolder = objShell.Namespace(MY_COMPUTER)
 Set objFolderItem = objFolder.Self

'This for all - [url]http://msdn.microsoft.com/en-us/library/ff521729(v=VS.85).aspx[/url]

 Set objShell = CreateObject("Shell.Application")
 Set objFolder = objShell.BrowseForFolder _
 (WINDOW_HANDLE, "Select folder to Save File:", OPTIONS, strPath)
 

 If objFolder Is Nothing Then
  ShowOpenFolder = "C:"
  Exit Function
 End If
 Set objFolderItem = objFolder.Self
 objPath = objFolderItem.Path
 ShowOpenFolder = objPath
End Function

 

[mveijndh]

Hi,

No it did not work, you land at the root!!

 

[sneuberg]

I just notice that it's landing me on my desktop no matter what value I put in strPath. It must depend on some registry value. I'll see if I can figure this out, but how about the filedialog? I know that works if your version of Access supports it. I think it's supported in Access 2003 and later. Does your's support it?

 

[mveijndh]

Hi,

It comes back with an error on line:
Dim fDialog As Office.FileDialog

type is not defined

 

[sneuberg]

You probably didn't add the reference to to Microsoft Office 11.0 Object Library. The number 11.0 may be different on your system. On mine it's 15. To add the reference go to the VBA editor, select Tools and then References. Scroll way down to find to Microsoft Office xx.0 Object Library, put a x in the box and click ok.

 

[mveijndh]

Your're right again!
This one works great, to find files and create directories, but it doen not let you SELECT a directory, it only let's you select files.
I need to select Directories to put my files in.
Did I miss something??

 

[sneuberg]

I did not read it all but https://bytes.com/topic/access/insights/916710-select-file-folder-using-filedialog-object looks like it might tell you how.

 

[mveijndh]

This is it!! perfect and the point Thanks!!

 

[mveijndh]

I've decided to use this code:
This gives you the ability to create a directory select is.
Works fine for me!

'http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun05/hey0617.mspx
'You must set a reference to the Microsoft Scripting Runtime
'to use the BrowseFolder_Scripting() function in your application!
'One of the advantages of this function is it gives the user the
'option to create a new folder and you can set [which also limits] where
'the user can browse from. The API function does not.
Public Function BrowseFolder_Scripting(StrDefPath As String) As String
On Error GoTo Err_BrowseFolder_Scripting
Dim objShell As Object
Dim objfolder As Object
Dim objFolderItem As Object
Dim sBrowsingPath As String
Dim Wscript As Object
Dim sPath As String
Dim test As String

Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0
StartUp
Set objShell = CreateObject("Shell.Application")
Rem Set objfolder = objShell.NameSpace(MY_COMPUTER)
Rem Set objFolderItem = objfolder.Self
Rem sBrowsingPath = objFolderItem.Path
sBrowsingPath = strDefaultFolder
Set objShell = CreateObject("Shell.Application")
Set objfolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder for exporting your file:", OPTIONS, "C:\Users\mvane_000\OneDrive\CAE\00_CAE\_Opdrachten\_2016")
'Switch the above line with the next line if you want to limit the user to browsing only their C:\ drive
'(WINDOW_HANDLE, "Select a folder for exporting your file:", OPTIONS, "C:\")

If objfolder Is Nothing Then
Wscript.Quit
End If

Set objFolderItem = objfolder.Self
sPath = objFolderItem.path

If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If

BrowseFolder_Scripting = sPath

Exit_BrowseFolder_Scripting:
Exit Function
Err_BrowseFolder_Scripting:
If Err.Number = 91 Then 'Object variable or With block variable not set
MsgBox "User did not select a valid folder.", vbInformation, "Folder Selection Canceled"
Exit Function
ElseIf Err.Number = 424 Then
MsgBox "User did not select a valid folder.", vbInformation, "Folder Selection Canceled"
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_BrowseFolder_Scripting
End If
End Function
'Run this function as-is to test the BrowseFolder_Scripting()function
Public Function Test_BrowseFolder_Scripting()
On Error GoTo Err_Test_BrowseFolder_Scripting
Dim sFolderName As String

sFolderName = BrowseFolder_Scripting

If sFolderName <> "" Then MsgBox "You selected the '" & sFolderName & "' folder.", vbInformation
Exit_Test_BrowseFolder_Scripting:
Exit Function
Err_Test_BrowseFolder_Scripting:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Test_BrowseFolder_Scripting
End Function