Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-5: VBA for Excel for the Workbooks
(Levels: Intermediate, Advanced)

To develop a VBA procedure that is triggered by an event relating to the workbook (when you open it, when you save it...) see the lesson on events.

In alphabetical order here are the 15 VBA words that you need to learn to work efficiently with workooks:

Activate, ActiveWorkbook, Close, Kill, Name, Names, OpenText, Path, PrintOut, Save, Saved, SaveAs, ThisWorkbook, Windows, Workbooks

About the PATH

If you are working with many different workbooks within your VBA procedure read this segment on PATH if not come back to it later.

Before we talk about workbooks let's talk about the path. The path is the directory that Excel points at when you use "File/Open" , File/Save As" or "File/Save" . If you open an Excel file from Explorer the "File/Open" path is the directory  by default of your version of Excel (usually C:/My Documents) if you have not changed it "Tools/Options/Save").   The "File/Save As" or "File/Save" path is the directory of the file you have just opened. On the contrary if you open Excel first and from there you open a file all  paths are  the directory of the file that you have just opened.

This reality becomes a real concern when you start opening and saving more than one workbook within a VBA procedure. For example if you create a new workbook within a VBA procedure the line of code: ActiveWorkbook. SaveAs "Test.xls" will save the workbook in the default directory if you have opened the workbook containing the VBA procedure from Explorer but will save the workbook in the directory of the workbook that contains the VBA procedure if it has been opened through Excel. You might need to write: ActiveWorkbook. SaveAs ThisWorkbook.Path & "/" & " Test.xls" to save it in the same directory as the workbook containing the VBA procedure.

If you want to change the directory at the beginning of the procedure you can use:
ChDir ThisWorkbook.Path
and " File/Open, " File/Save As" or " File/Save" will all point at the directory that contains the VBA Procedure.

You can also use the  following  approach to make sure where you save your workbooks or open them from:

Sub proTest()
Dim varPath as String

          varPath= ThisWorkbook.Path
or you can open another workbook from the VBA procedure and set the path to this workbook
          Workbooks.Open " book1.xls"
          varPath=ActiveWorkbook.Path
then you can open or save workbooks with these lines of code
          Workbooks.Open varPath & " /" & " book1.xls"
          Workbooks.SaveAs varPath & " /" & " book1.xls"

End Sub

The other important " objects"

What you call a spreadsheet or an Excel file VBA calls it a workbook. So if somebody talks about workbooks you may suspect that this person knows about VBA.

For very basic code working with only one workbook you need to know about the difference between ActiveWorkbook and ThisWorkbook. ThisWorkbook is the workbook within which your VBA procedure runs as the ActiveWorkbook is the workbook that is live on screen. There can be many workbooks opened at the same time but the ActiveWorkbook    is the one that you see when you look at Excel.

So you can understand that if ThisWorkbook is live on screen it is also the ActiveWorkbook and you can use either objects.

There are many methods and properties that you can use with ActiveWorkbook and ThisWorkbook. You can Save, Close or Print the workbook with the following code:

ThisWorkbook.Close this code will close the workbook and end the procedure that is running.
ActiveWorkbook.Close

If you want to close ThisWorkbook without saving it:
ThisWorkbook.Saved = True
ThisWorkbook.Close

If you want to close the active workbook without saving it:
ActiveWorkbook.Saved = True
ActiveWorkbook.Close

You can  save the workbook that is active whit this code:
ActiveWorkbook.Save
ThisWorkbook.Save

When you want to save the active workbook under another name or in another directory you will use the following code:
ActiveWorkbook.SaveAs " suchAndSuch.xls" but make sure that you have read the segment on Path above.
To make sure that your workbook is saved in the right directory you can use both the name and the path:
ActiveWorkbook.SaveAs " C:/suchAndSuch.xls"

To print an entire workbook you will code:
ThisWorkbook.PrintOut
or
ActiveWorkbook.PrintOut

A little extra

When you have saved a workbook on your disk and you want to remove it you can use the KILL statement:
Kill " C:\myFile.xls"
Note: You cannot kill any workbook that is opened including ThisWorkbook

Working with Many Workbooks and Files

When you start working with more than the workbook in which resides your VBA procedure you need to add a few objects  to your vocabulary: Workbooks and  Windows.

Opening another workbook

To open another workbook within a VBA procedure you simply write (notice that " Workbooks" is plural):
Workbooks.Open " suchAndSuch.xls" but make sure that you have read the segment on Path above.
To make sure that you are looking in the right directory use both the path and the filename
Workbooks.Open " C:\suchAndSuch.xls"

