Often when we record a Macro we find that it will work for a week or a month or a particular file – but we have to re-record it in order to make it work for another file or time period. In order to make a macro more useful – we can try to generalise it and remove any unwanted statements that tie it to specific values, ranges or files. There are many different techniques involved in doing this but here is one approach which is easy to start with which is often useful.

Let’s suppose we’ve recorded a macro that imports a text file into the current workbook. We’ve recorded the Macro and it works great – our problem is next week it’s a different text file and we’ll have to go through the whole process again – defeating the value of our macro. One thing we can do quite quickly is ask Excel to prompt for the name of the file to import at the start of the macro.

Here’s a sample macro we’ve recorded that will import a specific textfile into the current workbook (in this case a file called “import.txt”:

If you look carefully at the code – you’ll see that the file is specified in the FILENAME:= parameter. We want to generalise this macro so that it prompts us for the file to import rather than importing this file all the time.

Let’s edit the macro and before the workbooks.OpenText statement enter the following:

Dim myFile as string

    myFile = Application.GetOpenFilename(“Text Files,*.txt”)

The DIM command creates a local variable called “myfile” which will hold the name of the file the user chooses.

The next line prompts the user to choose a text file they wish to open and puts the users choice into our new variable myFile. Notice that as soon as you press the “.” after the wordapplication you will notice a list of properties and methods that are available for the application object appear. We can choose “GetOpenFileName” from that list.

Now we change the highlighted lines below to refer to the file the user has picked instead of the hard-wired file name that was recorded with the macro.

And that’s it. Now when we run the Macro a pop-up window like this will appear:

And when we select the file Excel will continue with the Macro using the file we chose in the dialog box.

Editing macros to take out specific references to files that may change is one step in make a macro more flexible and reusable. We’ll look at other techniques in the another article.