Macros with Multiple Files

 

« Back to videos

Additional Information:

The most common use for macros is to save time through automation. We do not want to limit ourselves to processing just the file that contains our code. Referring to multiple files will necessarily complicate our code.

In the video, you will see that we only process one other file at any given time. This means we can refer to our files as ThisWorkbook e.g. the one in which our code is written, and ActiveWorkbook/ActiveSheet e.g. the file displayed on screen that we have just opened. This is useful because, when we open a file, we have no control over its structure.

For example, we may be expecting the file to contain a sheet called "DataSheet". However, if we refer to Sheets("DataSheet") and no such sheet is present, Visual Basic will error and require debugging. Unfortunately, the only certainty we have is that Excel will have opened our file and attempted to display it on a spreadsheet. By only opening one file, we can refer to ActiveWorkbook and ActiveSheet, and so avoid unexpected errors.

This may appear limiting but there is no reason for processing multiple files at the same time. Ultimately you are either extracting data from an input file, or writing data to an output file. Most the time, it will make sense to embed the code in one of those files, generally the output. Even where this is not the case, you can grab all the input data, store it in the coded file, then feed it to an output file. Although this sounds inefficient, it means you can manipulate the data in the coded file. This makes it easier to develop and check your macro.

If you ever need to have three workbooks open at the same time, you can refer to locations by workbook and sheet e.g. Workbooks("wbkGeneric").Sheets("shtGeneric").Range/Cells. You can also assign "wbkGeneric" to a variable when you first open that file e.g. wbkName = ActiveWorkbook.Name. You would then replace "wbkGeneric" with wbkName in the above expression. This is useful if you do not know what the various workbooks or sheets are called.

This training course has been developed in Excel 2003 but all the tips and tricks still work in Excel 2007 and 2010. However, issues may arise when we refer to files. This is because the standard Excel file format has changed from ".xls" to ".xlsx/xlsm". Due to compatibility issues, you should always save your Excel files in ".xls" (Excel 97-03) format if there is any possibility of them being opened in pre-2007 versions of Excel. You should also ensure that filenames in your code have the ".xls" extension for the same reason.

If you use the SaveAs command on a text file in Excel 2007, it will save the file in "xlsx" format. If you have specified ".xls" in the filename e.g. ActiveWorkbook.SaveAs FileName:="TextFile.xls", the inconsistency may confuse Windows next time you wish to open the file. To ensure files do not default to saving in ".xlsx/xlsm" formats, you should specify the file format using ActiveWorkbook.SaveAs FileName:="TextFile.xls", FileFormat:=ThisWorkbook.FileFormat. This means that, if your code is in an "xls" file, any subsequent files created will adopt the same format.