You might not  want to hard code the name of the file (so that if it changes you don't have to modify the code). Enter the name of the file in cell " A1" of sheet1 for example and write this:
Workbooks.Open Sheets("sheet1").Range("A1").Value

The way I prefer to do it is by storing the name of the file in a variable of the string type like this:
Sub proTest()
Dim varFileName as String
          varFileName=Sheets("sheet1").Range("A1").Value
          Workbooks.Open varFileName
End Sub

You can also write the path in a cell and the file name in another cell:
Workbooks.Open Sheets("sheet1").Range("A1").Value & Sheets("sheet1").Range("A2").Value
You can also work with 2 variables for the path and the file name as shown above:
Workbooks.Open varPath & varFileName

For both approaches it is important to know if the final backslash for the path has been entered in the cell or in the variable (C:\Documents vs C:\Document\). You can either create a text box to ask the user to make sure that the final backslash is there or use an IF statement to avoid any problem. For example if the path is entered in cell A1 of Sheet1 this is the code:

If Left(Sheets("sheet1").Range("A1").Value,1)=" \" than
            Workbooks.Open Sheets("sheet1").Range("A1").Value & Sheets("sheet1").Range("A2").Value
Else
        Workbooks.Open Sheets("sheet1").Range("A1").Value & " \"   & Sheets("sheet1").Range("A2").Value
End If

If you  need to open a TXT, CSV or any other type of files you can  use the macro recorder to do it and you will get something like this:
Workbooks.OpenText Filename:= _
                " C:\Book1.txt" , Origin:=xlMSDOS, _
                StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
                Space:=True, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers _
                :=True
If I don't want the path and file name to be hard coded I use the same approach as shown above for Workbooks.Open using addresses or  variables replacing " C:\Book1.txt" by the address or the variable name (no quotation marks).

I then delete the  ,TrailingMinusNumbers:=True because certain older versions of Excel don't understand this argument.

A little extra

If you want the user to choose a file from the regular " Open" dialog window you write this code:
Application.Dialogs(xlDialogOpen).Show

Closing, Saving and Printing Workbooks

You have learned how to close the ActiveWorkbook or ThisWorkbook above but you can also close  a workbook that is open but not active with:
Workbooks("Book1.xls").Close
If you don't want to hard code the name of the file you can use the address of a cell in which you have entered the file name or a variable in which you have stored the file name as above with the " Open" method.
Workbooks(Range("A1").Value).Close
Workbooks(varFileName).Close
Notice the absence of double quotes in the last two sentences.

You have learned how to save  the ActiveWorkbook or ThisWorkbook above but you can also save a workbook that is open but not active with:
Workbooks("Book1.xls").Save
If you don't want to hard code the name of the file you can use the address of a cell in which you have entered the file name or a variable in which you have stored the file name as above with the " Open" method.
Workbooks(Range("A1").Value).Save
Workbooks(varFileName).Save
Notice the absence of double quotes in the last two sentences.

You have learned how to saveAs  the ActiveWorkbook or ThisWorkbook above but you can also save a workbook that is open but not active with:
Workbooks("Book1.xls").SaveAs " Such and Such.xls"
If you don't want to hard code the name of the file you can use the address of a cell in which you have entered the file name or a variable in which you have stored the file name as above with the " Open" method.
Workbooks(Range("A1").Value).SaveAs Range("A2").Value
Workbooks(varFileName).SaveAs varOtherFilename
Notice the absence of double quotes in the last two sentences.

To save an Excel workbook as TXT, CSV or any other type of file use the macro recorder and as shown above modify the code to suit your needs.

To print an entire workbook you will code:
Workbooks("Book1.xls").PrintOut

A little Extra

If you want the user to choose a directory and create a file name in the regular " SaveAs" dialog window you write this code:
Application.Dialogs(xlDialogSaveAs).Show

Moving around Workbooks

When two workbooks are open and you want to move from one to the other you will use:
ThisWorkbook.Activate and Windows("OtherWorkbook.xls").Activate

When I work with two workbooks I usually declare a variable in which I store the name of the second workbook and I move from one to the other with ThisWorkbook.Activate and Windows(varThatWorkbook).Activate. Here is an  example:

Sub proTest()
Dim varThatWorkbook as String

          Workbooks.Open " book1.xls"
          varThatWorkbook=ActiveWorkbook.Name
and then I move from one to the other until I close the other workbook
          ThisWorkbook.Activate
          Windows(varThatWorkbook).Activate
          Windows(varThatWorkbook).Close

End Sub

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple