Sumit Jain

VBA-Excel: Create worksheets with Names in Specific Format/Pattern.

by

  Download Link:Worksheets Many times we find ourselves in a situation where we want to allow users to add the new worksheets and the sheet name must follow some specific format or pattern. Recently I have received the same request from one our reader. This made me realize that it’s a very common problem so … Read more

VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

by

  Download Link: Multi Sheets Objective: This tool is very useful. Many times we encounter a situation where we need to maintain a report on the daily basis (For weekdays). We manually create a worksheet for each day and copy the template from previous sheets and edit it. This tool will automate all the manual … Read more

VBA-Excel: Create or Add Worksheets at the Run time.

by

Objective: This article will teach you “how to add worksheets in excel workbook at the run time”. Important Command: Workbook. Worksheets.Add().Name = “NameYourSheet” Complete Code: Sub Sumit() Dim mainWorkBook As Workbook Set mainWorkBook = ActiveWorkbook mainWorkBook.Worksheets.Add().Name = “Sumit” End Sub Output:   Also Read: Create worksheets with Names in Specific Format/Pattern. Add Worksheets For All … Read more

VBA-Excel: Change Font, Color, Weight of Table Data in the Word document

by

To Change Font, Color, Weight of Table Data in the Word document using Microsoft Excel, you need to follow the steps below (First we will create a table and while filling the data we change the font, color and weight of data):

  • Create the object of Microsoft Word
  • Create a document object and add documents to it
  • Make the MS Word visible
  • Create a Range object.
  • Create Table using Range object and define no of rows and columns.
  • Get the Table object
  • Enable the borders of table using table object.
  • Fill the data in table (Here we will insert some code to make changes in data)
  • Save the document

Read more

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

by

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

Send Mail With Multiple Different Attachments From MS Outlook using Excel.

by

In earlier post you have learned how to send Excel Workbook as attachment with email from MS Outlook using Excel. In this article we will learn about how to Send Mail With Multiple Different Attachments From MS Outlook using Excel. These attachments might be anything, an image, word document, excel document, pdf file, text file etc.

Note: Don’t provide any filter in GetOpenFileName() to select any file as attachment.

strFileToOpen = Application.GetOpenFilename(Title:=”Please choose a file to open”)

I would recommend that you must read Send a Simple Mail From MS Outlook Using Excel and how to send Excel Workbook as attachment with email from MS Outlook using Excel to understand the complete code below.

Read more

Excel-VBA : Open a MS Word Document using Excel File using Explorer Window.

by

This tutorial will teach you how to Open a MS Word Document using Excel File using Explorer Window. 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:=”Word Files *.doc* (*.doc*) – to open Excel files.

Complete Code:

Read more

Excel-VBA : Send Unique Images Embedded to Mail Body, With Every Mail From MS Outlook using Excel.

by

Many a times you need a scenario when you want to send a mail on the daily basis and every new mail contain new image embedded to the mail body, say for example you want to send Good morning message with new image every day.

I would recommend that you must read Send a Simple Mail From MS Outlook Using Excel and how to send Embedded to the mail body from MS Outlook using Excel to understand the basics first if you are new to this.

So how would you do that, there are many different ways to accomplish this, the strategy which I have used here is

  • Store all the Images in a folder.
  • First time you run your macro, the program will connect to the image folder, take the first image and use it and mark that image as used in our excel.
  • Next time when you run the macro, it will see which image is not marked as used, use that. see this picture below.

Used Images

Read more

Excel-VBA : Send Mail with Embedded Image in message body From MS Outlook using Excel.

by

In previous articles you have learned about Send a Simple Mail From MS Outlook Using Excel and how to send Attachment With the Mail MS Outlook Using Excel. ( I recommend you first read these articles to understand the basics if you are new to this)

In this tutorial you will learn how to add an image in the mail body or message and send it from Microsoft Outlook.

Ides is to attach the image in hidden manner and later add it to using image name in the HtmlBody.

Read more

Excel-VBA : Send a Excel Workbook as Attachment in Mail From MS Outlook Using Excel

