Working with Excel Files Using VB6

The code is totally self explanatory, In the load event we're going to open the new instance of the excel library and our excel file “book1.xls” will be accessible from our code. Then we'll use Command1 to retrieve data from book1, please note that you must have some data in the excel file. Similarly Command2 is used to put/replace the data in the excel sheet cells.

'do declare these variables you need to add a reference
'to the microsoft excel 'xx' object library. 

'you need two text boxes and two command buttons
'on the form, an excel file in c:\book1.xls 

Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook 

Private Sub Command1_Click()
'the benifit of placing numbers in (row, col) is that you
'can loop through different directions if required. I could
'have used column names like "A1" 'etc. 

    Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1
    Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2 

'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
    xl.ActiveWorkbook.Close False, "c:\book1.xls"
    xl.Quit
End Sub 

Private Sub Command2_Click()
    xlsheet.Cells(2, 1) = Text1.Text
    xlsheet.Cells(2, 2) = Text2.Text
    xlwbook.Save 

'don't forget to do this or you'll not be able to open
'book1.xls again, untill you restart you pc.
    xl.ActiveWorkbook.Close False, "c:\book1.xls"
    xl.Quit
End Sub 

Private Sub Form_Load()
    Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
    Set xlsheet = xlwbook.Sheets.Item(1)
End Sub 

Private Sub Form_Unload(Cancel As Integer)
    Set xlwbook = Nothing
    Set xl = Nothing
End Sub 

You might also like...

Comments

Adil Hussain Raza Adil is doing MCS from Virtual University Of Pakistan. He is working as a software developer for 3 years. He has developed several customized commercial software in Haroon Abad City for various cli...

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.” - Tom Cargill