updating data in excel file

[daz95]

hey guys, im fairly new to vb, and so would like a bit of help with this.
I have a vb form with textbox inputs and am trying to store the inputs to an existing excel file. Basically i need to open the excel file, write the data to a row, and save it. Then each time the program is run it should add the new values to the next row down in the file. any help with this would be much appreciated.

[si_the_geek]

Moved to Office Development forum

You can do this fairly easily using automation code. To do this, add a reference ("Project"->"References") to "Microsoft Excel X.X Object library", then you can use code like this:

Dim oXLApp as Excel.Application         'Declare the object variables
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
 
  Set oXLApp = New Excel.Application    'Create a new instance of Excel
  Set oXLBook = oXLApp.Workbooks.Open("[u]C:\folder\file.xls[/u]")    'Open your workbook
  Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
 
Dim lngRow as Long
  With oXLSheet           'Find the row to write to
    lngRow = .UsedRange.Rows.Count + 1
                          'Write the values
    .Cells(lngRow, [U]1[/U]) = [U]Text1.Text[/U]
    .Cells(lngRow, [U]2[/U]) = [U]Text2.Text[/U]
  End With
 
  Set oXLSheet = Nothing             'disconnect from the Worksheet
  oXLBook.Close SaveChanges:= True   'Save (and disconnect from) the Workbook 
  Set oXLBook = Nothing
  oXLApp.Quit                        'Close (and disconnect from) Excel 
  Set oXLApp = Nothing

[daz95]

Nice one mate thanks a lot, the only query I have is that I get an error message on this line

lngRow = UsedRange.Rows.Count + 1

stating "object required"

Any ideas on this?

Cheers

[daz95]

Edit reply. Problem solved all sorted. Thanks a lot mate, good stuff.

[si_the_geek]

Ah yes, I missed the dot! (now corrected in my post above)

As you have the answer, could you do us a favour and mark this thread as resolved? Just above the first post is a 'Thread tools' menu which contains 'Mark thread resolved'.