Browse for Drive starting from MyComputer

[FaneDuru]

I have an addin needing to set the drive where the back-up to be done. It should be an external media (HD or memory stick). I know how to identify/check if it is external media and I have the next function in order to Browse for a folder:

 Function GetDrive() As Variant
    Dim fldr As FileDialog
    Dim vItem As Variant
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select the drive for back-up!"
        .AllowMultiSelect = False
        .InitialFileName = "" 'Here I think should set MyComputer if I would know how...
        If .Show <> -1 Then GoTo NextCode
        vItem = .SelectedItems(1)
    End With
NextCode:
    GetDrive = Left(vItem, 3)
    Set fldr = Nothing
End Function

It works well returning the drive letter even if a folder on that drive is selected. What I will like to improve is to set the start directory in MyComputer (to set InitialFileName = 'MyComputer', or change CurDir for 'MyComputer') in order to see only drives. Since, sometimes when I browse for a folder this thing happens I suppose it can be set but I downt know how...
Can anybody help on that issue?
Thanks in advance!

 

[mintjulep]

Doesn't look easy.

http://msdn.microsoft.com/en-us/library/aa969312.aspx

 

[Gavona]

Perhaps get the list of drives a different way!

http://www.mrexcel.com/forum/showthread.php?t=438433

or

http://bytes.com/topic/access/answers/704117-search-file-all-drives-computer

Gavin

 

[FaneDuru]

Thanks mintjulep! I am a little cleverer now but I still do not know how to solve the issue...

Thanks Gabona, but I do not need a way to obtain the list of existing drives. I need to memorize a specific drive (checked to be external) selected by user. I can do that with the code posted before but I would like a more elegant way of selection (directly in the My Computer 'virtual' folder). To be necessary only one click...

 

[FaneDuru]

In the meantime I found this piece of code in VBScript forum which starts browsing in My Computer (if sInitPath = 17) and I adapted a little:

Function getDriveLetter(sCaption, sInitPath)
 Dim oShell As Object, oFolder As Object, oFolderItem As Object
 Dim return_ As String
 
 Set oShell = CreateObject("Shell.Application")
 Set oFolder = oShell.BrowseForFolder(0, sCaption, 0, sInitPath)
 On Error Resume Next
 Set oFolderItem = oFolder.Items.Item
 If Err.Number > 0 Then
    return_ = ""
 Else
    If Left(oFolderItem.Path, 3) <> "::{" Then  'Computer (in Windows 7) selected
        return_ = Left(oFolderItem.Path, 3)
    Else
        return_ = ""
    End If
    Set oFolderItem = Nothing
 End If
 Set oFolder = Nothing: Set oShell = Nothing
 On Error GoTo 0
 
 getDriveLetter = return_
End Function

But it is not as elegant as previous FileDialog... I mean, beside the caption which I can set, there is an other (Title) 'Browse for Folder' and I intend to browse for drive...
For the sake of doing things nice can anybody else find a solution for the initial FileDialog?
I feel that PHV know how to solve it… He found the solution for the thread where I found VBScript code (in 2004…) but the link for documentation does not work any more…

 

[Gavona]

My thought was get a list of drives.
Use your existing technique to eliminate those that are not external
then create a userform (or menu dropdown)to enable the user to make the initial selection between them.

Gavin

 

[FaneDuru]

I am afraid that I couldn't make me understood...
I just need a way to start browsing from My Computer virtual folder...

 

[FaneDuru]

Sorry Gavona, I missed your point… I was so focused on my idea that I did not read with enough attention what you suggested. It can be a solution, indeed.
But, I refuse do believe that it is not possible to start browsing from My Computer using that FileDialog…
Can anybody else find a way to do that?

 

[N1GHTEYES]

I use the following API code to browse for folders. When I run it it starts at MyComputer.

Put the following code into a module and call the "BrowseFolder()" function.

'*****************************************************************************************
'  1. DESCRIPTION of API_Funcs Module
'
'       This module contains code related to the use of windows API functions within XL.
'
'*****************************************************************************************

Option Private Module
Option Explicit

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

Private Const MAX_PATH = 260

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
        Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long
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)

Public Function BrowseFolder(Optional msg As Variant) As String
'**********************************************************************
'  1. FUNCTIONAL DESCRIPTION of BrowseFolder
'
'       This function is derived from one found on the internet.
'       It uses API functions to call a folder browse dialog to the user,
'       and return the folder selected. The return value is "" if the user selected
'       cancel.  The optional msg is presented to the user as the message in the browse box.
'       If no msg is supplied, he gets "Please select a Folder".
'
'       Note that the significant difference between this function and the inbuilt
'       Excel Application functions GetOpenFilename and GetSaveAsFilename, is that
'       this function returns a FOLDER, whereas those expect the user to select a FILE.
'
'  2. REFERENCES - None
'
'  3. INPUTS
'
'     Optional msg - Variant - message to be displayed to the user.
'
'  4. RETURN VALUE
'     String - the folder the user selected.
'
'  5. EXTERNAL EFFECTS - None
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  Dim bi As BrowseInfo
  Dim pidl As Long
  Dim Path As String
  Dim pos As Integer

  BrowseFolder = ""
 
' pointer to root folder (0 for desktop)
bi.pidlRoot = 0
' message
Dim dlgmsg As String
If IsMissing(msg) Then
  dlgmsg = "Please select a Folder"
Else
  If VarType(msg) <> vbString Then
      dlgmsg = "Please select a Folder"
  Else
      dlgmsg = msg
  End If
End If
bi.lpszTitle = dlgmsg

' browsing type (&H1 for folders)
bi.ulFlags = &H1
' show the dialog
pidl = SHBrowseForFolder(bi)
Path = Space$(MAX_PATH)

If SHGetPathFromIDList(ByVal pidl, ByVal Path) Then
  pos = InStr(Path, Chr$(0))
  BrowseFolder = left(Path, pos - 1)
End If

If (Right$(BrowseFolder, 1) <> "\" And BrowseFolder <> "") Then
  BrowseFolder = BrowseFolder & "\"
End If

Call CoTaskMemFree(pidl)

End Function

I hope that helps,

Tony

 

[FaneDuru]

Thanks Tony!
It starts from My Computer indeed like the simpler code from VBScript forum posted above and like this one beside your string message it shows also 'Browse for Folder'. Even if I intend to browse for drive I think I have to let it showing whatever it wants...
I hopped that somebody else will help me finding the most elegant solution but not all the time you can do things perfectly…