Visual Basic Macro to open a folder to select a file

Question

Hello. I am not well versed at all on Visual Basic but am trying to piece this together. I have a button that when clicked needs to open up a specific folder on our server so you can select from the (picture) files in that folder. I can get the folder selector to open, but can't get that specific folder to open. I've copied together code from various posts that I've read below. It's probably a disaster and needs to be cleaned up a lot. Thanks in advance for any help.

Private Sub CommandButton1_Click()

Sub InsertOfficePic()

GetActiveSheet = ActiveSheet.Select

Dim myPict As Excel.Picture

wh = ActiveSheet.Range("C3:D3").Height

ww = ActiveSheet.Range("C3:D3").Width

Sub OpenFolder()

Dim MyFolder As String

MyFolder = "O:\Images\Size Chart Logos"

ActiveWorkbook.FollowHyperlink MyFolder

End Sub

If filetoopen = False Then Exit Sub

With ActiveSheet.Range("C3:D3")

Set myPict = .Parent.Pictures.Insert(filetoopen)

myPict.Top = .Top

myPict.Left = .Left

myPict.ShapeRange.LockAspectRatio = msoTrue

myPict.ShapeRange.Width = ww

myPict.ShapeRange.Rotation = 0#

myPict.Locked = False

End With

With Selection

myPict.Placement = xlFreeFloating

myPict.PrintObject = True

End With

End Sub

Answer 1

....I don't want a default path to be set though.....

Hi,

try, 'GetOpen' method to select a picture...

expected result (picture) in active sheet, in cells C3:D3

Sub GetOpen_Pic_01()

'May 20, 2014

On Error GoTo errh

Dim ws As Worksheet

Set ws = ActiveSheet

Dim v As Variant

Dim r As Range

Set r = ws.Range("C3:D3") '<<< target area

'delete old pic. from target

For Each p In ws.Pictures

If Not Intersect(p.TopLeftCell, r) Is Nothing Then p.Delete

Next

v = Application.GetOpenFilename(FileFilter:="images (*.bmg;*.gif;*.jpg;*.jpeg;*.png;*.tif;*.yuv),*.bmg;*.gif;*.jpg;*.jpeg;*.png;*.tif;*.yuv", _

Title:="select an image", MultiSelect:=False)

With ws.Pictures.Insert(v)

.ShapeRange.LockAspectRatio = msoFalse

.Height = r.Height

.Width = r.Width

.Top = r.Top

.Left = r.Left

.Placement = xlMoveAndSize

End With

errh:

ActiveWorkbook.Save

End Sub

Answer 2

Here is a separate code snippet that may help you. Edit the line marked to include the full filepath that you want the user to start at (the specific folder).

It looks like you are using a mapped path- you need to replace that with the underlying network folder path (e.g. something like \Server\Folder\Subfolder instead of O:\Subfolder )

Sub test()

      Dim FileName As Variant

      Dim StartDir As String

      StartDir = CurDir 'saves the default path that is set on this PC

      ChDir "\ServerName\Images\Size Chart Logos" '<-- change this to your real server name

      FileName = Application.GetOpenFilename(MultiSelect:=False)

      Debug.Print FileName 'confirms that you have the right file

    'Your code goes here to work with the file that has been selected

    ChDir StartDir 'changes the path back to the original so you don't annoy the user

End Sub

Answer 3

I do want a default path, but not a specific file within that path.

Here is the basics of what I'm trying to do.

I need to create a button that says "Insert Picture". When the button is selected, the folder \SERVER\Folder1\Folder2 opens so the user can select a file. When that file (picture) is selected, it will insert into the spreadsheet centered horizontally with C3 and D3.

I'm not following along with the above. Can someone please help?

Answer 4

Thanks. This is helpful. I don't want a default path to be set though. Everyone who uses the spreadsheet will open the same network folder to populate it.

Should your code come first? What code that I posted should be modified or eliminated?