VB 2005 open/save information in excel.xls file


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: VB 2005 open/save information in excel.xls file

  1. #1
    Join Date
    Jul 2005
    Posts
    24

    VB 2005 open/save information in excel.xls file

    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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jun 2006
    Posts
    1
    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.

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 8
    Last Post: 02-15-2010, 05:53 AM
  2. How do you create a file system object in vb srcipt or vb 6.0
    By Michael Kascher in forum VB Classic
    Replies: 3
    Last Post: 09-01-2006, 04:15 PM
  3. How to create Setup.exe file given a Vb Project
    By cooldude.i2k in forum VB Classic
    Replies: 3
    Last Post: 08-05-2005, 04:13 AM
  4. NullPointerException when reading text file
    By Andrew McLellan in forum Java
    Replies: 3
    Last Post: 05-09-2001, 06:34 PM
  5. Replies: 0
    Last Post: 05-03-2001, 11:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center