Search In Site

04 July, 2013

Working with Excel Files in VB

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

0 comments:

Post a Comment

Dear Visitors All The Tricks And Hacks Posted Here Are Only For Knowledge Purpose.Don't Use These for Illegal Operations.

 
Twitter Bird Gadget