Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Activate and Close with save syntax

0

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

Answer
Discuss

Answers

0
Selected Answer

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.

Discuss

Discussion

Thank you very much. You response was clear, right on target with the question, and has really clarified some things that once were vague.
wornhall (rep: 4) Apr 30, '19 at 9:15 am
Add to Discussion
0

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

Open Excel Workbook Using VBA Macros

Close Excel Workbook using VBA Macros

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login