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.:crying::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