MSO File Picker Help

[malleshg24]

Hi Team,

To pick a file , I am using below method. wherein user has to paste the file path and run a macro.

Set wbk = Workbooks.Open(Sheet1.Range("B5").Value) ' F:\varsha\Book2.xlsx

I look here to go below option, But I am getting Object Variable or with block variable not set. plz help.

Dim filename As String
filename = Application.GetOpenFilename
Dim cell As Range
cell = Application.Sheet1.Range("B5").Value)
cell.Value = filename

Regards,
Mallesh

[offthelip]

Why not use filedialog to get the user to pick the file since they will be familiar with using it.
This code uses the Microsoft Scripting runtime reference so:
Set reference to Microsoft Scripting runtime by selecting tools/references in the VBa window and putting a tick by Microsoft Scripting runtime.

Sub pickfile()
Dim objFSO As New FileSystemObject
'Dim fDialog As FileDialog, result As Integer
Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
         
    'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = Path
    'Show the dialog. -1 means success!
    If fDialog.Show = -1 Then
        fileselected = fDialog.SelectedItems(1)
        fname = objFSO.GetFileName(fileselected) 'The file name
        MsgBox (fname)
    End If
End Sub

You can set the inital path for the file if you want to by setting a value in "Path" at the start either by setting an absolute path or a relative such as :

Path = ActiveWorkbook.Path & "\"

[Aflatoon]

FYI, to correct your code, all you need is:

Sheet1.Range("B5").Value = fileName