You will often see a code where a reference to a worksheet or a cell/range is made without referring to the workbook. The above code first activates Sheet1 in the Examples.xlsx workbook and then selects cell A1 in the sheet. Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate If you want to activate a workbook and select a specific cell in a worksheet in that workbook, you need to give the entire address of the cell (including the Workbook and the Worksheet name). If you’re not sure what name to use, take help from the Project Explorer. If it hasn’t been saved, then you can use the name without the file extension. Note that you need to use the file name along with the extension if the file has been saved. If you have two workbooks open, and you want to activate the workbook with the name – Examples.xlsx, you can use the below code: Sub ActivateWorkbook() If you have the exact name of the workbook that you want to refer to, you can use the name in the code. In this section, I will cover the different ways to refer to a workbook along with some example codes. The method you choose would depend on what you want to get done. There are different ways to refer to a Workbook object in VBA. Open the Specified Workbook by Double-clicking on the Cell.Error while Working with the Workbook Object (Run-time error ‘9’).Create a New Workbook for Each Worksheet.Save a Copy of the Workbook (with Timestamp).Access VBA change Query criteria using QueryDef.Access replace Crosstab Query with Expression.Solution to Access Error 3047 Record is too large.Microsoft Access produce Cartesian product with Cross Join.MS Project delete Summary Task without deleting subtasks.Access VBA import txt using DoCmd.TransferText Method.Access VBA delete Table using DoCmd.DeleteObject Method.Access VBA loop through all Tables using DAO.TableDef.Access VBA run Query or run Action Query.Access VBA import workbook to Access using Transferspreadsheet.Access StrComp Function to Compare text (case sensitive comparison).Access Case Sensitive Join Table (Inner Join, Left Join).Access VBA delete Table records with SQL using DoCMD.RunSQL Method.MS Access select the first record of each group using First Function.Report this ad Categories Categories Archives Archives report this ad Recent Posts Set masterWB = Workbooks.Open("C:\Users\WYMAN\Desktop\folder\FileB.xlsx") Public Function wIfWbOpen(wbName As String) As Booleanįinally use the Function in the Sub in which you want to run code in FileB. This Function loop through all currently opened Workbook to see if there is a Workbook name same as the name provided in the Function argument. There are some methods to test if a Workbook is already opened, below is my preferred method. You may already have FileB opened before the Macro is run, in that case you will receive an alert message if you use Workbooks.Open, this will prevent your Macro from further running. ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "FileA" Check if Workbook is opened already Now you can manipulate FileA (using thisworkbook) and FileB(using masterWB) from FileA masterWB.Sheets("Sheet1").Range("A1").Value = "FileB" If the FileB is already opened, you will receive an alert. The below code give FileB a name called masterWB. Therefore you need to give FileB a name in order to manipulate it. Workbooks.Open ("C:\Users\WYMAN\Desktop\folder\FileB.xlsx")Īfter you open FileB, you may need to change the data. In FileA, create a Sub in Module and insert the below code, which will open FileB.
Now you want to open FileB from FileA, so FileA should contain the Macro (therefore. One is called FileA.xlsm, another is called FileB.xlsx Example of Excel VBA Workbooks Open MethodĪssume that you have two workbooks. Note that Workbooks Open Method is different from Workbook Open Event, the latter is to trigger VBA when a workbook is opened. Workbooks Open Method is very straight forward to use, you just need to specify the file name to open, but there are many other optional parameters you may want to use. In worksheet automation, we may need to programmatically open another workbook to change data based on the active workbook.
This Excel tutorial explains how to use Workbooks Open Method to open a closed workbook and check if workbook is opened.