Prompt user to browse & Select file and hardcode values into Workbook

[JonRowland]

Hi,

I'm trying to do the folllowing with my VBA code. I want to prompt the user to browse for a file and then hard code the PATH and FileName into that workbook for another procedure that would be used at various times.

At the moment I'm doing this manually. In the code below the variable FileToOpen captures the full path & filename as one. The issue with this, unless anyone can advise different , is that another produced I add an XLOOKUP based on

    Cells(2, 4).FormulaR1C1 = _
                            "=XLOOKUP(RC[-1],'" & FilePath & "[" & File & "]" & ShtName & "'!C1,'" _
                         & FilePath & "[" & File & "]" & ShtName & "'!C35,""NOT IN LIST"",FALSE)"

So the [ & ] is the issue in having a one variable with the path & filename.

So ideally I'd like to tweak the code below to get separate path and filename.

Sub Get_Data_From_File()

Dim FileToOpen  As Variant
Dim Path As String
Dim File As String

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls*), *xls*", , "Select File")
        
        If FileToOpen <> False Then

' Here I'd like to hard code the path & filename.  FileToOpen get the full path & filename in one string

Path = xxxxxxxx
FileName =  xxxxxxxxxxxx

End If
    
End Sub

Hope that makes sense.

Thanks

 

[dmt32]

Hi
Try making your code a function & pass back the values to your declared variables

See if this update to your code does what you want

Place in STANDARD module

Function Get_Data_From_File(ByRef FolderPath As String, ByRef FileName As String) As Boolean

    Dim FileToOpen  As Variant
    
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xls*), *xls*", , "Select File")
    If FileToOpen = False Then Exit Function
    
    FolderPath = Left$(FileToOpen, InStrRev(FileToOpen, "\"))
    FileName = Mid$(FileToOpen, InStrRev(FileToOpen, "\") + 1)
    Get_Data_From_File = True
End Function

and to call it

Sub MyProg()
    Dim FilePath As String, File As String
    
    If Not Get_Data_From_File(FilePath, File) Then Exit Sub
    
    'rest of code
    
    MsgBox FilePath & File
    
End Sub

Dave

 

[JonRowland]

dmt32 - that's just what I wanted. Will work perfect with the rest of my code.

Thank you so much.

 

[dmt32]

dmt32 - that's just what I wanted. Will work perfect with the rest of my code.

Thank you so much.

Glad suggestion does what you want & appreciate your feedback

Dave