Excel-VBA : Open a Excel File using Another Excel File using Browse Option.

This tutorial will teach you how to Open a Excel File using Another Excel File using Browse Option. We will be using Application.GetOpenFile() function to accomplish this.

NOTE: You can define any type file you want to , just set the proper filter.

FileFilter:=”Excel Files *.xls* (*.xls*) – to open Excel files

FileFilter:=”Excel Files *.txt* (*.txt*) – To open Text File

NOTE :Do not provide any filter if you want to select any type of file as attachment

Complete Code:

Read moreExcel-VBA : Open a Excel File using Another Excel File using Browse Option.

Password Protection to Your Macro Code

In earlier sessions we saw that how we can write code, generate code and customize it but at the same time it’s very essential to protect it. Excel provides an option to make our Code password protected.

In Next few steps we will see how to make our macro secure.

Step 1:

 In VB Editor, right click on the “VBAProject(Book1)” in Project window and select “VBA Project Properties..”

Excel-VBA-MACRO - Password Protection To Macro - 1
Excel-VBA-MACRO – Password Protection To Macro – 1

“Project Properties” window will be displayed.

Read morePassword Protection to Your Macro Code

Add Tools to Quick Access Tool Bar(Excel Top Bar)

You can also add the “Commands” or “Tools” to “Quick Access Tool Bar” (top nav bar) in your excel sheet and it will be placed next to “Save” or “undo” icon in the excel sheet and their fore it will be easy for you to use it, it will be like shortcut.
Below are the following steps you need to follow to add commands to your excel “Quick Access Tool Bar”
Main Menu -> Excel Options

Excel-VBA-MACRO-Add Tool or command to excel - 1
Excel-VBA-MACRO-Add Tool or command to excel – 1

Read moreAdd Tools to Quick Access Tool Bar(Excel Top Bar)

Excel Macro – Visual Basic Editor

The first question will be where do we write the code , Open the Excel and then press “Alt+F11” and you will get the another window named as “Microsoft Visual Basic” and press “Alt+F11” and you will get back to Excel window.

VBA Editor has 3 main windows:

  1. Project Window
  2. Code Window
  3. Properties Window

Project window and Properties window generally appears on the left side of the Editor. You can close them any time you want and to get them back all you need to do is

  • For Project Window : View-> Project Explorer ( Shortcut : Ctrl+R)
  • For Properties Window : View->Properties Window ( Shortcut : F4)

 

Read moreExcel Macro – Visual Basic Editor

VBA Codes – Record Macro

 

There are two most common ways to write VBA codes,  they are-

  • Record the MACRO.
  • Write it in VBA Editor from scratch.

Obviously it’s easy to record the Macro and use it but there are certain disadvantages when you record the Macro like size of the script goes huge which makes it bit difficult to customize it if needed, When we have to deal with the dynamic contents like getting the search results from Google, in such case its recommended to write the VBA.

1. Record a macro:

Recording a macro is one of the powerful feature in Excel, It will record every action done by the user and it will automatically generate the code for the user and user gets the option to create a shortcut for the Macro so the next time when user wants to

Read moreVBA Codes – Record Macro