OOP in VB6

[rothj0hn]

Guys do you have a tutorial and samples in OOP VB6..
Thanks

[Nightwalker83]

There is no point because VB6 uses a different way of doing things it uses the Component Object Model.

Edit:

However, if you want to try here is one of the sites I found in google searching for "oop in vb6" you might find more but I can't really see the point in doing oop in VB6 because there are a lot of things you can not do.

[dilettante]

Actually VB6 is very object oriented, and probably could have been called Object Basic.

Yes, it does use a subset of COM, but for the most part the thing you might miss is inheritance, one way of implementing polymorphism. Even that can be done in VB6 using interfaces though.

The whole OOP thing gets silly very quickly, and you'll find plenty of religious arguments about it, mostly stemming from C++ bigots and their stepchildren (Java and .Net folks).

It is better to ask more specific questions than something as broad as "how to do OOP" which is nearly meaningless. Have you at least read and studied Programming with Objects first? The VB6 manuals exist for a good reason, and almost all of the material is in the MSDN CDs shipped with every legitimate VB6 Edition beyond Learning Edition.


If you insist on inheritance you will of course need to use a language in the C++ tradition. If so, that's one way to go. But a shocking number of people have never read the docs and actually studied VB6, which must make life difficult trying to make significant use of it. They become all too likely to accept the trolling of those who don't know enough to have a valid opinion.

[rothj0hn]

Is this possible? And how?

For example i have to class Person and Pet.

Person Properties:
* Name
* Gender
* Age
* Pet

Pet Properties:
* Pet_Name
* Pet_Type (dog,cat,etc...)

Questions:
1. How can i create a new person and add some pet to that person. How can i call the pet class in person class? Do i need to declare all properties of pet class in the person class?
2. How can i Display all person and all the pet that person have?

Can you please give me a sample, Im using Visual basic 6.0

[jcis]

Is that homework or just an example you invented? Anyway, all that is very easy to do with VB6 when you understand how to create Classes, instantiate Classes (create objects), using Classes from inside other Classes, etc.. A good place to start would be reading and working with the info you'll find in the link on Dilettante's post.

[rothj0hn]

It was just a sample. Thanks

[rothj0hn]

Can you give me a sample based on person and pet? Im confused.

In my Person Class how can i create a new pet?
Should i create all the properties of pet class in person class?

So in my form it coult be like this:

Dim p1 as new person1

p1.Name = "AA"
p1.Age = "18"
p1.Gender = "Male"
p1.Pet_Name = "Bark"
p1.Pet_Type = "Dog"
p1.NewPet()

Assuming NewPet will create a pet name bark, type dog to person name AA.

How can i do this?

[jcis]

Ok, following your sample:

ClsPerson:

Option Explicit

Private mName As String, mGender As String, mAge As Long, mPets As Collection

Public Property Get Name() As String
   Name = mName
End Property

Public Property Let Name(pName As String)
   mName = pName
End Property

Public Property Get Gender() As String
   Gender = mGender
End Property

Public Property Let Gender(pGender As String)
   mGender = pGender
End Property

Public Property Get Age() As Long
   Age = mAge
End Property

Public Property Let Age(pAge As Long)
   mAge = pAge
End Property

Public Property Get Pets() As Collection
    Set Pets = mPets
End Property

Public Sub AddPet(pPet_Name As String, pPet_Type As String)
Dim lPet As ClsPet
    Set lPet = New ClsPet
    
    lPet.Pet_Name = pPet_Name
    lPet.Pet_Type = pPet_Type
    
    mPets.Add lPet
    Set lPet = Nothing
End Sub

Private Sub Class_Initialize()
    Set mPets = New Collection
End Sub

Private Sub Class_Terminate()
    Set mPets = Nothing
End Sub

ClsPet:

Option Explicit

Private mPet_Name As String, mPet_Type As String

Public Property Get Pet_Name() As String
   Pet_Name = mPet_Name
End Property

Public Property Let Pet_Name(pPet_Name As String)
   mPet_Name = pPet_Name
End Property

