 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
Saving
With this Method we can Save a Workbook as it's existing name or as another name and path. We can trick Excel into thinking a Workbook is already had it's changes Saved. This may also be a good time to introduce the two methods available to refer to the active Workbook. The first one is:
Sub SaveActiveWorkbook()
ActiveWorkbook.Save
End Sub
The second is:
Sub SaveThisWorkbook()
ThisWorkbook.Save
End Sub
Both of these Methods will Save the a Workbook as it's existing name. There is one small (which could be huge) difference with these two methods and that is:
"ActiveWorkbook" always refers to the Workbook that happens to be active at the time of running the code.
While "ThisWorkbook" always refers to the Workbook that houses the code, regardless of which workbook happens to be the Active Workbook.
Each Method is good in that they are generic (we don't need to know the name of the Workbook). But use them in their wrong context and you could end up accessing the wrong Workbook. I generally use "ThisWorkbook" with the exception of the Workbook being an Add-in. An Add-in is a Workbook that has been saved as such ( *.xla). Once it has been saved it will always open as a Hidden Workbook.
As I motioned above we can trick Excel into thinking any changes to a Workbook have been saved, to do this we would use:
Sub TrickExcelToThinkWorkbookIsSaved()
ThisWorkbook.Saved = True
End Sub
Run this code immediately prior to closing a Workbook that has had changes and Excel will think the Workbook has already been saved and close without saving.
Activating
While we can (in theory) have an infinite number of Workbooks open at any one time we can only ever have one of them Active at any one time. At times we may need to Activate another Workbook so we can do something with it via VBA. If we try to access another Workbooks Objects etc while it is NOT Open we will encounter a Run time error! So it is important that the Workbook is Open. It is rare that we would need to Activate it, but if we do We can do this easily if we know it's name like this:
Sub ActivateAnotherWorkbookViaName()
Workbooks("Book2").Activate
End Sub
Problem is we may not always know the name of any other open Workbook so we need to either find out it's name or use it's Index number, like this:
Sub ActivateAnotherWorkbookViaIndex()
Workbooks(3).Activate
End Sub
An important note here is, the index number is the same as the order in which the workbooks were opened. Workbooks(1) is the first workbook opened, Workbooks(2) is the second Workbook opened and so on... Activating a Workbook won't change its index number. All open workbooks are included in the index count, even if they are hidden.
Closing
As with Open Method of a Workbook the Close Method also takes arguments, which again are all optional. To close the open Workbook you would use:
Sub CloseThisWorkbook()
ThisWorkbook.Close
End Sub
This would Close the Workbook and Prompt the user to save any changes.
Sub CloseThisWorkbookAndSave()
ThisWorkbook.Close SaveChanges:=True
End Sub
This would Save the Workbook without prompting and then Close. Of course using "False" in place of "True" would Close the Workbook and not save any changes.