Excel WorkBook | Excel-Macro

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: 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 more

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: 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 more

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 more

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.

VBA-Excel: Date-Time Functions – Hour(), Minute(), Second()

January 30, 2015January 13, 2014 by Sumit Jain

Hour()

Description:

The Hour function takes time as a parameter and returns a number between 0 and 23, that is the Hour of the time provided.

Format:

Hour(strTime)

Arguments:

strTime
- Mandatory
- Type: Time
- Time, whose hours need to be calculated.
Read more

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

January 30, 2015January 8, 2014 by Sumit Jain

Description:

The FormatDateTime function takes date as a parameter and returns the specified formatted date or time.

Format:

FormatDateTime(strDate[,strFormat])

 Arguments:

strDate
- Mandatory
- Type: Date
- Date which needs to be formatted.

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

January 30, 2015January 5, 2014 by Sumit Jain

Description:

The Day function takes date as a parameter and returns a number between 1 and 31, that is the day of the month.

Format:

Day(strDate)

Arguments:

strDate
- Mandatory
- Type: Date
- Date, whose day needs to be calculated.

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

January 30, 2015December 31, 2013 by Sumit Jain

Description:

The DateValue() function converts string to a date and return it.

Format:

DateValue(strString)

Arguments:

strString
- Mandatory
- Type: String
- String that needs to convert into date, if year is not provided then it will take the current year by default.

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

January 30, 2015December 29, 2013 by Sumit Jain

Description:

The DateSerial() function returns the Date type based on the parameters provided (year, month and date).

Format:

DateSerial(year,month,day)

Arguments:

Year
- Mandatory
- Type: Numeric or Any Numeric Expression
- Between 100 and 9999, values between 0-99 will be considered as 1900-1999

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

January 30, 2015December 24, 2013 by Sumit Jain

Description:

The DatePart() function returns the part of given date based on the parameters provided.

Format:

DatePart(interval,date[,firstdayofweek[,firstweekofyear]])

Arguments:

interval
- Mandatory
- Type: String expression
- The time interval you want as a part of given date

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

January 30, 2015December 21, 2013 by Sumit Jain

Description:

The DateDiff() function returns the specified time interval difference between the two dates provided.

Format:

DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])

Arguments:

interval
- Mandatory
- Type: String expression
- The time interval you want the difference between two dates.