Mailing Labels using EXCEL

Mailing Labels using EXCEL


Here is a simple tool for generating mailing labels using EXCEL.

There will be two sheets namely

1. List sheet


(larger images are given below)







2. Labels Sheet


(larger images are given below)







In the List sheet we have to enter the address information.

In the Label sheet, we have to prepare the labels using INDEX function. The following formula is used to generate labels

To bring the name in the first row =INDEX(Address,D1,2)
To bring the addr1 in the second row =INDEX(Address,D1,3)
To bring the addr2 in the third row =INDEX(Address,D1,4)
To bring the city in the fourth row =INDEX(Address,D1,5)
To bring the pincode in the fifth row ="PIN:"&INDEX(Address,D1,6)

for second column, we can add 1 to the D1 cell
for third column, we can add 2 to the D1 cell

for the second row of labels, we can use D7 cell as row reference

We can enter the formula once for first row of the labels.
For subsequent rows, we can copy and paste the formula.

I hope this simple solution will be useful for you all.

Cheers! (mail me your comments)

karthiganesh@yahoo.com

Larger images of the above sheets are given below:

List Sheet



Labels Sheet

EXCEL-VBA Application Development Strategy

As an EXCEL-VBA based BI Consultant, I faced many situation where the requirements are legacy system. There is no ready made, built-in solution available. Hence the Business Analyst and System Analyst have to find out a solution on their own. As a third part consultant, I too was asked any suitable strategy. With the experiences I gained during those many brain storming sessions, I learned many feasible, practical solution strategy. I would like to share them with you!

1. Simple Switch Board

We can have the first sheet as Main Menu. (Recently User Interface specialists call this as SWITCHBOARD). The main menu can be

• set of carefully arranged command buttons
• Custom menu (menu added to EXCEL menu)
• Custom tool bar (new toolbar added to existing toolbars)

2. Data Entry

Data Entry can be achieved with the help of UserForm or specially designed excel worksheet.

3. Data Source

The data source can be anything including

• text file
• another worksheet
• user data entry
• ORACLE, SQL Server, mySql (ODBC)

4. Output

The output of your EXCEL-VBA application can be

• another workbook
• chart
• email
• word document
• text file

I will continue other aspects of EXCEL-VBA Application Development Strategy in next blog.

We will be benefited from sharing of our knowledge. So please feel free to share your valuable knowledge with me by your comments/email etc.

Cheers!

Contact me @ karthiganesh@yahoo.com

Mail Merge utility!!!

We all know that MS-Word has wonderful feature called MailMerge for merging a Letter with many address and print individual letters. The same functionality can be implemented in EXCEL. The new EXCEL Mail Merge workbook can found new uses!!!

• A Bill / Receipt
• A Data Fillup Form
• An Account Voucher
• etc

In the simplest form, the workbook will have 3 sheets

1. LETTER: Where you will type the Letter
2. LIST: The list of addresses (or data to be printed)

Now the question is how can we view the Printing Page. I may suggest two ways:

1. View Individuals Letters/Forms
2. View Group of Letters/Forms

I amusing the INDEX function to fetch the respective data from LIST sheet into PRINTPAGE sheet by referring the sequence number in the list. You can use VLOOKUP function to fetch data based on NAME or ID field.

A bigger images of those two sheets are follow :

A simple solution for Investment Portfolio Management



Here is a simple solution developed by me for Investment Portfolio Management which requested by my cousin. He is having a novel technique of tracing his investments. Since I did not get his permission, I did not explained his technique. But I implemented a generic method using Weighted Average. This solution helps to add market rate whenever you have data with you. If it is feasible, this can be modified to fetch data from website and add to your database. Most of the user may prefer to enter their own database.

The applications has four sheets

• Status
• MyInvestments
• RateData
• FetchWebData

In the MyInvestments Sheet you have enter the asset details, and periodical market rate in the RateData sheet

When you enter the date in the Status sheet, it will show the value of the investment.

Once again I reiterate that I will send this excel file free of charges, if you send me an email.
My email id is karthiganesh@yahoo.com

Cheers!

Frequently used Functions in my solutions!

Here is the secret ingredients of my EXCEL-VBA recipes.

I will use the following functions extensively :

1. INDEX
2. MATCH
3. VLOOKUP
4. HLOOKUP
5. SUMIF
6. SUMPRODUCT

