If you need to perform some action on a workbook or need to work with an object within a specific workbook, you need to tell Excel which workbook you want to use. There are 3 ways in VBA of referring to a workbook and they are:
Workbooks – Refers to the collection of all open workbooks. To work with a specific workbook you must nominate either its index number (where 1 represntes the first workbook opend) or enclosed the workbooks filename in quotation marks.
ActiveWorkbook – this refers to the workbook that is active or has the focus of excel.
ThisWorkbook – this refers to the workbook that contains the procedures that are currently being executed
These following Workbook methods are shown using the Workbook(index) reference.
Workbooks.Open “filename” – opens the workbook called filename. Include the full filename including the drive and folder e.g. Workbooks.Open “C:\Marketing\Sales.xlsx”
Workbooks.Add – Creates a new blank workbook and adds t to the collection
Workbooks(“filename”).SaveAs Filename:= “newfilename” – this saves the workbook called filename as newfilename.
Workbooks(“filename”).PrintOut – Prints the workbook called filename.
Workbooks(“filename”).PrintPreview – print previews the workbook called filename.
Workbooks(“filename”).Activate – makes the workbook called filename as the active workbook.
Workbooks(“filename”).Close – will close the workbook called filename.
Kill “filename” –a statement used to delete the workbook called filename.
Workbooks(“filename”).Name – Used to set or return the name of the workbook filename.
Workbooks.Count – this will count the number of open workbooks in the collection.
Workbooks(“filename”).BuiltinDocumentProperties- used to set the return the value of built-in document properties such as Title, Subject, Author, Keywords, Comments and Template.
Workbooks(“filename”).Path – returns the complete path to the workbook e.g. MsgBox “The path to this file is “ & Workbooks(“Sales.xlsx”).Path
Workbooks(“filename”).ReadOnly – determines whether or not the files is read-only.
You could attend a Excel VBA course which will teach this and more in a lot more detail.