Chapter 4
In This Chapter
Creating a workbook
Saving a workbook when a cell is changed
Saving a workbook before you close it
Protecting and unprotecting a workbook
Opening a user-defined workbook
Determining whether a workbook is open or exists
Printing all workbooks in a directory
Creating backups of your workbook
A workbook is not just an Excel file; it's also an object in Excel's Object model (a programming hierarchy that exposes parts of Excel to VBA).
You can reference workbooks through VBA to do cool things like automatically create new workbooks, prevent users from closing workbooks, and automatically back up workbooks. In this chapter, you explore a few of the more useful workbook-related macros.
You may sometimes want or need to create a workbook in an automated way. For instance, you may need to copy data from a table and paste it into a newly created workbook. The following macro copies a range of cells from the active sheet and pastes the data into a new workbook.
As you'll see when you read through the lines of the code, this macro is relatively intuitive:
Sub Macro1()
'Step 1 Copy the data
Sheets("Example 1").Range("B4:C15").Copy
'Step 2 Create a new workbook
Workbooks.Add
'Step 3 Paste the data
ActiveSheet.Paste Destination:=Range("A1")
'Step 4 Turn off application alerts
Application.DisplayAlerts = False
'Step 5 Save the newly created workbook
ActiveWorkbook.SaveAs _
Filename:="C:TempMyNewBook.xlsx"
'Step 6 Turn application alerts back on
Application.DisplayAlerts = True
End Sub
In Step 1, you simply copy the data that ranges from cells B4 to C15. Note that you specify both the sheet and the range by name. This approach is a best practice when working with multiple open workbooks.
In Step 2, you use the Add method of the Workbook object to create a workbook. The blank workbook is equivalent to manually choosing File ⇒ New ⇒ Blank Document in the Excel ribbon.
In Step 3, you use the Paste method to send the copied data to cell A1 of the new workbook.
Pay attention to the fact that the code refers to the ActiveSheet object. When you add a workbook, the new workbook immediately gains focus, becoming the active workbook. (Excel does the same when you add a workbook manually.)
In Step 4 of the code, you set the DisplayAlerts method to False, effectively turning off Excel’s warnings. You do this because in the next step of the code, you save the newly created workbook. You may run this macro multiple times, in which case Excel attempts to save the file multiple times.
What happens when you try to save a workbook multiple times? That’s right — Excel warns you that there is already a file with that name and then asks if you want to overwrite the previously existing file. Because your goal is to automate the creation of the workbook, you want to suppress that warning.
In Step 5, you save the file by using the SaveAs method. Note that you enter the full path of the save location, including the final filename.
Because you turned off application alters in Step 4, you need to turn them back on (see Step 6). If you don’t, Excel continues to suppress all warnings during the current session.
To implement this macro, you can copy and paste it into a standard module:
Type or paste the code in the newly created module.
You'll probably need to change the sheet name, the range address, and the save location.
Sometimes, you may be working on data that is so sensitive that you'll want to save every time a particular cell or range of cells is changed. The next macro allows you to define a range of cells that, when changed, forces the workbook to save.
In the example demonstrated in Figure 4-1, you want the workbook to save when an edit is made to any of the cells in the range C5:C16.
The secret to this code is the Intersect method. Because you don’t want to save the worksheet when any old cell changes, you use the Intersect method to determine if the target cell (the cell that changed) intersects with the range specified as the trigger range (C5:C16 in this case).
The Intersect method returns one of two things: a Range object that defines the intersection between the two given ranges, or nothing. So in essence, you need to throw the target cell against the Intersect method to check for a value of Nothing. At that point, you can decide whether to save the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
'Step 1: Does the changed range intersect?
If Intersect(Target, Range("C5:C16")) Is Nothing Then
'Step 2: If there is no intersection, exit procedure
Exit Sub
'Step 3: If there is an intersection, save the workbook
Else
ActiveWorkbook.Save
'Step 4: Close out the If statement
End If
End Sub
In Step 1, you simply check to see whether the target cell (the cell that has changed) is in the range specified by the Intersect method. A value of Nothing means the target cell is outside the range specified.
Step 2 forces the macro to stop and exit the procedure if there is no intersection between the target cell and the specified range.
If there is an intersection, Step 3 fires the Save method of the active workbook, overwriting the previous version.
In Step 4, you simply close out the If statement. Every time you start an If-Then-Else check, you must close it out with a corresponding End If.
To implement this macro, you need to copy and paste it into the Worksheet_Change event code window. Placing the macro here allows it to run each time you make any change to the sheet:
The macro presented in this section is an excellent way to protect users from inadvertently closing their file before saving. When implemented, this macro ensures that Excel automatically saves the workbook before closing it.
The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The crux of the code is simple — it asks the users whether they want to close the workbook (see Figure 4-3). The macro then evaluates whether the user clicked OK or Cancel.
The evaluation is done with a Select Case statement. The Select Case statement is an alternative to the If-Then-Else statement, allowing you to perform condition checks in your macros. The basic construct of a Select Case statement is simple:
Select Case <some expression to check>
Case Is = <some value>
<do something>
Case Is=<some other value>
<do something else>
Case Is=<some 3rd value>
<do some 3rd thing>
End Select
With a Select Case statement, you can perform many conditional checks. In this case, you are simply checking for OK or Cancel. Take a look at the code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Activate the message box and start the check
Select Case MsgBox("Save and close?", vbOKCancel)
'Step 2: Cancel button pressed, so cancel the close
Case Is = vbCancel
Cancel = True
'Step 3: OK button pressed, so save the workbook and close
Case Is = vbOK
ActiveWorkbook.Save
'Step 4: Close your Select Case statement
End Select
End Sub
In Step 1, you activate the message box as the condition check for the Select Case statement. You use vbOKCancel argument to ensure that the OK and Cancel buttons are presented as choices.
In Step 2, if the user clicked Cancel in the message box, the macro tells Excel to cancel the Workbook_Close event by passing True to the Cancel Boolean.
If the user clicked the OK button in the message box, Step 3 takes effect. Here, you tell Excel to save the workbook. And because you didn't set the Cancel Boolean to True, Excel continues with the close.
In Step 4, you simply close out the Select Case statement. Every time you instantiate a Select Case, you must close it out with a corresponding End Select.
To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro there allows it to run each time you try to close the workbook:
Sometimes you need to send your workbook out into the world with specific worksheets protected. If you find that you’re constantly protecting and unprotecting sheets before distributing your workbooks, the macro in this section can help you.
The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The macro automatically protects the specified sheet with the given password, and then saves the workbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Protect the sheet with a password
Sheets("Sheet1").Protect Password:="RED"
'Step 2: Save the workbook
ActiveWorkbook.Save
End Sub
In Step 1, you're explicitly specifying which sheet to protect — Sheet1, in this case. You also provide the password argument, Password:="RED", which defines the password needed to remove protection.
This password argument is optional. If you omit it, the sheet will still be protected, but you won’t need a password to unprotect it.
Step 2 tells Excel to save the workbook. If you don’t save the workbook, the sheet protection you just applied won’t be in effect the next time the workbook is opened.
To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:
Note that you can protect additional sheets by adding addition statements before the Activeworkbook.Save statement.
If you’ve distributed workbooks with protected sheets, you likely get the workbooks back with the sheets still protected. Often, you need to unprotect the worksheets in a workbook before continuing your work. If you find that you are continuously unprotecting worksheets, this section’s macro may be just the ticket.
The code is triggered by the workbook’s Open event. When you open a workbook, this event triggers, running the code within. This macro automatically unprotects the specified sheet with the given password when the workbook is opened:
Private Sub Workbook_Open()
'Step 1: Protect the sheet with a password
Sheets("Sheet1").Unprotect Password:="RED"
End Sub
The macro explicitly names the sheet you want to unprotect — Sheet1, in this case. Then it passes the password required to unprotect the sheet.
To implement this macro, you need to copy and paste it into the Workbook_Open event code window. Placing the macro here allows it to run each time the workbook is opened:
In some situations, it's imperative that your workbook be started on a specific worksheet. With the next macro, if users are working with your workbook, they can't go astray because the workbook starts on the exact worksheet it needs to.
In the example illustrated in Figure 4-7, you want the workbook to go immediately to the sheet called Start Here.
This macro uses the workbook’s Open event to start the workbook on the specified sheet when the workbook is opened:
Private Sub Workbook_Open()
'Step 1: Select the specified sheet
Sheets("Start Here").Select
End Sub
The macro explicitly names the sheet the workbook should jump to when it's opened.
To implement this macro, you need to copy and paste it into the Workbook_Open event code window. Placing the macro here allows it to run each time the workbook is opened:
Want to give yourself or your users a quick way to search for and open a file? The next macro uses a simple technique that opens a friendly dialog box, allowing you to browse for and open the Excel file of your choosing.
This macro opens the dialog box you see in Figure 4-9, allowing the user to browse for and open an Excel file.
Here's how this macro works:
Sub Macro1()
'Step 1: Define a String variable
Dim FName As Variant
'Step 2: GetOpenFilename Method activates dialog box
FName = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks,*.xl*", _
Title:="Choose a Workbook to Open", _
MultiSelect:=False)
'Step 3: If a file was chosen, open it!
If FName <> False Then
Workbooks.Open Filename:=FName
End If
End Sub
In Step 1, the macro declares a Variant variable that holds the filename that the user chooses. FName is the name of your variable.
In Step 2, you use the GetOpenFilename method to call up a dialog box that allows you to browse and select the file you need.
The GetOpenFilename method supports a few customizable parameters. The FileFilter parameter allows you to specify the type of file you're looking for. The Title parameter allows you to change the title that appears at the top of the dialog box. The MultiSelect parameter allows you to limit the selection to one file.
If the user selects a file from the dialog box, the FName variable is filled with the chosen filename. In Step 3, you check for an empty FName variable. If the variable is not empty, you use the Open method of the Workbooks object to open the file.
To implement this macro, you can copy and paste it into a standard module:
(Optional) Assign the macro to a button.
For details, see the section on assigning a macro to a button in Chapter 1.
The previous macro automatically opened a workbook based on the user’s selection. As you think about automatically opening workbooks, consider what may happen if you attempt to open a book that is already open. In the non-VBA world, Excel attempts to open the file again, with the message shown in Figure 4-10 warning that any unsaved changes will be lost. You can protect against such an occurrence by checking whether a given file is already open before trying to open it again.
The first thing to notice about this macro is that it is a function, not a Sub procedure. As you will see, making this macro a function enables you to pass any filename to it to test whether that file is already open.
The gist of this code is simple. You're testing a given filename to see if it can be assigned to an Object variable. Only opened workbooks can be assigned to an Object variable. When you try to assign a closed workbook to the variable, an error occurs.
If the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is closed.
Function FileIsOpenTest(TargetWorkbook As String) As Boolean
'Step 1: Declare your variables
Dim TestBook As Workbook
'Step 2: Tell Excel to resume on error
On Error Resume Next
'Step 3: Try to assign the target workbook to TestBook
Set TestBook = Workbooks(TargetWorkbook)
'Step 4: If no error occurred, workbook is already open
If Err.Number = 0 Then
FileIsOpenTest = True
Else
FileIsOpenTest = False
End If
End Function
The first thing the macro does is to declare a String variable that will hold the filename that the user chooses. TestBook is the name of your String variable.
In Step 2, you tell Excel that there may be an error running this code an, in the event of an error, resume the code. Without this line, the code would simply stop when an error occurs. Again, you test a given filename to see if it can be assigned to an Object variable. If the given workbook can be assigned, it’s open; if an error occurs, it’s closed.
In Step 3, you attempt to assign the given workbook to the TestBook Object variable. The workbook you try to assign is a String variable called TargetWorkbook. TargetWorkbook is passed to the function in the function declarations (see the first line of the code). This structure eliminates the need to hard-code a workbook name, allowing you to pass it as a variable instead.
In Step 4, you simply check to see if an error occurred. If an error did not occur, the workbook is open, so you set the FileIsOpenTest to True. If an error occurred, the workbook is not open and you set the FileIsOpenTest to False.
The following macro demonstrates how to implement this function. Here, you use the same macro you saw in the previous section, “Opening a Specific Workbook Defined by the User,” but this time, you call the new FileIsOpenTest function to make sure that the user cannot open an already opened file:
Sub Macro1()
'Step 1: Define a String variable
Dim FName As Variant
Dim FNFileOnly As String
'Step 2: GetOpenFilename Method activates dialog box
FName = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks,*.xl*", _
Title:="Choose a Workbook to Open", _
MultiSelect:=False)
'Step 3: Open the chosen file if not already opened
If FName <> False Then
FNFileOnly = StrReverse(Left(StrReverse(FName), _
InStr(StrReverse(FName), "") - 1))
If FileIsOpenTest(FNFileOnly) = True Then
MsgBox "The given file is already open"
Else
Workbooks.Open Filename:=FName
End If
End If
End Sub
With this macro implemented, you get the friendlier message box shown in Figure 4-11.
To implement this macro, you can copy and paste both pieces of code into a standard module:
(Optional) Assign the macro to a button.
For details, see the section on assigning a macro to a button in Chapter 1.
You may have a process that manipulates a file somewhere on your PC. For example, you may need to open an existing workbook to add data to it on a daily basis. In this case, you may need to test to see whether the file you need to manipulate exists. The macro described in this section allows you to pass a file path to evaluate whether the file is there.
The first thing to notice about this macro is that it is a function, not a Sub procedure. Making this macro a function enables you to pass any file path to it.
In this macro, you use the Dir function, which returns a string that represents the name of the file that matches what you pass to it. This function can be used in lots of ways, but here, you use it to check whether the file path you pass to it exists:
Function FileExists(FPath As String) As Boolean
'Step 1: Declare your variables
Dim FName As String
'Step 2: Use the Dir function to get the filename
FName = Dir(FPath)
'Step 3: If file exists, return True; else False
If FName <> "" Then FileExists = True _
Else: FileExists = False
End Function
Step 1 declares a String variable that holds the filename that returns from the Dir function. FName is the name of the String variable.
In Step 2, you attempt to set the FName variable. You do this by passing the FPath variable to the Dir function. This FPath variable is passed via the function declarations (see the first line of the code). This structure prevents you from having to hard-code a file path, passing it as a variable instead.
If the FName variable can’t be set, the path you passed does not exist. Thus the FName variable is empty. Step 3 merely translates that result to a True or False expression.
The following macro demonstrates how to use this function:
Sub Macro1()
If FileExists("C:TempMyNewBook.xlsx") = True Then
MsgBox "File exists."
Else
MsgBox "File does not exist."
End If
End Sub
To implement this macro, you can copy and paste both pieces of code into a standard module:
One of the more annoying things in Excel is closing many workbooks at once. For each workbook you've opened, you need to activate the work, close it, and confirm the saving of changes. Excel has no easy way to close them all at once. This little macro takes care of that annoyance.
In this macro, the Workbooks collection loops through all opened workbooks. As the macro loops through each workbook, it saves and closes them down:
Sub Macro1()
'Step 1: Declare your variables
Dim wb As Workbook
'Step 2: Loop through workbooks, save and close
For Each wb In Workbooks
wb.Close SaveChanges:=True
Next wb
End Sub
Step 1 declares an Object variable that represents a Workbook object. This allows you to enumerate through all the open workbooks, capturing their names as you go.
Step 2 simply loops through the open workbooks, saving and closing them. If you don’t want to save them, change the SaveChanges argument from True to False.
The best place to store this macro is in your personal macro workbook. This way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.
If you don’t see personal.xlb in your project window, it doesn't exist yet. You’ll have record a macro using personal macro workbook as the destination.
If you need to print from multiple workbooks in a directory, you can use the macro presented in this section.
In this macro, you use the Dir function to return a string that represents the name of the file that matches what you pass to it.
You use the Dir function to enumerate through all .xlsx files in a given directory, capturing each file’s name. Then you open each file, print it, and then close it.
Sub Macro1()
'Step 1:Declare your variables
Dim MyFiles As String
'Step 2: Specify a target directory
MyFiles = Dir("C:Temp*.xlsx")
Do While MyFiles <> ""
'Step 3: Open workbooks one by one
Workbooks.Open "C:Temp" & MyFiles
ActiveWorkbook.Sheets("Sheet1").PrintOut Copies:=1
ActiveWorkbook.Close SaveChanges:=False
'Step 4: Next file in the directory
MyFiles = Dir
Loop
End Sub
Step 1 declares the MyFiles String variable that will capture each filename in the enumeration.
Step 2 uses the Dir function to specify the directory and file type you are looking for. Note that the code is looking for *.xlsx, so only xlsx files will be looped through. If you want to look for .xls files, you will need to specify that (along with the directory you need to search). The macro passes any filename it finds to the MyFiles String variable.
Step 3 opens the file and then prints one copy of Sheet1. Needless to say, you will probably want to change which sheets to print. You can also change the number of copies to print.
Step 4 loops back to find more files. If there are no more files, the MyFiles variable is blank and the loop and the macro end.
To implement this macro, you can copy and paste it into a standard module:
There are times when you don’t want a user closing out a workbook without entering a specific piece of data. In these situations, it would be useful to deny the user the ability to close the workbook until the target cell is filled in (see Figure 4-12). This is where the next macro comes in.
This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. This macro checks to see if the target cell (cell C7, in this case) is empty. If it is empty, the close process is cancelled. If C7 is not empty, the workbook is saved and closed:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Check to see if cell C7 is blank
If Sheets("Sheet1").Range("C7").Value = "" Then
'Step 2: If cell is blank, cancel the close and tell user
Cancel = True
MsgBox "Cell C7 cannot be blank"
'Step 3: If cell is not blank, save and close
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub
Step 1 checks to see whether C7 is blank.
If C7 is blank, Step 2 takes effect, cancelling the close process by passing True to the Cancel Boolean. Step 2 also activates a message box notifying the user of his or her stupidity (well, it's not quite that harsh, really).
In Step 3, if cell C7 is not blank, the workbook is saved and closed.
To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:
You know that backing up your work is important. Now you can have a macro do it for you. This simple macro saves your workbook to a new file with today’s date as part of the name.
The trick to this macro is piecing together the new filename as the path, today’s date, and the original filename.
The path is captured by using the Path property of the ThisWorkbook object. Today's date is grabbed with the Date function.
By default, the Date function returns mm/dd/yyyy. Forward slashes would cause the file save to fail, so you format the date using hyphens instead (Format(Date, "mm-dd-yy")) because Windows does not allow forward slashes in filenames.
The last piece of the new filename is the original filename. You capture it by using the Name property of the ThisWorkbook object:
Sub Macro1()
'Step 1: Save workbook with new filename
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "" & _
Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.Name
End Sub
In the one and only step, the macro builds a new filename and uses the SaveCopyAs method to save the file.
To implement this macro, you can copy and paste it into a standard module:
3.145.111.116