Public Property Get Pet_Type() As String
    Pet_Type = mPet_Type
End Property

Public Property Let Pet_Type(pPet_Type As String)
   mPet_Type = pPet_Type
End Property

Example on how to use the Classes, creating 2 persons, one with 3 pets and another with 2 pets. Storing this 2 persons in a collection so later it can be iterated to get the data back:

Private Sub Command1_Click()
    Dim ObjPerson As ClsPerson, ObjPet As ClsPet, lCol As Collection

    Set lCol = New Collection

    'Creating Person Jhon that has 3 Pets
    Set ObjPerson = New ClsPerson
    With ObjPerson
        .Name = "Jhon"
        .Age = 30
        .Gender = "Male"
        .AddPet "Spike", "Dog"
        .AddPet "Tom", "Tiger"
        .AddPet "Felix", "Cat"
    End With
    lCol.Add ObjPerson
    
    'Creating Person Susan that has 2 Pets
    Set ObjPerson = New ClsPerson
    With ObjPerson
        .Name = "Susan"
        .Age = 24
        .Gender = "Female"
        .AddPet "Newton", "Dog"
        .AddPet "Bugs", "Bunny"
    End With
    lCol.Add ObjPerson
    
    'Print all in Form:
    For Each ObjPerson In lCol
        Me.Print "Person Name: " & ObjPerson.Name
        Me.Print "Person Age: " & ObjPerson.Age
        Me.Print "Person Gender: " & ObjPerson.Gender
        Me.Print "Person Pets: "
        
        For Each ObjPet In ObjPerson.Pets
            Me.Print vbNullString
            Me.Print vbTab & "Pet Name : " & ObjPet.Pet_Name
            Me.Print vbTab & "Pet Type : " & ObjPet.Pet_Type
        Next
        Me.Print vbNullString
    Next
        
    Set ObjPerson = Nothing
    Set ObjPet = Nothing
End Sub

Now the answer to your questions:

How can i create a new person and add some pet to that person?

It's this part:

    'Creating Person Jhon that has 3 Pets
    Set ObjPerson = New ClsPerson
    With ObjPerson
        .Name = "Jhon"
        .Age = 30
        .Gender = "Male"
        .AddPet "Spike", "Dog"
        .AddPet "Tom", "Tiger"
        .AddPet "Felix", "Cat"
    End With

How can i call the pet class in person class?

That question should be: How can i call the Pet Object (and its properties) from person Object? (Object = instantiated Class). It's this part:

        For Each ObjPet In ObjPerson.Pets
            Me.Print vbNullString
            Me.Print vbTab & "Pet Name : " & ObjPet.Pet_Name
            Me.Print vbTab & "Pet Type : " & ObjPet.Pet_Type
        Next

Do i need to declare all properties of pet class in the person class?

No, clsPerson Class has only Person code and clsPet Class has only Pet code; But: the clsPerson Class has a Pets Collection, it's just a container where the Pets can be dropped, I used a Collection so one Person can have Multiple Pets, if you want each person to have only one Pet then it's even easier: remove the Pet Collection inside clsPet Class and use a Pet Object there (this change will affect the code outside). Class Person also have a AddPet method, i prefer to do it this way using a method but this could also be done from outside (in the Form) by directly referencing the Pet Object insde the Person Object, you can do it like this if you want.

2. How can i Display all person and all the pet that person have?

That's the last part in code, those 2 For Each interate all Persons and each Person's Pets printing all in your Form.

[firoz.raj]

Can you give me a sample based on person and pet? Im confused.

In my Person Class how can i create a new pet?
Should i create all the properties of pet class in person class?

here in the following code . you will see how to add the different person.and altogether collecting into the collection variable mcol.assumed you have already have
person and pet class.hope that might be helpful .same way you can also create pet class what jcis has recommend .and you can add it into the collection.

Private mcol As New Collection
Dim m_Person As New Person


Private Sub Command2_Click()
For Each Person In mcol
    Me.Print "PersonName =" & m_Person.Name
    Me.Print "Person Age=" & m_Person.Age
    Me.Print "person gender=" & m_Person.Gender
Next
MsgBox ("all person are added to Collection")


End Sub

Private Sub Form_Load()

    m_Person.Name = "John"
    m_Person.Age = "63"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Age)
    
    m_Person.Name = "David"
    m_Person.Age = "60"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Age)
    
    m_Person.Name = "Wof"
    m_Person.Age = "55"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Age)
        

End Sub

and here is the person class Methods.

Private m_Sname As String
Private m_lgAge As Long
Private m_sgender As String


Public Property Get Name() As String
Name = m_Sname
End Property

Public Property Let Name(ByVal vNewValue As String)
m_Sname = vNewValue
End Property


Public Property Get Age() As Long
Age = m_lgAge
End Property

Public Property Let Age(ByVal vNewValue As Long)
m_lgAge = vNewValue
End Property


Public Property Get Gender() As String
 Gender = m_sgender
End Property

Public Property Let Gender(ByVal vNewValue As String)
m_sgender = vNewValue
End Property

[rothj0hn]

Thanks a lot guys. Ill start OOP with this example.

[firoz.raj]

Thanks a lot guys. Ill start OOP with this example.

Same way you can add pet class also into the collection .additional if you have other doubt just feel free to ask.

Private m_petname As String
Private m_pettype As String



Public Property Get PetName() As String
PetName = m_petname
End Property

Public Property Let PetName(ByVal vNewValue As String)
m_petname = vNewValue
End Property

Public Property Get PetType() As String
PetType = m_pettype
End Property

Public Property Let PetType(ByVal vNewValue As String)
m_pettype = vNewValue
End Property


Private m_Sname As String
Private m_lgAge As Long
Private m_sgender As String


Public Property Get Name() As String
Name = m_Sname
End Property

Public Property Let Name(ByVal vNewValue As String)
m_Sname = vNewValue
End Property


Public Property Get Age() As Long
Age = m_lgAge
End Property

Public Property Let Age(ByVal vNewValue As Long)
m_lgAge = vNewValue
End Property


Public Property Get Gender() As String
 Gender = m_sgender
End Property

Public Property Let Gender(ByVal vNewValue As String)
m_sgender = vNewValue
End Property

Public Property Get Key() As String
Key = "A" & CStr(m_lgAge)
End Property

Private mcol As New Collection
Dim m_Person As New Person


Private Sub Command2_Click()
For Each Person In mcol
    Me.Print "PersonName =" & m_Person.Name
    Me.Print "Person Age=" & m_Person.Age
    Me.Print "person gender=" & m_Person.Gender
Next
MsgBox ("all person are added to Collection")


End Sub

Private Sub Form_Load()

    m_Person.Name = "John"
    m_Person.Age = "63"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Key)
    
    m_Person.Name = "David"
    m_Person.Age = "60"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Key)
    
    m_Person.Name = "Wof"
    m_Person.Age = "55"
    m_Person.Gender = "Male"
    mcol.Add (m_Person.Key)
        

End Sub

[rothj0hn]

Regarding with the function..

Public Sub AddPet(pPet_Name As String, pPet_Type As String)

If i had to add properties to my pet like age, gender, breed, color, and etc.. then it would be like this, .AddPet "Felix", "Dog","13","Female","Labrador","black","etc"...

cant it be done like this?

With ObjPet
.Name = "Felix"
.Type = "Dog"
.Age = 13
.Gender = "Female"
.Breed = "Labrador"
.Color = "Black"
.Etc = "Etc"
.Add
End With

[jcis]

Yes, you can do it like that, simple example (using the code i posted)

    Dim oPerson As ClsPerson, oPet As ClsPet

    Set oPerson = New ClsPerson 'Create an Instance of a Person
    
    Set oPet = New ClsPet 'Create an Instance of a Pet
    
    'Add values to Pet Properties:
    With oPet
        .Pet_Name = "Felix"
        .Pet_Type = "Cat"
        ' + Any other property..
    End With
    
    'Add Pet oPet to Person oPerson:
    oPerson.Pets.Add oPet

