Browse the file and directory from cell contents

[greegan]

I have someone else's work here to demonstrate what I am trying to do.

Basically I'm going to run a routine on files specified by the directory and file identified in a cell:
ie in cell A1 it may appear as

c\user\project folder\filename1

(not sure if i used the tags correctly)
next to cell A will be a browse button so I can select the above file.
The string will be called in a later code.
Right now, I only have a function and code allowing me to select the directory.
I know how to bring up the dialog box to open the file. However with the MajorDomo macro I am writing to reference Cell A1, it will also call other files in a similar fashion in cells A3, A5, and A7.

here's the code I have. I need to SELECT the file and directory, not open it.

Thank you.

Declare Function SHBrowseForFolder Lib "shell32.dll" _Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim Path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&


'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1


'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    Path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal Path)
    If r Then
        pos = InStr(Path, Chr$(0))
        GetDirectory = Left(Path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function














'Set variables and constants
Dim MyPath As String
Dim DirName As String, NextFile As String, ChangeTo As String
Dim ErrorMsg As String
Dim Path As String, Msg As String
Dim RowCounter As Integer
Dim Unchanged As Integer
Const RenamedColour As Integer = 36
Const ProblemColour As Integer = 40
Const UnchangedColour As Integer = 35


Sub GetSourcePath()
'    ActiveSheet.Unprotect
'Get the path using shell32.dll routine
    Msg = "Select a directory for the file list"
    Path = GetDirectory(Msg)
    If Path <> "" Then Range("Path").Value = Path
'    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub