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

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: Down­load 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

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:

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

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

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

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:

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

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

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

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

VBA-Excel: User Define Functions

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

or

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.

Read moreVBA-Excel: User Define Functions

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

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

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

VBA-Excel: Application.Wait OR Wait Method

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:

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

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

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

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

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”)

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

VBA-Excel: Working with Internet Explorer

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.

Read moreVBA-Excel: Working with Internet Explorer

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

Description:

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

Format:

DateSerial(year,month,day)

Arguments:
    • Year
      • Manda­tory
      • Type: Numeric or Any Numeric Expression
      • Between 100 and 9999, values between 0-99 will be considered as 1900-1999

    Read moreVBA-Excel: Date-Time Functions – DateSerial()

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

    Description:

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

    Format:

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

    Arguments:
    • interval
      • Manda­tory
      • Type: String expression
      • The time interval you want as a part of given date

    Read moreVBA-Excel: Date-Time Functions – DatePart()

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

    Description:

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

    Format:

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

    Arguments:
    • interval
      • Manda­tory
      • Type: String expression
      • The time interval you want the difference between two dates.

    Read moreVBA-Excel: Date-Time Functions – DateDiff()