[rothj0hn]

Thanks

[Max Peck]

The whole OOP thing gets silly very quickly, and you'll find plenty of religious arguments about it, mostly stemming from C++ bigots and their stepchildren (Java and .Net folks).

While I agree that such discussions get "religious" and silly quickly, your statement (reference to C++ bigots and Java/.Net 'step-children') is a little like the pot calling the kettle black.

-Max

[firoz.raj]

it seems jcis having a good understanding of oops .i would like to ask a question on this .i have been trying to add 3 person in my collection variable m_persons.

for the collection of 3 person i have made one method Add at Persons class . but when i debug and it comes at Set Add = objnewmemebr .after comming at this line when i press f8 .it says object requires.so can you tell me ?why i am getting this message .becuase it has already collected in the mcol collection .after collecting this i am returning the collected value .i am attaching my programme also.i have to collect all the person in my collection m_persons and finally i need to
print .that is all about .let me know some light on it .

Private m_persons As New Persons
Private m_person As New Person


Private Sub btShowCollectionData_Click()
For Each Person In m_persons
    Me.Print "PersonName =" & m_person.Name
    Me.Print "Person Age=" & m_person.Age
    Me.Print "person gender=" & m_person.Gender
Next
MsgBox ("all person are added to Collection")  'I Want to show all the collected value at fom_load in the moment it shows only one john
 'and their corresponding value gender,age .........
End Sub

Private Sub Form_Load()
    m_person.Name = "John"
    m_person.Age = 63
    m_person.Gender = "Male"
 Call m_persons.Add(m_person)
    
    m_person.Name = "David"
    m_person.Age = "60"
    m_person.Gender = "Male"
    Call m_persons.Add(m_person)

    m_person.Name = "Wof"
    m_person.Age = "55"
    m_person.Gender = "Male"
   Call m_persons.Add(m_person)

End Sub

here are the persons collection code .

'local variable to hold collection
Private mCol As Collection


Public Function Add(objnewmemeber As Person) As Person
     Call mCol.Add(objnewmemember, objnewmemeber.Key)
       'return the object created
       Set Add = objnewmemebr        Set objnewmember = Nothing
End Function

Public Property Get Item(Skey As String) As Person
     Set Item = mCol(Skey)
End Property



Public Property Get Count() As Long
       Count = mCol.Count
End Property


Public Sub Remove(vntIndexKey As Variant)
 
    mCol.Remove vntIndexKey
End Sub


Public Property Get NewEnum() As IUnknown
    'this property allows you to enumerate
    'this collection with the For...Each syntax
    Set NewEnum = mCol.[_NewEnum]
End Property


Private Sub Class_Initialize()
    'creates the collection when this class is created
    Set mCol = New Collection
End Sub


Private Sub Class_Terminate()
    'destroys collection when this class is terminated
    Set mCol = Nothing
End Sub

[jcis]

In your Add Function you're using 3 different object names when i think you want to use one and the same, verify your syntax:

objnewmemember
objnewmemebr
objnewmember

[firoz.raj]

yep that was really typo .now it is ok .

[jcis]

You still have bad syntax, the line in yellow says objnewmemebr, the others say objnewmember.

Yes, by making these correction your project runs ok for me.

[firoz.raj]

Now i simple want to modify collection data .so is it possible to modify collection data on runtime ?give some light on it .
i am again attaching project for it .i have added button btEditColData for it .

[jcis]

MsgBox m_persons.Item("A63").Name 'Jhon is the name for your item "A63"
m_persons.Item("A63").Name = "Dave"
MsgBox m_persons.Item("A63").Name 'Now Dave is the name for your item "A63"
 
MsgBox m_persons.Item("A55").Gender  'your item "A55" is a male
m_persons.Item("A55").Gender = "Female"
MsgBox m_persons.Item("A55").Gender 'Now your item "A55" is a female
 
'You can also directly use a lPerson object to do this work:
Dim lPerson As Person
Set lPerson = m_persons.Item("A60")
lPerson.Name = "Rich"
MsgBox m_persons.Item("A60").Name 'Now your item "A55" has Name Rich
 
