extract data from spread sheet

27 Feb 2006 9:15 PM

Gonzosez

How can I extract data from an excel spread sheet?
I know the sheet name but the range varies.

28 Feb 2006 4:16 PM

Eric

Here is a quick example, hope it helps!!! :

private Sub GetExcelData()

    'Excel objects. (to access those objects, add a reference to an Excel
library)
    Dim appExcel As New Excel.Application
    Dim wkbExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet

    'File path of the excel workbook
    Dim filePath As String
    filePath = c:\excelfile.xls

    'Passwords and unprotect are optional
    wkbExcel = appExcel.Workbooks.Open(filePath, 0, , , , "password")
    wksExcel = CType(wkbExcel.Sheets("SheetName"), Excel.Worksheet)
    wksExcel.Unprotect("password")

    dim value1 as string
    dim value2 as string

    'Gets the value of the field named field1
    value1 = wksExcel.Range("field1").Value
    'Gets the value of cell A2
    value2 = wksExcel.Cells(2,1).Value

    'Save and protect are optional
    wksExcel.Protect("password")
    wkbExcel.Save()

    'Quits the workbook and Excel application.
    wkbExcel.Close()
    appExcel.Quit()
    'Cleans all excel objects from memory, otherwise the might still be in
your process list.
    ReleaseComObject(wksExcel)
    ReleaseComObject(wkbExcel)
    ReleaseComObject(appExcel)
    GC.WaitForPendingFinalizers()

End Sub

'Cleans your memory from excel com objects
Private Shared Sub ReleaseComObject(ByVal Reference As Object)
    Try
      Do Until
System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference) <= 0
      Loop
    Catch ex As Exception
      ex = ex
    Finally
      Reference = Nothing
      GC.Collect()
    End Try
End Sub