Excel WorkBook

VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order

May 7, 2015 by Sumit Jain

Download Link: MergeExcel This is the extension of my earlier article “Consolidator“. In this article we will modify it further. Suppose we have a scenario where we have multiple excel files with same columns but they are not in the same order. See the example below. How to Use it: Download the MergerExcel.xlsm from the link … Read more VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order

VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One

January 30, 2015June 30, 2014 by Sumit Jain

Download Link : Consolidator_1.0

If you want to combine many excel files into one file and you don’t want to do it manually, you have come to the right place. This piece of software will allow you merge as many excel files you want, say 500-1000 excel files. This tool will provide you various options of formatting before you merge your files.

How to Use it:

Download the consolidator.xlsm from the link provided at the top and at the bottom of this article.
Place all the excel files, which you want to combine, into one folder (make sure all files are closed).

VBA-Excel: Date-Time Functions – Timer()

January 30, 2015June 30, 2014 by Sumit Jain

Description:

The Timer() function returns the Number of seconds and MiliSeconds since 12:00 AM.

Format:

Timer()

Example:

VBA-Excel: Date-Time Functions – TimeSerial() and TimeValue()

January 30, 2015June 23, 2014 by Sumit Jain

TimeSerial()

Description:

The TimeSerial() function returns the Time type based on the parameters provided (Hours, Minutes and Seconds).

Format:

DateSerial(Hour,Minutes,Seconds)

Arguments:

Hour
- Mandatory
- Type: Numeric or Any Numeric Expression
- Between 0 and 23

VBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()

January 30, 2015June 19, 2014 by Sumit Jain

WeekDay()

Description:

The WeekDay function takes Date as a parameter and returns a number between 1 and 7, that is the week day of the date provided.

Format:

WeekDay (strDate [Firstdayofweek])

Arguments:

strDate
- Mandatory
- Type: Date
- Date, whose Week Date need to be calculated.
Read moreVBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()

VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

January 30, 2015March 19, 2014 by Sumit Jain

This tutorial will teach you how to get all the working days or weekdays in a specified date range(excluding Satudays and Sundays), simple but very useful when you have come up with a excel where you have to work with all the business working days. Example: If the date range is mentioned between 8-Jan-2014 to … Read more VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

VBA-Excel: User Forms

January 30, 2015March 7, 2014 by Sumit Jain

User Forms are one of the very important features in Excel Macro. It provides and an interface to the user for interaction with the workbook. It has many controls which makes user’s life easy for entering the data from excel in a specific manner or reading the particular data from the excel.

Getting Started : Create a Blank User Form

To open or create a new user form, follow the steps below
Open an Excel file
Press “Alt+F11” to open the Microsoft Visual Basic Editor.
Make Project Explorer and Property Window visible from “view menu” (If not already visible)
Select the Project from the Project Explorer.
Form “Insert” menu, select “User Form”
Read moreVBA-Excel: User Forms

VBA-Excel: User Define Functions

November 9, 2014February 14, 2014 by Sumit Jain

Microsoft Excel has lot of built in formulas like Sin(), Avg() and many more, which we discuss in detail in future articles, but apart from that you can write your own functions which will act as any other built-in formula in excel , Which is one of the powerful feature of Excel.

Like other formulas, you can apply your function in each cell or range of cells.

Syntax:

Public Function functionName (Arg As dataType,……….) As dataType

Private Function functionName (Arg As dataType,……….) As dataType

Arg As dataType -> data type of argument

The second data type is the return type of function.

Public : The function is applicable to the whole project.

VBA-Excel: Automate Internet Explorer (IE) using Microsoft Excel – Login into Gmail.

January 30, 2015January 28, 2014 by Sumit Jain

To Automate the Internet Explorer (IE) using Microsoft Excel, say for example you want to login to your gmail account using Microsoft Excel. Please follow the steps mentioned below

Steps:

Create the object of Internet Explorer
Make the Internet Explorer visible
Navigate to the “www.gmail.com”
Wait till the browser is busy and page is fully loaded
Get the document Object
Identify the objects on the Page using “GetElementById”
Set the Authentication details in the Gmail login page
Identify the Sign In Button using “GetElementById” and Click on it

VBA-Excel: Application.Wait OR Wait Method

January 30, 2015January 27, 2014 by Sumit Jain

Description:

The Application.Wait () function pauses the running code or macro for the time specified and once that time is passed, it returns True value.

Format:

Expression.Wait (Time)

Expression : A variable that represents an Application Object.
The Wait method stops all the Microsoft Excel activities and prevent all the major operations.

Arguments:

Time
- Mandatory
- Type:Variant
- The time at which macro to be resumed and returns True
Read moreVBA-Excel: Application.Wait OR Wait Method

VBA-Excel: Maximize, Minimize and Restore Internet Explorer (IE) using Microsoft Excel.

January 30, 2015January 24, 2014 by Sumit Jain

To Maximize, Minimize and Restore Internet Explorer (IE) using Microsoft Excel, follow the steps mentioned below

Steps:Create the object of Internet Explorer

Make the Internet Explorer visible
Navigate to the specified URL
Wait till the browser is busy and page is fully loaded
Create WshShell (Windows Script Host) object
Use send keys function to maximize, minimize and restore operations

VBA-Excel: Launch Internet Explorer (IE) using Microsoft Excel.

January 30, 2015January 20, 2014 by Sumit Jain

To launch the Internet Explorer (IE) using Microsoft Excel, follow the steps mentioned below

Steps:

Create the object of Internet Explorer
Make the Internet Explorer visible
Navigate to the specified URL
Wait till the browser is busy and page is fully loaded

Create the object of Internet Explorer

Set objIEBrowser = CreateObject(“InternetExplorer.Application”)

VBA-Excel: Working with Internet Explorer

January 2, 2015January 16, 2014 by Sumit Jain

You can perform various operation on Internet Explorer (IE) using your excel, like Launch IE browser, navigate to specific URL, Click on Images on IE, setting text in input box or web edit and many more.

Since its and external application, to deal with it first step would be get the instance of the word file or you can say create the Object of the word file using CreateObject Function.

Set objIEBrowser = CreateObject(“InternetExplorer.Application”)

And that’s it Now you are all set to perform various operations.