- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: Activate, Open, and, Close Excel Workbooks
Moving between sheets in a workbook
One of the first challenges you will find when beginning to write VBA is moving between sheets.
You will soon find this is no challenge at all.
You can select as sheets as follows:
Sheets("Sheet Name").select
Once you begin to implement VBA for real tasks. It won't be long before you find the need to work with other workbooks. To do this I you will use the workbooks method.
Open a Workbook
Workbooks.open(filepathasstring)
Open Read-only
Workbooks.open(filepathasstring,readonly)
Close A Workbook
Close Without saving changes
Workbooks.close(filepathasstring,false)
Close and Save Changes
Workbooks.close(filepathasstring,true)
Toggle Between Workbooks
Code will be executed on the active workbook.
To activate a workbook
Workbooks(filenameasstring).activate
To return the name of the active workbook
Activeworkbook.name
It is handy to store the filename as string after opening the file in case it ever changes.
This Workbook Method
When working with the workbook in which the code reside you can use the thisworkbook method.
To Close Without saving changes
Thisworkbook.close false
To Close and Save changes
Thisworkbook.close true
To activate
Thisworkbook.activate
A Sample Macro
Sub testmynewskills() 'defines string variables Dim filepath As String Dim filename As String 'store file past locate in cell A1 in the variable file path filepath = Range("A1").Value 'opens the file path in cell A1 Workbooks.Open (filepath) 'store the filename of the newly open workbook to the variable filename filename = ActiveWorkbook.Name 'activates the workbook where the code it stored ThisWorkbook.Activate 'clears contents in the workbook where the code is stored Range("A2:A10").Cells.ClearContents 'toggles to other workbook Workbooks(filename).Activate 'Copies data in cells A2 thru A10 Range("A2:A10").Copy 'paste data into workbook where code resides ThisWorkbook.Activate Range("A2:A10").PasteSpecial (xlPasteAll) 'close workbook without saving changes Workbooks(filename).Close False End Sub
Two Important Notes about Copying and Pasting Between Workbooks
- Always paste data before closing the workbook in which it resides. When a workbook is close the clipboard is cleared. This one gets me every time.
- Always paste values when pasting from a protected sheet. If you paste all from a protected sheet the cells on your new sheet will become protected.