'Note that you changed object lPerson and the object m_persons.Item("A60") also changed, this happens simply because
'your new object variable lPerson and  m_persons.Item("A60") both are pointers to the same object, literally meaning the same space in memory.
'This is why i didn't use AS NEW when i declared lPerson, because i wanted it to point to an object that already exists in memory
'NEW creates a new space in memory for an Object. It's a good idea never declaring an object AS NEW, because in cases like this we need the declaration but
'we don't want a new space in mamory, because we want to point this object to an object that's already alive in memory
 
'A bit complicated might be.. but this concept about your objects being just pointers (in cases like this) to the same space in memory is very important.
 
Set lPerson = Nothing 
' This final line means lPerson is not pointing to the object anymore, not that the object will be destroyed, 
' an object is removed from memory just when no variable is pointing at it, in this case the collection is still pointing at it in one of its items.

[rothj0hn]

Guys I need some opinion, which of the two codes is best to use, or if you have a suggestion on how to do it..

Private gcnRIS      As ADODB.Connection
Private pstConnStr  As String
Private mvarErrDesc As String
 
Public Property Get ErrDesc() As String
    ErrDesc = mvarErrDesc
End Property
 
Public Property Let Connection(ByVal vData As ADODB.Connection)
    Set gcnRIS = vData
End Property
 
Public Function SearchSQL(sQuery As String) As ADODB.Recordset
    Set SearchSQL = New ADODB.Recordset
    SearchSQL.Open sQuery, gcnRIS, adOpenStatic, adLockReadOnly, adCmdText
End Function
 
Public Function ExecuteSQL(sQuery As String) As Boolean
 
On Error GoTo ExecuteSQLErr
    
    gcnRIS.Execute sQuery, adExecuteNoRecords
    ExecuteSQL = True
    Exit Function
    
ExecuteSQLErr:
    mvarErrDesc = Err.Description
    ExecuteSQL = False
End Function

Public Function SearchSQL(gcnRIS as ADODB.Connectnon, sQuery As String) As ADODB.Recordset
    Set SearchSQL = New ADODB.Recordset
    SearchSQL.Open sQuery, gcnRIS, adOpenStatic, adLockReadOnly, adCmdText
End Function
 
Public Function ExecuteSQL(gcnRIS as ADODB.Connectnon, sQuery As String) As Boolean
 
On Error GoTo ExecuteSQLErr
    
    gcnRIS.Execute sQuery, adExecuteNoRecords
    ExecuteSQL = True
    Exit Function
    
ExecuteSQLErr:
    mvarErrDesc = Err.Description
    ExecuteSQL = False
End Function

[firoz.raj]

hi jcis ,can you see why i am unable to fill empdetail in a listview ?.i have tried my level best .but still i am unable to fill empdetail.Kindly let me know .
see the attachment .as i still i am struggling .syntax seems to be ok of adding item in a listview .

Option Explicit
Private m_cEmplyees As Employees
Private m_cEmployee As Employee

Private Sub Form_Load()
Dim strsql$
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
con.ConnectionString = ConString
con.Open
strsql = "Select * from employees"
Set rs = New ADODB.Recordset
Set m_cEmplyees = New Employees
rs.CursorLocation = adUseClient
rs.Open strsql, con, adOpenDynamic, adLockOptimistic
rs.MoveFirst
Do
    Set m_cEmployee = New Employee
    m_cEmployee.EmpID = rs.Fields("Employee_id")
    m_cEmployee.EmpName = rs.Fields("Name")
    m_cEmployee.Position = rs.Fields("Position")
    m_cEmployee.DeptID = rs.Fields("Department_id")
    Call m_cEmplyees.Add(m_cEmployee)
    Call FillListView
 rs.MoveNext
 If rs.EOF Then Exit Do
Loop
End Sub

Public Sub FillListView()
Dim Item As ListItem
For Each m_cEmployee In m_cEmplyees
Set Item = ListView1.ListItems.Add(m_cEmployee.EmpID, m_cEmployee.Key)
Next
End Sub

