VBA : Change Path or Location A Folder With Automatically

[muhammad susanto]

hi all...

i have vba code to insert picture into a cell...this code work properly but i want this code can "looking for" a folder that contains images..
Before it, i must change that path/location every time to set locate folder if i change my photos..
here this code :

Sub ReplaceImages()    
If Range("AM8") = "" Then Exit Sub
    Dim MyPic As String
    For i = 1 To 4
        MyPic = [COLOR=#ff0000]"d:\myfotos[/COLOR]\" & Range("AM8").Value & ".jpg"
        With Me.Shapes("Pic_" & i).Fill
            .Visible = msoTrue
            On Error Resume Next
            .UserPicture MyPic
        End With
    Next i
End Sub
Private Sub CommandButton1_Click()
    ReplaceImages
End Sub

how to make that code not change manually typing path/location ..i want the code work like can "browse" a folder that i want it..

for everyone would help me, greatly appreciated...

.sst

 

[MrIfOnly]

Selamat Petang,

If you just want the option of selecting the folder before running your code, try this:

Dim folderspec As String    ' Open the Browse Folder dialog
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .InitialFileName = "C:\"
            If .Show = 0 Then   'User pressed Cancel
            Exit Sub
            End If
            folderspec = .SelectedItems(1)
        End With

Then use:

[COLOR=#333333]MyPic = folderspec[/COLOR][COLOR=#333333] & Range("AM8").Value & ".jpg"[/COLOR]

Regards,

CJ

 

[muhammad susanto]

hi Mr...
after combine your code then like this :
Sub ReplaceImages()
If Range("AM8") = "" Then Exit Sub
Dim MyPic As String
For i = 1 To 4
Dim folderspec As String ' Open the Browse Folder dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = "C:"
If .Show = 0 Then 'User pressed Cancel
Exit Sub
End If
folderspec = .SelectedItems(1)
End With
MyPic = folderspec & Range("AM8").Value & ".jpg"
With Me.Shapes("Pic_" & i).Fill
.Visible = msoTrue
On Error Resume Next
.UserPicture MyPic
End With
Next i
End Sub

but i can't see how the code it's work to insert picture in cell...how to finish.?

just like this after running above code :

https://www.screencast.com/t/4wW5ygpUhGYy

 

[MrIfOnly]

I'm sorry, I don't understand your question. In your first post you said that your code was working properly and just wanted a way to browse to a certain folder, which is the code I supplied. Please explain further what it is you are looking for.

Regards,

CJ

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...electing-the-folder-before-running-stuck.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.