Insert Embedded object to fit the size of target cell

[Vincent88]

Hi Guys,
I use this code to insert anobject to a target cell in excel. This always open the Document folder to select image file. Is it possible to modify the code to select file from a desired folder instead ?

Sub SelectOLE3()
Dim objFileDialog As Office.FileDialog

    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)

        objFileDialog.AllowMultiSelect = False
        objFileDialog.ButtonName = "Select File"
        objFileDialog.Title = "Select File"
        objFileDialog.Show

        If (objFileDialog.SelectedItems.Count > 0) Then
                    
        Set f = ActiveSheet.OLEObjects.Add _
            (Filename:=objFileDialog.SelectedItems(1), _
              Link:=False, _
              DisplayAsIcon:=True, _
              IconLabel:=objFileDialog.SelectedItems(1), _
              Top:=ActiveCell.Top, _
              Left:=ActiveCell.Left _
           )
        f.Select
        
        With f
       .ShapeRange.LockAspectRatio = msoFalse
       .Width = ActiveCell.Width
       .Height = ActiveCell.Height
        End With
        
        ActiveCell.Value = objFileDialog.SelectedItems(1)
        ActiveCell.ShrinkToFit = True
              
        End If
        
        Set f = Nothing
        Set objFileDialog = Nothing
   End Sub

 

[GWteB]

The use of the InitialFileName property is what you're after. Usage as per example below. Make sure the folder exists and the pathname's string ends with a backslash character ( \ ).

        objFileDialog.AllowMultiSelect = False
        objFileDialog.ButtonName = "Select File"
        objFileDialog.Title = "Select File"
        objFileDialog.InitialFileName = "C:\Users\Vincent88\MyPictures\"
        objFileDialog.Show

 

[Vincent88]

Hi GWteB,
Can the path be network path like \\abc.com\rfs\v1\ ?

 

[GWteB]

I'm not sure, you have to try that. At my office all accessible network shares are mounted to logical drives.