- 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.
Comments
No comments yet.