Rizwan Parkar's: February 2009

Wednesday, February 25, 2009

Open and Edit Cells in an Excel 2007 file in VB.NET

The following sections you can find how to open and edit an Excel 2007 worksheet through VB.NET 2005. For open or edit an Excel 2007 worksheet in VB.NET 2005 , you have to add the Microsoft Excel 12.0 Object Library in you project.

From the following pictures to show how to add Excel reference library in your project.

1. Create a new project and add a button to the Form.

Select reference dialouge from Project menu

Select Microsoft Excel 12.0 Object Library and click OK button

Now you can start coding to open or read from Excel file and edit cells.

VB.NET Source Code

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
    xlWorkSheet = xlWorkBook.Worksheets("sheet1")
    'display the cells value B2
    MsgBox(xlWorkSheet.Cells(2, 2).value)
    'edit the cell with new value
    xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

End Class

In the code , Imports Excel = Microsoft.Office.Interop.Excel - we assign the excel reference to a vatriable Excel.

When you execute this program , the program open the file c:\test1.xlsx and edit the content in the cell B2, it replace the old content to "http://vb.net-informations.com" . Before running this program you have to create an excel file name test1.xlsx and add some data in the cell B2.

OOpen and Edit Cells in an Excel 2007 file in VB.NET

From the following pictures to show how to add Excel reference library in your project.

1. Create a new project and add a button to the Form.

Select reference dialouge from Project menu

Select Microsoft Excel 12.0 Object Library and click OK button

Now you can start coding to open or read from Excel file and edit cells.

VB.NET Source Code

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

     Dim xlApp As Excel.Application
     Dim xlWorkBook As Excel.Workbook
     Dim xlWorkSheet As Excel.Worksheet

     xlApp = New Excel.ApplicationClass
     xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
     xlWorkSheet = xlWorkBook.Worksheets("sheet1")
     'display the cells value B2
     MsgBox(xlWorkSheet.Cells(2, 2).value)
     'edit the cell with new value
     xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
     xlWorkBook.Close()
     xlApp.Quit()

     releaseObject(xlApp)
     releaseObject(xlWorkBook)
     releaseObject(xlWorkSheet)

 End Sub

 Private Sub releaseObject(ByVal obj As Object)
     Try
         System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
         obj = Nothing
     Catch ex As Exception
         obj = Nothing
     Finally
         GC.Collect()
     End Try
 End Sub

End Class

Read Data from Excel using OLEDB in VB.NET 2005

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel 2007 file using OLEDB in VB.NET 2005 . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from Excel file using ado.net , We can use the SELECT command like in SQL Operations.

sample Select sql

sql = "select * from [Sheet1$]"

Here is the sample Excel file .

Open the connection using OLEDB Provider

(provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;)

Specify which data you want to read

select * from [Sheet1$]

Here is the screen short after reading from Excel file .

VB.NET Source Code

Imports System.Data
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles Button1.Click
Try
 Dim MyConnection As System.Data.OleDb.OleDbConnection
 Dim DtSet As System.Data.DataSet
 Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
 MyConnection = New System.Data.OleDb.OleDbConnection _
 ("provider=Microsoft.Jet.OLEDB.4.0;"  _
 " Data Source='c:\testfile.xls'; " _
  "Extended Properties=Excel 8.0;")
 MyCommand = New System.Data.OleDb.OleDbDataAdapter _
     ("select * from [Sheet1$]", MyConnection)
 MyCommand.TableMappings.Add("Table", "TestTable")
 DtSet = New System.Data.DataSet
 MyCommand.Fill(DtSet)
 DataGridView1.DataSource = DtSet.Tables(0)
 MyConnection.Close()
Catch ex As Exception
 MsgBox(ex.ToString)
End Try
End Sub
End Class

When you execute this program you will get the contents in the excel file to the DataGrid.

SMS collection..!!!

updated :(feb-25,2009)

LOVE :
-----------------------------------------------------------
Apne gamo ki yu numaish na kar,
Apne nasib ki yu aazmaish na kar,
Jo tera hai tere dar pe khud ayega,
Roz roz use pane ki khwaish na kar
---------------------------------------------
Nabz Meri Dekhi Aur Bimar Likh Dia
Rog Mera Dekha To Pyar Likh Dia
Karzdar Rahnge Hum Us Hakim K
Jisne Dava Me Aapka Didar Likh Dia.
---------------------------------------------

