Open All Workbooks in a Folder

Sometimes you may need to go into your folder, open each workbook, edit, save it, close the workbook, and then open the next one. Opening each workbook in a folder or directory is typically a time consuming manual process. This little macro takes care of that annoyance. Macro Code (VBA…

Save and Close All Workbooks at Once

Sometimes you may need to save and close many workbooks at once. For each workbook you have opened, you need to activate the work, close it, and confirm save changes. There is no easy way to close them all down at one time. This little macro takes care of that…

Refresh All Data Connections on Workbook Open

Your workbook may have connections to external data sources such as web queries, MSQuery connections, PivotTable connections, and so on. In these cases, it may be helpful to refresh all data connections automatically when the workbook is opened. The following macro code allows you to refresh all data connections on…

Determine If a File Exists in a Directory

Sometimes you may need to determine if a file exists in a directory. For instance, the purpose of your macro may be to open and read from a text file. Before doing so, you will want to check to see if the file exists, in order to avoid an error….

Determine If a Excel Workbook is Already Open

The last macro automatically opened a specific workbook chosen by the user’s selection. As we consider automatically opening workbooks, you must think about what may happen if you attempt to open a workbook that is already open. In the non-VBA world, Excel attempts to open the workbook again, with a…

Opening a Specific Workbook Chosen by the user

Sometimes you need to allow your users a rapid way to open a Excel Workbook? This macro opens a friendly dialog box, allowing you to browse for and open the Excel Workbook of your choosing. Macro Code Sub OpenSpecificFile() ‘Step 1: Define a string variable. Dim SpecificFileName As Variant ‘Step…

How to Assign a Macro to a Button Form Controls or a Sharp

When you create macros, you may want to have a clear and easy way to run each macro. In this post We’ll show you how to assign a macro to a button form controls or a sharp. we’ll use this simple macro for our example. Sub HelloWorld() MsgBox “Hello, World!”, vbExclamation…

Open a Workbook to a Specific Sheet

Sometimes you need to open your workbook be started on a specific worksheet. With this macro, if a user is working with your workbook, they can’t go astray because the workbook starts on the exact worksheet it needs to. Macro Code Private Sub Workbook_Open() ‘Step 1: Select the specified sheet…