VB 2005 open/save information in excel.xls file

[lew26]

Hello,
i' am trying to learn how to access a excel file and save information into it. have been searching and I haven't had any luck what i want to accomplish is when i enter text into multiple text box and hit a save button i want it to save the entered information into each row (e.g. Row: firstName, lastname, phone number, email, etc..........) I have found something i have been working on but have received sqiggly lines. The example code is posted below and any help will be surely appreciated for i am ready to give up on it so far i have been working on this for a couple weeks now and still no progress.


P.S. the squiggle line are lit up under "xlApp.Workbooks, xlBook.Worksheets, xlBook.Close, xlApp.Quit, xlBook.Save"
The message states "The above (e.g. save, close, quit, workbooks, worksheets) are not members of 'System.Array'

The source code was not originally made by me i found it in searches online i was trying to make it work for what i am doing.





Imports excel = Microsoft.Office.Interop.Excel

Public Class ExcelWorkBookData

'*************************************************************
' You need to set a reference to the Microsoft Excel Object
' Library to use these Excel Objects.
'*************************************************************
Private xlApp As Microsoft.Office.Interop.Excel.Application() ' Excel Application Object
Private xlBook As Microsoft.Office.Interop.Excel.Workbook() ' Excel Workbook Object

'*************************************************************
' Gets the contents of an Excel Worksheet's cell.
'
' xlWorksheet: Name of a worksheet in an Excel File, for example,
' "Sheet1"
' xlCellName: Name of a Cell (Row and Column), for example,
' "A1" or "B222".
' xlFileName: Name of an Excel File, for example, "C:ExcelDatabase.xls"
'*************************************************************
Private Function GetExcel(ByVal xlFileName As String, _
ByVal xlWorksheet As String, _
ByVal xlCellName As String) As String

On Error GoTo GetExcel_Err

Dim strCellContents As String
' Create the Excel App Object
xlApp = CreateObject("Excel.Application")
' Create the Excel Workbook Object.
xlBook = xlApp.Workbooks.Open(xlFileName, )

' Get the Cell Contents
strCellContents = xlBook.Worksheets(xlWorksheet).range(xlCellName).Value

' Close the spreadsheet
xlBook.Close(SaveChanges:=False)
xlApp.Quit()
xlApp = Nothing
xlBook = Nothing

GetExcel = strCellContents

Exit Function
GetExcel_Err:
MsgBox("GetExcel Error: " & Err.Number & "-" & Err.Description)
Resume Next
End Function

'*************************************************************
' Sets the contents of an Excel Worksheet's cell.
'
' xlWorksheet: Name of a worksheet in an Excel File, for example,
' "Sheet1"
' xlCellName: Name of a Cell (Row and Column), for example,
' "A1" or "B222".
' xlFileName: Name of an Excel File, for example, "C:TestTesting.xls"
' xlCellContents: What you want to place into the Cell.
'*************************************************************
Private Sub SetExcel(ByVal xlFileName As String, _
ByVal xlWorksheet As String, _
ByVal xlCellName As String, _
ByVal xlCellContents As String)

On Error GoTo SetExcel_Err

' Create the Excel App Object
xlApp = CreateObject("Excel.Application")

' Create the Excel Workbook Object.
xlBook = xlApp.Workbooks.Open(xlFileName)

' Set the value of the Cell
xlBook.Worksheets(xlWorksheet).range(xlCellName).Value = xlCellContents

' Save changes and close the spreadsheet
xlBook.Save()
xlBook.Close(SaveChanges:=False)
xlApp.Quit()
xlApp = Nothing
xlBook = Nothing
Exit Sub
SetExcel_Err:
MsgBox("SetExcel Error: " & Err.Number & "-" & Err.Description)
Resume Next
End Sub

End Class

[pclement]

Would it be easier for you to use data access and ADO.NET? This way you won't have to worry about launching Excel or whether it is installed.

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

[elGeorge]

You have to add the Microsoft Excel 11 Library to your project

I did it, but know y have the message "Old Format or Invalid Library" when i try to open the new workbook, by the way.

[pclement]

This is a documented bug:

BUG: "Old format or invalid type library" error when automating Excel