[Karim Wafi]

@firoz.raj: Try the attachment.

[firoz.raj]

Hi Karim Wafi & Jcis .
another point when user select item in a listview .then that selected item need to point in a text box for edit ? .can i get any comment .Kindly let me know .
see the attached rar for more explanation .any help would be highly appreciated .Thx in advance .

Option Explicit
Dim emp As Employee
Private Sub Form_Load()
'here how should i select specific subitem(index) of listitems collection
'after that we need to search this subitem(index) into our collection employees
Call FetchEmployee
End Sub

Public Sub FetchEmployee()
Text1.Text = emp.EmpID
Text2.Text = emp.EmpName
Text3.Text = emp.Position
End Sub

[Karim Wafi]

Hi Karim Wafi & Jcis .
another point when user select item in a listview .then that selected item need to point in a text box for edit ? .can i get any comment .Kindly let me know .
see the attached rar for more explanation .any help would be highly appreciated .Thx in advance .

OK. I have modified your project. Now you can modify the data from Listview SelectedItem, modify object in collection and save to database. See the attachment, full commented.



-

[firoz.raj]

instead of using public variable .i want to transport the class using event .is thier anyway ?let me know please.

Private m_cEmplyees As Employees
Private m_cEmployee As Employee

Public Event SendData(m_cEmployee As Employee)


Private Sub Command1_Click()
' Dim frm As New Form2
' Set frm.emp = m_cEmplyees.Item("E" & ListView1.SelectedItem.Text)
' frm.Show
Dim frm As Form2
RaiseEvent SendData(m_cEmployee)
Form2.Show

End Sub

[firoz.raj]

Hi Karim & Jcis Again
'Here after deleting the rows from Employees Table .i want to refresh the listview. 'because when i delete the rows it still shows in the listview.It needs to delete immediately just after deleting from the listview .can you tell me how should i refresh.after deleting specific employeeID From the Employees Table.see the attached
zip .

Private Sub Command2_Click()
Dim strsql$


       
    With m_cEmplyees     'Delete from Collection
         .Remove (ListView1.SelectedItem.Index)
    End With
    
   strsql = "delete from Employees where Employee_id= " & ListView1.SelectedItem.Text
   Debug.Print strsql
   con.Execute strsql
   ListView1.Refresh 'when i reload the form it works .but it does not work immediately at runtime
   'here how should i refresh the listview after deleting specific Employee From the table
   
  
    
End Sub

[firoz.raj]

Hi Jcis & Karim it seems only you two person have full concept of oop in the forum itself .can you tell me ?.why i am unable to generate excel sheet .if user should select one of the item .corresponding detail needs to be come
in excel sheet that is ok .but i am getting error when press f8 .and after pressing f8 it says .see the Red .class does not support Automation or does not support expected interface .let me know some idea.

Private Sub btPrint_Click()
 
       Set m_cEmployee = m_cEmplyees("E" & ListView1.SelectedItem.Text)
       Call WriteExcelSheet(m_cEmployee)
End Sub

here is the code to generate Excel sheet !

Public Sub WriteExcelSheet(m_eEmployee As Employee)
'Dim m_casedetail As CaseDetail
Dim strNames(7) As String
Dim fileName As String
Dim xl As New Excel.Application
Dim wb As New Excel.Workbook
Dim ws As New Excel.Worksheet
Dim I As Integer
Dim rowNo As Integer
    strNames(0) = "EMPLOYEE ID"
    strNames(1) = "EMPLOYEE NAME"
    strNames(2) = "EMPLOYEE POSITION"
    
 xl.Visible = False
 xl.UserControl = False
 
For I = 0 To 7
     ws.Range("A1").Offset(0, I).Value = UCase(strNames(I))  'Now i have written all Headers
     ws.Range("A1:K1").Font.Bold = True
Next