FRIENDSHIP :
-----------------------------------------------------------
Na Ki Dosti Tumse Kisi K Kehne Se,
Na Joda Rishta HmneTumSe Ashqo K Behne Se,
Hmne DekhaTumhare DiL Ko,
Aur Dosti Ki Apne DiL K Kehne Se
----------------------------------------
Who is Dost?
Dost vo jo bin Bulaye aye
Bevaja Sar Khaye
Jaib Khali Kerwaye
Kabhi sataye
Kabhi Rulaye
Mager Hamesha Sath nibhaye
----------------------------------------
College me School ki yaad ayi
Bade hokar Bachpan ki yaad ayi
Kaanto ko chuha to Phool ki yaad ayi
Tanha mehsus kiya to Dost teri Yaad ayi
-------------------------------------------------

JOKES (HUMOR) :
-----------------------------------------------------------
Meri Har yaad me TUM Ho
Meri Har Soch me TUM Ho
Meri Har Dua me TUM Ho
Meri Har Aas me TUM Ho
Or
Ek TuM Ho,Pata Nahi Kahan GuM Ho!
----------------------------------------------------
Chand Ko Akele Me Sharam Aati Hogi
Baat Kuchh Aapko B Samajh Me Aati Hogi
Jab B Zikar Hota He SMART Logo Ka
Aapko Meri Yaad Bahut Aati Hogi
----------------------------------------------------
Puri botal na sahi,1 jam ho jaye
Milan na sahi,Dua-salam
hi ho jaaye
Jinki yad me hum
BIMAAR pade he,Kam se kam unhe
ZUKHAM To ho jaye
----------------------------------------------------
Zindgi badi udas hai,Muje lagi pyas hai,
Mujhe dunya se na koi aas hai,Cow khati Ghaas hai.
Main uska sir fod dunga
jsne kaha meri poem bakwas hai..
----------------------------------------------------
Sardar lost his cheque book
bank Manager:Be careful any1 can put ur sign

Sardar: I'm not fool ,I have already signed all cheque.
--------------------------------------------------------------
Ek dost dusre se: yar aaj meri GIRL FRND ka birthday hai,Use kya du.
2sra: dekhne me kesi hai?
1dost: achi hai 2sra: To fir mera number dede
--------------------------------------------------------------
Sitam dane ki bhi had hoti hai, kisi ko satane ki bhi had hoti hai,
SMS karne ki koshish karo dost , muft me SMS pane ki bhi had hoti hai
------------------------------------------------------------------------------
HEART BREAK (SAD) :
-----------------------------------------------------------
Chot Pe Chot Khaye Ja Rhe Hai,
Rishte Phir B Nibhaye Ja Rhe Hai,
Zindagi Ka Ye Imtihaan Hi Sahi,
Honto Par Muskaan Laye Ja Rhe Hai
----------------------------------------------------
Lambi He Manzil Dur He Kinara..
Jane Q Nhi Aaya sMs Tumhara?
Bhul Gaye Ho Number Ya Nam Hmara?
Ya Koi Mil Gaya He Humse B Pyara?
----------------------------------------------------
Har Ghadi intzar me Gujari,
Baaton k Baad Apki yaad me Gujri,
HumTo Ek phooL The, rakh k BhuL Gaye,
Phir Tamam umar Apki Kitaab Me Gujri!
----------------------------------------------------
Humne Mita Dia Jaha B Likha Naam Tera,
Apne Dil Se Mitana Bhool Gye,
Tum Juda Hue Humse Muskrate Hue,
Par Palkon Se Ansoo Hatana Bhool Gye
----------------------------------------------------
Kaanch ko chahat thi patthar pane ki
Ek pal me phir tut kar bikhar jane ki
Chahat thi us diwane ki
Apne Har tukde me Tasveer unki pane ki
----------------------------------------------------

Tuesday, February 24, 2009

live Streaming

Monday, February 23, 2009

A NICE MEANINGFUL MSG..!!!

No Pain No Gain… Accept the Pain, Future will be Fruitful…

