Copying images to a folder

Ongoing tutorial - First part is here: Part One

 

If you want to copy a file you can use the FileCopy method. This method needs a source and a destination. The source is the location on your computer where the file is; the destination is which folder you want to copy it to.

Set up the following three variables in your code:

Dim ImageFolderPath As String
Dim SourceImage As String
Dim DestinationImage As String

We can get the destination folder by calling our NavigateFromWorkBookPath function again and adding the images folder:

ImageFolderPath = NavigateFromWorkBookPath( )

ImageFolderPath = ImageFolderPath & "images\"

We can also add the name of the image to the end of the file path above:

DestinationImage = ImageFolderPath & ImageName

The ImageName variable is the one we set up earlier, in the General Declarations area.

We also set the location of the new image in the General Declarations area, in the CopyImage variable:

SourceImage = CopyImage

We don't really need to set up a new variable called SourceImage, but it's there just to make things clearer when we copy the file.

When we copy the image over, we can test to see if the folder already contains an image of this name.

If SourceImage < > DestinationImage Then

FileCopy SourceImage, DestinationImage

End If

The If Statement checks to see if SourceImage does not equal DestinationImage. If it does then we know the file already exists in the images folder. If they are not equal then we can go ahead and copy it:

FileCopy SourceImage, DestinationImage

The FileCopy method needs the path of the source file first. After a comma, you type the destination file path. We have both of these in our SourceImage and DestinationImage variables.

If you like, you can add a message at the end, just to show that everything went OK:

MsgBox "New Image Information Added"

The whole of the code for the cmdSave button looks like this:

Excel VBA code to save User Form information to a spreadsheet

And that's it - the whole programme done! Test it out. Run your form and select a new image. Click your Save New Details button and then return to your spreadsheet. You should find that a new row has been added.

 

This has been a long project. But it should have given you lots of experience at creating and coding user forms. There's obviously a lot more to them, but experiment for yourself and it should bring your VBA skills on a treat!

 

In the next section of this course, we move on and look at how to add charts with VBA. You'll also learn how to add a chart to an Excel User Form