A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.

The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.

To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks.

[Read more…] about Loop Through Subfolders using FileSystemObject

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation.

[Read more…] about Email Workbook as an Attachment – Excel VBA Macro

In this blog post, we will explore how to pick names at random from a list using Excel VBA.

Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.

These names must be selected at random and be unique. You cannot select the same name more than once.

The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.

This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. This macro demonstrates some key VBA techniques including a Do While and a For Next loop. It also uses variables including a string array and an If statement.

Want to improve your Excel VBA skills. Take our online course to master Excel VBA quickly.

[Read more…] about Pick Names at Random from a List – Excel VBA

This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of the workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.

By creating a macro this seemingly repetitive task can be performed at the click of a button.

Although all sheets are used in this example. The macro can be adapted to include all except the first sheet, or some other variation.

The code below can be copied into a VBA module and adapted to your needs. An explanation for the main parts of the code can be found below.

[Read more…] about Export All Sheets to PDF – Excel VBA

If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.

You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.

Adding a Hyperlink to a Userform

In this example, we will add a hyperlink so users may email an enquiries department from the userform.

[Read more…] about Add a Hyperlink to a Userform – Excel VBA