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

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

severynm

Board Regular
Joined
Jan 8, 2021
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
''==========================================================================================================================
'' 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:
VBA Code:
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.
 
Solution

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Thanks so much *Severynm. I'll give it a try 😄
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,765
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top