VBA references are not very enlightening in relation to "Activate" nor the open-close procedures as well. Can you provide or point to needed documentation? Thanks.
Warren Hall
VBA references are not very enlightening in relation to "Activate" nor the open-close procedures as well. Can you provide or point to needed documentation? Thanks.
Warren Hall
From the perspective of VBA, "Activate" and "Open / Close" are two very different subjects. From my comments below you will see that your question is insufficiently precise to permit the type of answer you probably seek.
The "Open" and "Close" methods in VBA are amply documented. You can google for them (like "Workbook open". Include "MSDN" in the search criterum to get Microsoft's own documentation.). The underlying question, however, is in the location of your code. Obvisouly, if the ode is in workbook A that workbook must be open for the code to run. Therefore the workbook to open must be one that doesn't contain the code that opens it. Similar logic applies to the Close method because closing the workbook that contains the code would terminate code execution.
The "Save" method is equally well documented. The problem you are likely to face, however, is to specify which workbook to save. While only one workbook is open (the one containing the code) VBA will treat it as the default, requiring no specification and permitting it to be addressed as "ThisWorkbook" or "ActiveWorkbook". Better code would name each workbook as it is opened, like
Set SecondWorkbook = Workbooks.Open([Path and Name])
SecondWorkbook.Close SaveChanges = True
VBA doesn't require the "Activate" method it provides because once a workbook is "open" its entire content is loaded into memory and can be accessed with commands like
Debug.Print SecondWorkbook.Worksheets("Sheet1").Cells(1, 1).Value
It doesn't matter which workbook is the active one while the above code is executed. The Activate method is therefore needed only for the purpose of switching windows for the user to watch. Once a workbook is activated it can be referred to as "ActiveWorkbook". However, activating a workbook for this purpose is decidedly bad coding.
Note that Workbooks.Add will activate the new workbook. Best practise is to assign a name to the workbook immediately, possibly with code like Set SecondWorkbook = ActiveWorkbook. This twist would normally be combined with code that prevents the new workbook to actually be displayed on the screen, meaning it would briefly, and unnoticed by the user, be active but kept in the background by the command ThisWorkbook.Activate.
I think that this series of tutorials should be quite helpful to you:
Activate or Navigate to a Worksheet using Macros VBA in Excel
Macro to get Data from Another Workbook in Excel