Don't feel the work you are doing is pain, because there will be always a reason for that pain or work.

So face the pain, for the pain you face, there will be definitely happiness a head.

Questions and Answers about holy Quran!!!

Dear All,

Please find below some important information from the "Quran".

S.No.	QUESTION	ANSWER
1.	How many Sura are in Holy Quran ?	114
2.	How many Verses are in Holy Quran ?	6666.
3.	How many dots are in Holy Quran ?	1015030.
4.	How many over bar (zaber) are in Holy Quran ?	93243
5.	How many under bar ( Zaer ) are in Holy Quran ?	39586
6.	How many Raque are in Holy Quran ?	1000.
7.	How many stop ( Waqf ) are in Holy Quran ?	5098.
8.	How many Thashdeed are in Holy Quran ?	19253.
9.	How many letters are in Holy Quran ?	323671
10	How many pash are in Holy Quran ?	4808.
11.	How many Madd are in Holy Quran ?	1771
12	How many words are in Holy Quran ?	77701.
13	How many parts of Holy Quran ?	30.
14	How many time Besmillah Al-Rahmaan Al-Raheem is repeated ?	114.
15	How many Sura start with Besmillah Al-Rahmaan Al-Raheem ?	113.
16	How many time the word 'Quran' is repeated in Holy Quran ?	70.
17	Which is the longest Sura of Holy Quran ?	Al-Baqarah.
18	Which is the best drink mentioned in Holy Quran ?	Milk.
19	The best eatable thing mentioned in Holy Quran is ?	Honey.
20	Which is the shortest Sura of Holy Quran ?	Qausar.
21	The longest verse of Holy Quran is in which Sura?	Al-Baqarah No.282
22	The most disliked thing by the God though Halal is ?	Divorce
23	Which letter is used for the most time in Holy Quran.?	Alaph
24	Which letter is used for the lest time in Holy Quran ?	Zaa.
25	Which is the best night mentioned in Holy Quran ?	Night of Qadar.
26	Which is the best month mentioned in Holy Quran ?	Ramzan.
27	Which is the biggest animal mentioned in Holy Quran ?	Elephant.
28	Which is the smallest animal mentioned in Holy Quran ?	Mosquito
29	How many words are in the longest Sura of Holy Quran ?	25500.
30	How many words are in the smallest Sura of Holy Quran ?	42
31	Which Sura of Holy Quran is called the mother of Quran ?	Sura Hamd
32	How many Sura start with Al-Hamdullelah ?	Five_ Hamd, Inaam, Kahf, Saba & Fatr.
33	Which Sura has the same number of verses as the number of Sura of Holy Quran ?	Taqveer, 114 verses.
34	How many Sura's name is only one letter ?	Three, Qaf, Sad & Noon.
35	How many Sura start with word " Inna " ?	Four sura - Fatha, Nuh,Qadr, Qausar.
36	Which Sura has the number of its verses equal to the number of Masumeen ?	Saf, 14 verses.
37	Which sura are called Musabbahat ?	Esra, Hadeed, Hsar, Juma, Taghabun & Aala.
38	How many sura are Makkahi and how many are Madni ?	Macci 86, Madni 28.
39	Which sura is on the name of tribe of Holy Prophet ?	Quresh
40	Which sura is called the heart of Holy Quran ?	Yaseen.
41	In which sura the name of Allah is repeated five time ?	Sura al-Haj.
42	Which sura are named Azaiam ?	Sajdah, Fusselat, Najum & Alaq.
43	Which sura is on the name of one Holy war ?	Sura Ahzaab.
44	Which sura is on the name of one metal ?	Sura Hadeed
45	Which sura does not starts with Bismellah ?	Sura Tauba.
46	Which sura is called ' Aroos-ul-Quran ?	Sura Rehman.
47	Which sura is considered as 1/3 of holy Quran ?	Sura tauheed.
48	The name of how many sura are with out dot ?	Hamd, Raad, Toor, Room, Masad.
49	In which sura Besmillah came twice ?	Sura Naml.
50	How many sura start with the Initials ( Mukette'at )	29 Sura.
51	Which Sura was revealed twice ?	Sura Hamd.
52	In which Sura the back biter are condemned ?	Sura Humzah.
53	In which Sura the name of Allah is repeated in every verse ?	Sura Mujadala.
54	In which Sura the letter 'Fa' did not come ?	Hamd.
55	Which Sura are called Muzetain ?	Falk & Nas.
56	Which are those Sura if their name are reversed remain the same ?	Lael & Tabbat.
57	Which is that Sura if its first letter is remove becomes the name of one of the city of Saudi Arab?	Sajdah
58	Which Sura start with word ' Tabara Kallazi' '	Mulk & Furkan
59	Macci Sura were revealed in how many years ?	13 years
60	Madani Sura were revealed in how many years ?	10 years.
61	Which sura start with word Kad ?	Mujadala & Momenoon.
62	Which Sura is related to Hazrat Ali ?	Sura Adiat.
63	How many Sura are in 30th. Chapter ?	37.
64	Which sura every verse ends with letter 'Dal ' ?	Tauheed.
65	Which Sura is revealed in respect of Ahllelbayet ?	Sura Dahr.
66	Which sura every verse ends with letter ' Ra '	Qauser.
67	In which sura the creation of human being is mentioned ?	Sura Hijr V-26.
68	In which sura the regulations for prisoner of war is mentioned ?	Sura Nesa
69	Which sura is having the laws about marriage ?	Sura Nesa.
70	Which sura if its name is reversed becomes the name of one bird ?	Sura Room.
71	In which sura the story of the worship of cow of Bani Esra'iel is mentioned ?	Sura Taha.
72	In which sura the law of inheritance is mentioned?	Sura Nesa.
73	In which sura the Hegira of Holy Prophet is mentioned ?	Sura Infall.
74	In which Sura the 27 Attributes of God are mentioned ?	Sura Hadeed.

5 - MINUTE MANAGEMENT LESSONS

Must read all mensioned lessons will help every one some how some where....
Lesson 1:

A man is getting into the shower just as his wife is finishing up her shower, when the doorbell rings.
The wife quickly wraps herself in a towel and runs downstairs. When she opens the door, there stands Bob, the next-door neighbor.
Before she says a word, Bob says, "I'll give you $800 to drop that towel, "
After thinking for a moment, the woman drops her towel and stands naked in front of Bob After a few seconds, Bob hands her $800 and leaves.
The woman wraps back up in the towel and goes back upstairs.
When she gets to the bathroom, her husband asks, "Who was that?"
"It was Bob the next door neighbor," she replies.
"Great," the husband says, "did he say anything about the $800 he owes me?"

Moral of the story
If you share critical information pertaining to credit and risk with your shareholders in time,you may be in a position to prevent avoidable exposure.
*********

Lesson 2:

A priest offered a Nun a lift. She got in and crossed her legs, forcing her gown to reveal a leg. The priest nearly had an accident. After controlling the car, he stealthily slid his hand up her leg.
The nun said, "Father, remember Psalm 129?"
The priest removed his hand. But, changing gears, he let his hand slide up her leg again.
The nun once again said, "Father, remember Psalm 129?"
The priest apologized "Sorry sister but the flesh is weak."
Arriving at the convent, the nun sighed heavily and went on her way.
On his arrival at the church, the priest rushed to look up Psalm 129 It said, "Go forth and seek, further up, you will find glory."<

Moral of the story
If you are not well informed in your job, you might miss a great opportunity.
*********

Lesson 3:

A sales rep, an administration clerk, and the manager are walking to lunch when they find an antique oil lamp. They rub it and a Genie comes out.
The Genie says, "I'll give each of you just one wish."
"Me first! Me first!" says the admin clerk. "I want to be in the Bahamas, driving a speedboat, without a care in the world."
Puff! She's gone.
"Me next! Me next!" says the sales rep. "I want to be in Hawaii, relaxing on the beach with my personal masseuse, an endless supply of Pina Coladas and the love of my life."
Puff! He's gone.
"OK, you're up," the Genie says to the manager.
The manager says, "I want those two back in the office after lunch."

Moral of the story
Always let your boss have the first say.
*********

Lesson 4:

An eagle was sitting on a tree resting, doing nothing. A small rabbit saw the eagle and asked him, "Can I also sit like you and do nothing?"
The eagle answered: "Sure , why not."
So, the rabbit sat on the ground below the eagle and rested. All of a sudden, a fox appeared, jumped on the rabbit and ate it.

Moral of the story
To be sitting and doing nothing, you must be sitting very, very high up.
*********

Lesson 5:

A turkey was chatting with a bull. "I would love to be able to get to the top of that tree," sighed the turkey,"but I haven't got the energy."
"Well, why don't you nibble on some of my droppings?" replied the bull.
They're packed with nutrients."
The turkey pecked at a lump of dung, and found it actually gave him enough strength to reach the lowest branch of the tree.
The next day, after eating some more dung, he reached the second branch..
Finally after a fourth night, the turkey was proudly perched at the top of the tree. He was promptly spotted by a farmer, who shot him out of the tree.

Moral of the story
BullShit might get you to the top, but it won't keep you there.
*********

Lesson 6:

A little bird was flying south for the Winter.It was so cold the bird froze and fell to the ground into a large field. While he was lying there, a cow came by and dropped some dung on him. As the frozen bird lay there in the pile of cow dung, he began to realize how warm he was.
The dung was actually thawing him out! He lay there all warm and happy, and soon began to sing for joy.
A passing cat heard the bird singing and came to investigate.
Following the sound, the cat discovered the bird under the pile of cow dung, and promptly dug him out and ate him.

Morals of this story

(1) Not everyone who shits on you is your enemy.

(2) Not everyone who gets you out of shit is your friend.

(3) And when you're in deep shit, it's best to keep your mouth shut!

Lesson 7:

A new vacuum cleaner salesman knocked on the door on the first house of the street. A tall lady answered the door.

Before she could speak, the enthusiastic salesman barged into the living room and opened a big black plastic bag and poured all the cow droppings onto the carpet.
"Madam, if I could not clean this up with the use of this new powerful Vacuum cleaner, I will EAT all this dung!" exclaimed the eager salesman.
"Do you need chilly sauce or ketchup with that" asked the lady.
The bewildered salesman asked, "Why, madam?"

"There's no electricity in the house..." said the lady.

MORAL: Gather all requirements and resources before working on any project and committing to the client...!!!

Honorable MEN

Must Read for Every Man

and of course Woman (to

understand man)

If a female is reading this article

then just realize the value of a

man; and if its a male then feel

proud of after reading it!

"One day, while a woodcutter

was cutting a branch of a tree

above a river, his axe fell into

the river. When he cried out, the

Lord appeared and asked, "Why

are you crying?"

The woodcutter replied that his

axe has fallen into water, and he

needed the axe to make his

living.

The Lord went down into the

water and reappeared with a

golden axe. "Is this your axe?"

the Lord asked.

The woodcutter replied, "No."

The Lord again went down and

came up with a silver Axe. "Is

this your axe?" the Lord asked.

Again, the woodcutter replied,

"No."

The Lord went down again and

came up with an iron Axe. "Is

this your axe?" the Lord asked.

The woodcutter replied, "Yes."

The Lord was pleased with the

man's honesty and gave him all

three axes to keep, and the

woodcutter went home happy.

Some time later the woodcutter

was walking with his wife along

the riverbank, and his wife fell

into the river. When he cried

out, the Lord again appeared

and asked him, "Why are you

crying?"

"Oh Lord, my wife has fallen

into the water!"

The Lord went down into the

water and came up with

ANGELINA JOLIE "Is this your

wife?" the Lord asked.

"Yes," cried the woodcutter.

The Lord was furious. "You lied!

That is an untruth!"

The woodcutter replied, "Oh,

forgive me, my Lord. It is a

misunderstanding. You see, if I

had said 'no' to ANGELINA

JOLIE , You would have come up

with CAMERON DIAZ . Then if I

said 'no' to her, you would have

come up with my wife . Had I

then said 'yes,' you would have

given me all three. Lord, I am a

poor man, and am not able to

take care of all three wives, so

THAT'S

why I said yes to ANGELINA

JOLIE ."

The moral of this story is:

Whenever a man lies, it is for a

good and honorable reason, and

for the benefit of others.

That's our story, and we're

sticking to it! - "WE ARE

HONORABLE

MEN!!!!!!"