VBA Chapter 13 of 24: VBA for Excel for the Workbooks

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 chapter on events.

About the PATH

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.

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

Discover Even More in 50 Excel spreadsheets