Path of File Picked Up From Cells In Spreadsheet

Hi All,

Is there a way to make the contents of a particular cell in a worksheet show where to pick up another file. Here is my code to pick up a file:

Sub workOnSheet()

    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer 
    Dim NameOnly As String
    '   Set Drive letter
        ChDrive "E:\"
    '   Set to Specified Path\Folder
        ChDir "E:\PATH HERE"
    '   Set File Filter
        'Filt = "PID files (*.pid), *.pid"
        Filt = "PIW files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            End
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    
        NameOnly = GetFileName(CStr(FileName))
        
        DEFName = NameOnly
        
    '   Open Selected Workbook
        Workbooks.Open FileName

Then, I need to be able to pickup the path and drive from a cell out of a particular worksheet so I user can change it when they move the project around.
Lets say I want to put the drive and path under A1 and A2 in workbook named KittyCat where a user can go in and change it. How would I alter the code above to make it accept this. Thank you for any help you can provide on this!!!

Public DEFName As String

Public Function GetFileName(flname As String) As String
    
    'Get the filename without the path or extension.
    'Input Values:
    '   flname - path and filename of file.
    'Return Value:
    '   GetFileName - name of file without the extension.
    
    Dim posn As Integer, i As Integer
    Dim fName As String
    
    posn = 0
    'find the position of the last "\" character in filename
    For i = 1 To Len(flname)
        If (Mid(flname, i, 1) = "\") Then posn = i
    Next i

    'get filename without path
    fName = Right(flname, Len(flname) - posn)

    'get filename without extension
   ' posn = InStr(fName, ".")
    '    If posn <> 0 Then
     '       fName = Left(fName, posn - 1)
      '  End If
    GetFileName = fName
End Function

Do you know how I can make it accept the path and drive out of a worksheet cell??? P

Cells(1,1) is the same as Range("A1"). So, to put and get a value for cell A1:

Range("A1").Value=ThisWorkbook.FullPath
MsgBox Range("A1").Value

Kenneth Hobson - This looks like what I need. How do I specify a specific sheet of a workbook with the code you just gave me??

Roy- I am still trying to solve. Thank you for all the help so far though!!

Besides picking a specific worksheet to pull A1 from out of workbook. I would like to know where would I put this code, too? Could you show me the right spot to make it work - SEOT