Dim i As Integer With Application.FileSearch. However, opening all of the workbooks in a specified folder is a bit harder, as you can see in the following subprocedure: Sub OpenAllWB() 'Open all workbooks in specified folder. Listing all the open workbooks is an easy enough task, thanks to the Workbooks collection. Use the Workbooks collection to reference open workbooks. By referencing the Workbooks collection, you can reference all the open workbooks without hard-coding a single workbook name. The resulting user form, shown in Figure C, displays a list of open workbooks. Dim wb As Workbook For Each wb In Workbooks ListBox1.AddItem wb.Name Next wb End Sub For instance, the following subprocedure populates a list box in a user form with the names of all open workbooks: Private Sub UserForm_Activate() 'Populate list box with names of open workbooks. Using the Workbooks property, you can refer to open workbooks. The Workbooks collection contains all the open Workbook objects. 3: Reference workbooks in the Workbooks collection
Take advantage of ThisWorkbook's flexibility when you need to refer to the workbook running code when the active workbook isn't the workbook running code. Figure B Function GetThisWB() As String GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name End FunctionĪs you can see, HumanResources.xls is the active workbook, but the function is in a workbook named 0908002.xls. This added flexibility is great because the active workbook isn't always the workbook that's running code. VBA's ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook evaluates the workbook with the focus, ThisWorkbook refers to the workbook that's running the current code. 2: Reference the workbook that's currently running code
Use the ActiveWorkbook property to return the active workbook's full path and filename.
LEARN MORE: Office 365 Consumer pricing and features Figure A Function GetActiveWB() As String GetActiveWB = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name End Function Figure A Of course, you could just as easily combine all three into a Select Case statement and use a single function to pass a conditional argument that specifies which save to execute. ActiveWorkbook.Close True End Sub Sub CloseActiveWB() 'Close the active workbook. ActiveWorkbook.Close False End Sub Sub CloseActiveWBWithSave() 'Close the active workbook and save. The following procedures use the ActiveWorkbook property to close the active workbook: Sub CloseActiveWBNoSave() 'Close the active workbook without saving.
However, it's invaluable when referencing the active workbook remotely.įor example, after passing data to an active workbook, you'd probably want to save that workbook, which is a simple task for the ActiveWorkbook property. It's perfectly acceptable to use this property to reference the active workbook from code inside the active workbook. The active workbook may or may not contain the code that's referencing the active workbook, which is an important distinction. VBA's ActiveWorkbook property refers to the workbook with the focus. Quick glossary: Metaverse (TechRepublic Premium).