by

In out earlier post we have seen, How to send a simple text mail using MS Excel. In this tutorial we will learn How to send the entire Excel Workbook as attachment along with the mail from Microsoft Outlook using Excel VBA.

Function to add attachment :

olMail.Attachments.Add “File Name”

NOTE: I have used the browse option to make it more real, you can avoid that by directly providing the complete file name as parameter.

To understand the complete Code, Read – How to send a simple text mail using MS Excel

Read more

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

by

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 : Send a Mail using Predefined Template From MS Outlook Using Excel

by

In our earlier tutorial we have seen how to send a simple text mail using MS Outlook but how about when you want to send a proper composed mail, means your will compose the mail in a proper format in you excel and you want formatting to be retained while sending.

In that case you will not fetch the text from a excel cell for body, instead you will copy and paste the entire cell into your mail body.

Steps:

  • Create object of Outlook Application.
  • Create a Mail Item.
  • Get the Document Object (for pasting)
  • Compose and Send mail using mail Item.

Read more

Excel-VBA : Send a Simple Mail From MS Outlook Using Excel

by

This tutorial will teach you about how to send a simple text mail from Microsoft Outlook using excel-macro.

  • Create object of Outlook Application.
  • Create a Mail Item.
  • Compose and Send mail using mail Item.

 

  • Create object of Outlook Application.
Set otlApp = CreateObject("Outlook.Application")
  • Create a Mail Item.
Set olMail = otlApp.CreateItem(olMailItem)

VBA-Excel: Update XML File

by

In our earlier post we have seen how to Read Data from XML File using in Microsoft Excel. In this tutorial will teach you about how to update an XML file using Microsoft Excel.

I would recommend that you should this post before you continue –Read Data from XML File

Now there are various way you can update XML file –

  • Update Single ParentNode
  • Update attribute of a Node
  • Add a new node under existing node
  • Add new Attribute to the Node

Read more

VBA-Excel: Read Data from XML File

by

To Read Data from XML File using in Microsoft Excel, you need to follow the steps below:

  • Create the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)
  • Load the XML from a specified path.
  • Select the tag from the XML file using SelectNodes or SelectSingleNode.

o   SelectNodes – Selects a list of nodes matches the Xpath pattern.

o   SelectSingleNode – Selects the first XMLNode that matches the pattern.

Read more

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

by

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

VBA-Excel: Read Excel WorkBook as DataBase using ODBC Source

by

You can read an Excel WorkBook as a complete DataBase, means an Excel file can act as Database. You can make range of Rows and Columns as the Tables of your Database which means Excel WorkBook.

This is one of the very crucial feature excel provides because imagine if you have an excel file contains very huge data, say more than 10000X500 cells filled with data and you have to fetch some data from the entire workbook based on some conditions. Yes, you can navigate the entire WorkSheet row wise or column wise but it will take significant amount of time to navigate through each cell. That’s why we have Database and Table concept right.

Read Excel WorkBook as DataBase follows the steps below

  1. Store data in your excel file and save it some location in your system, say “DB Data.xlsx”
  2. Make Excel File as ODBC Source using Microsoft Excel Driver (give the name as SumitODBC)
  3. Open a new excel file in which you will fetch the data from “DB Data.xlsx”
  4. Open the VB Editor
  5. Create a “ADODB.Connection” Object

Read more

VBA-Excel: Make Excel File as ODBC Source(Database) using Microsoft Excel Driver

by

To use your Excel file as Database for your project, First you might need to make your excel file as ODBC source using Microsoft Excel Driver.

In order to make your excel file ODBC Data Source, please follow the steps below

  1. Store data in your excel file and save it some location in your system.
  2. Administrative Tools -> Data Sources (ODBC) -> Choose User DSN -> Choose ‘Excel Files’ from the list -> Press ‘Configure’
  3. Give the Data Source Name and description.
  4. Click on the Select WorkBook
  5. Browse and select the Excel file which you have created at step 1 and click ok
  6. Data Souce will be visible in the List

Read more