For Each m_eEmployee In m_cEmplyees
        rowNo = rowNo + 1
        ws.Cells(rowNo, 1) = m_cEmployee.EmpID
        ws.Cells(rowNo, 2) = m_cEmployee.EmpName
        ws.Cells(rowNo, 3) = m_cEmployee.Position
       
Next
ws.Application.DisplayAlerts = False
ws.Cells.EntireColumn.AutoFit
ws.Cells.Select
ws.Range("A2").Select
fileName = "C:\FIR\" & "FIRPROJECT" & Format$(Now, "ddmmyyyy_HHMMSS") & ".xls"
ws.Application.DisplayAlerts = False
wb.SaveAs fileName
wb.Close
Set ws = Nothing
Set wb = Nothing

End Sub

[jcis]

The problems you have here are not related to OOP, the problem is how you Instantiate your Excel App/Workbook/Worksheet, in your code there is not relation between them, i mean, you created a workbook alone and never tell vb that workbook belongs to an Excel instance called xl (object). Try this code, i painted in red the more important lines that show how to use Excel from VB: Creating/referencing and destroing each object instance correctly is the most important.

Public Sub WriteExcelSheet(m_eEmployee As EMPLOYEE)
Dim strNames(7) As String
Dim fileName As String
Dim I As Integer
Dim rowNo As Integer

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)

    strNames(0) = "EMPLOYEE ID"
    strNames(1) = "EMPLOYEE NAME"
    strNames(2) = "EMPLOYEE POSITION"
    
    For I = 0 To 7
         ws.Range("A1").Offset(0, I).Value = UCase(strNames(I))  'Now i have written all Headers
         ws.Range("A1:K1").Font.Bold = True
    Next
    
    rowNo = 1
    For Each m_eEmployee In m_cEmplyees
        rowNo = rowNo + 1
        ws.Cells(rowNo, 1) = m_eEmployee.EmpID
        ws.Cells(rowNo, 2) = m_eEmployee.EmpName
        ws.Cells(rowNo, 3) = m_eEmployee.Position
    Next
    
    xl.DisplayAlerts = False
    ws.Cells.EntireColumn.AutoFit
    ws.Cells.Select
    ws.Range("A2").Select
    fileName = "C:\FIR\" & "FIRPROJECT" & Format$(Now, "ddmmyyyy_HHMMSS") & ".xls"
    wb.SaveAs fileName
    
    wb.Close
    Set wb = Nothing
    xl.Application.Quit
    Set xl = Nothing
End Sub

The best idea is adding (only for developing) xl.Visible = True somewhere in the middle so you can debug (with F8) step by step and you can see how each line in VB affects your worksheet, for example by doing this i saw that your rowNo variable had to start from 1 so when incremented will use row 2 and not row 1 thats used for titles.

Also important:
1) After executing you app ensure no Excel instance remain alive (go to task manager and look for excel as proccess), if it's there then your're not destroying your objects correctly.
2) when you finish coding and everything works ok it's an excellent idea to transform excel object declarations to Late Binding and remove the project reference, this will ensure your code will work with any Excel version, if you don't do it then, for example: assuming you coded using office version 2003, this will fire an error when another user try to run it using offfice 2007 or 2011 or whatever version <> 2003.

[firoz.raj]

hi jcis, i have very thankful to know .if we use createobject function means early binding .even no need to reference excel.exe from references menu .and i have still having contradiction .how late binding effect excel version.i mean if i will user Late binding .it will work on all the versions of excel .let me know some comment my brother .

[jcis]

It's like this:

Early Binding: (your code)

- The reference must exist in VB Project
- You have Intellisense (meaning you can see Excel object properties/select from the list)
- Version incompatible (it will only work with the office version you have / the reference you have)

Example:

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)

Late Binding:
- No reference, it has to be removed from the project.
- No intellisense
- Compatibility with any Excel version, but offcourse the code must be valid in other versions also, it usually is, unless Microsoft for instance change a property name in later versions.

Example:

Dim xl As Object
Dim wb As Object
Dim ws As Object

    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Add
    Set ws = wb.Worksheets(1)

