VBA to copy and paste image from a folder to another folder

[ashley12]

Hi everyone,

I want a button in Excel that allows user to choose an image in their personal drive, through browsing the file location. Once they select the photo, it should create and save a copy of this photo into a shared folder, then put the new file path of the image into a cell. (This new file path will be used by another user to open the image again later.)

Would this be possible?

Thank you for your time.

 

[severynm]

That's very possible. Based on some previous work i've done, I created a function called OpenFile() that returns the path name of the selected file as a string:

''==========================================================================================================================
'' Name:      OpenFile()
'' Desc:      Opens file dialog and prompts user to select a file
'' Called by: cmd_ImportData_Click()
'' Args:      N/A
'' Comments:  (1) Default path is E:\! Sewing (usb)
''            (2) Limits selection to one file at a time
''            (3) Code from https://www.chicagocomputerclasses.com/excel-vba-display-a-file-open-dialog-and-open-the-file-excel-functions/
''==========================================================================================================================
Private Function OpenFile() As String
    Dim fullpath As String
    
    On Error Resume Next
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False 'user can only select one file not multiple
        .Filters.Add 'If you want to filter by image extensions only or all file types
        .InitialFileName = "E:\..." 'initial location to open the dialog
        .Show
        
        fullpath = .SelectedItems.Item(1)
    End With
    
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, "Sew3") = 0 Then
        MsgBox ("Incorrect file type selected.")
        Exit Function
    End If
    On Error GoTo 0
    
    Workbooks.Open fullpath
    OpenFile = fullpath
End Function

Depending on your needs, you might not need the section to verify the file name to ensure the correct type of file is used. If not, just remove that if function and the two On Error lines.

In your main function (button click event function) you want to call OpenFile() to know the file path to copy, and then actually do the copying. Lastly, write the destination path to whatever cell you want in the worksheet. Something like:

Private Sub buttonName_Click()
    Dim sourceFile As String
    Dim fso As Object
    Dim destinationPath As String
    sourceFile = OpenFile
    destinationPath = "..."
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    sourceFile
    Call fso.CopyFile(sourceFile, "destinationPath")
    SheetName.Range("A1").Value = destinationPath
End Sub

Look at this post at StackOverflow for this same solution, or the Microsoft Documentation for FileSystemObjects for all the possible things you can do.

 

[ashley12]

Thanks so much *Severynm. I'll give it a try