Apart from this I have my own collection of UDFs. Here I have listed their functionality only. Please dont try to criticise these methods. I am very clear that these are my own collections. They may be "better than the best" or "not bad" or "more worst than the most worst". In any case point to me as it will help me to improve my skills. I am still learning EXCEL-VBA.

1. Procedure to update a single cell named range
2. Function to retrieve the value in single cell named range
3. A generic Function to find the last column in a table or column number of a particular value
4. A generic Function to find the last row in a table or row number of a particular value
5. A generic function to compose SQL statements from a range of cells
6. A Procedure to Compare two excel worksheets
7. A function to compose EXCEL CELL formula from a user design
and many more...

I may update this post very frequently.

If you want know more about a particular Function or UDF, feel free to write to me karthiganesh@yahoo.com

Simple EXCEL-VBA Report Generation Guidelines

What are the basic components of an Excel Report Generator?
I like modularisation and customisation while coding. EXCEL-VBA offers entirely different kind of customisation compared to other programming language. In the Project Explorer you can see

Microsoft Excel Objects, Forms, Modules categories.

• You can place your procdure/function in any of the objects. I place modules like this:
  Event Handlers are placed inside ThisWorkBook (usually Cell Double Click, Right Click, WorkBookOpen events and handles by these event handlers)
• Procedures and Functions specific to a particular Sheet are placed inside the Sheets
• Utility procedures/functions used by all objects are placed here.

A typical procedure skeleton is like this

Public Sub GenerateReport
...{declaration of variables}
...{query building}
...{fetching data and populating into sheet}
...{formatting data as per their content (number, date etc)}
...{performing sort, subtotal, filter etc}
End Sub

Then call this procedure from the click event of a command button placed in the sheet.

Prominent advantage of EXCEL-VBA lies in customisation of Queries. You can keep your queries in a worksheet cell. Criteria can be easily composed into a cell using cell formulae. For example consider the following example

SELECT EMPNO, NAME, DEPTNO FROM EMP
WHERE DEPTNO=20

You can type this SQL statement in two cells and design the excel sheet as shown below

In the above sheet, the cell contents are like this
A5 is a single point cell for complete SQL SELECT statment. It is concatenation of below cells
=A6&A7

A6 is just SELECT statement entered as text
SELECT EMPNO, ENAME, DEPTNO FROM EMP

A7 is a formula to compose the criteria for DEPTNO it is entered as a formula
=" WHERE DEPTNO="&C3

Note that the user will enter the required DEPTNO in cell C3.
Now you may think that this will reveal the SQL SELECT statement to the users. If you are concerned about the security of source code, you can type them in a separate sheet and protect it using appropriate security measures. It is available in EXCEL. Those security guidelines will be posted in subsequent posts.

Keep on visiting my blog for better understanding EXCEL-VBA from an expert point of view. I assure you my prompt reply and support email me karthiganesh@yahoo.com

EXCEL-VBA for Reporting

MS-EXCEL as Reporting Tool for ODBC Database

Almost all RDBMS Databases are coming with sophisticated Front End tools for developing Data Entry forms, Reports and Charts. Then what is the need for using MS-EXCEL for the same purpose. I think these may be the reasons...

• MS-EXCEL is most easy to use spreadsheet application. Business users know MS-EXCEL very well.
• The existing knowledge of MS-EXCEL can be leveraged for better presentation. I mean the user can customize the reports. The developer can concentrate on implementing complex business logic rather than implementing User Interface. Most of the user will use that data effectively if they are able to customize the presentation (Like self-cooked meals).
• Business experts with MS-EXCEL knowledge are able to process the data on their own. There is no need for explaining the business logic to the developer/analyst. Or atleast it will help them to explain the logic better. (Earlier in my career, when our Factory Manager explains me his requirement, I used to implement the same in QuatroPro and get his approval. This helped me to develop robust FOXPRO applications)
• It is cost effective solution to use MS-EXCEL as a Front End Tool
• It can be used for WHAT-IF analysis. The same data can be represented for different scenarios by changing some parameters. MS-EXCEL ability for number-crunching can be better utilised for this purpose. (Practically this has numerous application. One of my spreadsheet application for Producation Planning is designed as WHAT-IF analysis tool)
• There are many... But I want to cut is short as you may feel it is boring.

One of my earlier post in this blog demonstrated how to fetch and populate EMP table into MS-EXCEL. This post can be considered as continuation of that post.