So, the best you can do is: keep coding with early binding, it gives you one big advantage: you have intallisense. When you're sure your code is ready for compilation, change to late binding, remove the reference, change the code (not many lines to change) and test you App then with late binding.

One more thing: There are contants comming from Excel reference in VB that begin with Xl, like: xlColorIndexAutomatic, xlEndSides xlProduct etc... If you're using any of those Excel contants they won't be available when you remove the reference, the solution for this is using your own contants instead, just see the constant value in object browser like this: write any contant name in VB, right click and select definition, there you can see the list, contants on the right, click one and see its value.

[firoz.raj]

Yep, jcis you are really genious . although intellisence did not work in late binding . intellisense has removed automatically . when i compile the project .but one more contradiction fact is there . excel 2003 excel extension is *.xls .and excel 2007 extension is *.xlsx .and may be excel 2011.also each time user need to change the extension of excel file .is there any solution for it ? .secondly when user open excel file .so there is no any need to go pageSetup and check the radio button fit to 1 page.it needs to be using by my progamme .if it is not checked .it needs to be checked by my programme automatically.let me know my brother .

Public Sub WriteExcelSheet(m_eEmployee As Employee)
Dim strNames(7) As String
Dim fileName As String
'Dim xl As New Excel.Application
'Dim wb As New Excel.Workbook
'Dim ws As New Excel.Worksheet
Dim xl As Object
Dim wb As Object
Dim ws As Object

Dim I As Integer
Dim rowNo As Integer
    strNames(0) = "EMPLOYEE ID"
    strNames(1) = "EMPLOYEE NAME"
    strNames(2) = "EMPLOYEE POSITION"
    
 
 Set xl = CreateObject("Excel.Application")
 Set wb = xl.workbooks.Add
 Set ws = wb.worksheets(1)
 
  xl.Visible = False
 xl.UserControl = False

 
For I = 0 To 7
     ws.Range("A1").Offset(0, I).Value = UCase(strNames(I))  'Now i have written all Headers
     ws.Range("A1:K1").Font.Bold = True
Next

For Each m_eEmployee In m_cEmplyees
        rowNo = rowNo + 1
        ws.Cells(rowNo, 1) = m_cEmployee.EmpID
        ws.Cells(rowNo, 2) = m_cEmployee.EmpName
        ws.Cells(rowNo, 3) = m_cEmployee.Position
       
Next
ws.Application.DisplayAlerts = False
ws.Cells.EntireColumn.AutoFit
ws.Cells.Select
ws.Range("A2").Select
fileName = "C:\FIR\" & "FIRPROJECT" & Format$(Now, "ddmmyyyy_HHMMSS") & ".xls"
ws.Application.DisplayAlerts = False
wb.SaveAs fileName
wb.Close
Set ws = Nothing
Set wb = Nothing

End Sub

[firoz.raj]

hi
using Following Code i am getting all the records from collection to the list box .

Public Sub FillListBox()
For Each rec In m_records
    List1.AddItem (rec.UserName & vbNullString)
Next
End Sub

but when i try to fill all collection records in a listview .it shows only one records .let me know some comment brother out of 5 .it needs to show all the records in the list view.

Public Sub FillListView()
  On Error GoTo errhnd
  Dim item As ListItem
 For Each rec In m_records
  Set item = ListView1.ListItems.Add(, rec.key, CStr(rec.UserID))
     With item
       .SubItems(1) = rec.UserID
       .SubItems(2) = rec.UserName
       .SubItems(3) = rec.MobileNo
       .SubItems(4) = rec.TelephoneExt
       .SubItems(5) = rec.IqamaNo
       .SubItems(6) = rec.PassportNo
       .SubItems(7) = rec.LightBillRefNo
       .SubItems(8) = rec.DishTvRefNo
       .SubItems(9) = rec.BankName
       .SubItems(10) = rec.AccountNo
       .SubItems(11) = rec.EmailID
       .SubItems(12) = rec.SponserID
       .SubItems(13) = rec.VoipUserID
       .SubItems(14) = rec.VoipPassword
     End With
 Next
errhnd:
End Sub:(