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 more

Excel-VBA : Insert Multiple Images from a Folder to Excel Cells

Say you have many images in a folder and you want to insert all these images in your excel work book, one image in one cell. You can do it manually, insert and resize the image and drag it to the particular cell, but think of a scenario where you have more than 100 images or may be more. I am sure you don’t want to do it manually. IF you are looking for solutions for problems like this, this is the tutorial for you.

This tutorial will teach you about how to insert multiple images from a folder into your excel.

Steps:

  • Open a folder(which contains images) using “Scripting.FileSystemObject”.
  • Get the files in it.
  • Make a loop for all the files
  • Read more

Excel-VBA : Change Passwords for all the WorkSheets in one shot

Objective : Suppose you have many worksheets in an excel and you want to change all the passwords in one shot rather doing it manually for every sheet, which might be very bad way to do specially when you have a power of doing it using VBA.

NOTE: you need a place to store your password, most of the timewe store it in our mind but computer doesn’t have mind, it has memory :), but the point is where, it could be a file, DB . Why not use your excel for that.

Store it in a separate sheet and make it very hidden in your code.

Read more

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 more

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 more

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:
  • Time
    • Manda­tory
    • Type:Variant
    • The time at which macro to be resumed and returns True

    Read more

VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:

  • Create the object of FileSystemObject(Link)
  • Create Folder object using FileSystemObject and GetFolder (link) method and count the number of files.
  • Get the files Object
  • Run the Loop for each files in folder
  • Insert the File objects , using OLE objects.
  • Insert the added files name list in another sheet(just for reference)
  • Save the workbook

Read more

VBA-Excel: Create Dynamic or Run-time WorkSheets using Macro.

To Create Dynamic or Run-time WorkSheets using Macro, follow the steps mentioned below

Steps:

  • Get the Active WorkBook object
  • Add the worksheets at the run time using Add().Name() method

 

Get the Active WorkBook object

        Set mainWorkBook = ActiveWorkbook

Add the worksheets at the run time using Add().Name() method

Read more

VBA-Excel: SUDOKU Solver

Download Link : SUDOKU Solver

Steps:

  • Open a new WorkBook
  • Create a SUDOKU template
  • Create a Button
  • Copy Paste the Complete Code
  • Press the button till the SUDOKU is solved.

Note: This Code works only for easy and medium level SUDOKU problems

 

After Creating the SUDOKU template you page should look like this:

NOTE: Make sure you create the template in correct cells else you will have to change the code

Create a SUDOKU template

SUDOKU SOLVER-1
SUDOKU SOLVER-1

Read more