This is code from John Walkenbach's book "Excel 2000 Power Programming with VBA":
VB Code:
  1. Option Explicit
  2.  
  3. '32-bit API declarations
  4. Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  5.     Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
  6.     pszpath As String) As Long
  7.  
  8. Declare Function SHBrowseForFolder Lib "shell32.dll" _
  9.     Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
  10.     As Long
  11.  
  12. Public Type BrowseInfo
  13.     hOwner As Long
  14.     pIDLRoot As Long
  15.     pszDisplayName As String
  16.     lpszTitle As String
  17.     ulFlags As Long
  18.     lpfn As Long
  19.     lParam As Long
  20.     iImage As Long
  21. End Type
  22.  
  23. Function GetDirectory(Optional msg) As String
  24.     Dim bInfo As BrowseInfo
  25.     Dim path As String
  26.     Dim r As Long, x As Long, pos As Integer
  27.    
  28.     'Root folder = Desktop
  29.     bInfo.pIDLRoot = 0&
  30.    
  31.     'Title in the dialog
  32.     If IsMissing(msg) Then
  33.         bInfo.lpszTitle = "Please select the folder where 'Old Bio-Analytical MS.xls' exists."
  34.     Else
  35.         bInfo.lpszTitle = msg
  36.     End If
  37.    
  38.     'Type of directory to return
  39.     bInfo.ulFlags = &H1
  40.    
  41.     'Display the dialog
  42.     x = SHBrowseForFolder(bInfo)
  43.    
  44.     'Parse the result
  45.     path = Space$(512)
  46.     r = SHGetPathFromIDList(ByVal x, ByVal path)
  47.     If r Then
  48.         pos = InStr(path, Chr$(10))
  49.         [COLOR=Red]GetDirectory = Left(path, pos - 1)[/COLOR]
  50.     Else
  51.         GetDirectory = ""
  52.     End If
  53. End Function
  54.  
  55. Sub GetAFolder()
  56.     Dim msg As String
  57.     msg = "Please select a location for the backup."
  58.     MsgBox GetDirectory(msg)
  59. End Sub
The Sub at the bottom is just a test for the call...which isn't working

I get the error where the red text is. Apparently variable pos = 0

Any ideas????