Connecting Ms Excel to vb.net retrieval & storage of data?

[Chandrakant81]

Hi,
Connecting Ms Excel to vb.net retrieval & storage of data?
I want to know if we can store or extract data from excel spreadsheet to vb.net application or get the data from excel & save to SQL database or access.

[Gauresh]

I don't know if what you asking is possible in vb.net or not but i want you to please have a look at this webpage.

http://support.microsoft.com/kb/257819

I hope this helps you!

[Bhusan]

excel has vba its visual basic like vb6

maybe that can help, that said I think VB.net will have functions to import/export to excel

try searching msdn manuals etc.

[Matellis]

How about something like:

Set objExcelA = New Excel.Application
    Set objExcelW = objExcelA.Workbooks.Open(strExcelSpreadSheet)
    Set objExcelS = objExcelW.Sheets(3)
    
'
' Lets get the data from the spread sheet, and load it into the
' Access table
'
    intRowCount = 2 'Ignore the data line
    
    Do While objExcelS.Cells(intRowCount, 4) <> ""
        With adrDetail
            .AddNew
            .Fields("CostCentre") = objExcelS.Cells(intRowCount, 4)
            .Fields("VechReg") = objExcelS.Cells(intRowCount, 7)
            .Fields("Dim6") = objExcelS.Cells(intRowCount, 8)
            .Fields("Dim7") = objExcelS.Cells(intRowCount, 9)
            .Fields("TaxCode") = objExcelS.Cells(intRowCount, 10)
            .Fields("DCFlag") = objExcelS.Cells(intRowCount, 11)
            .Fields("RidNett") = objExcelS.Cells(intRowCount, 12)
            .Fields("RidVatAmt") = objExcelS.Cells(intRowCount, 13)
            .Fields("NrvPerc") = objExcelS.Cells(intRowCount, 14)
            .Fields("Gross") = objExcelS.Cells(intRowCount, 15)
            .Fields("RecoverVat") = objExcelS.Cells(intRowCount, 16)
            .Fields("Total") = objExcelS.Cells(intRowCount, 17)
            .Fields("CurAmt1") = objExcelS.Cells(intRowCount, 18)
            .Fields("CurAmt") = objExcelS.Cells(intRowCount, 19)
            .Fields("Amount") = Val(objExcelS.Cells(intRowCount, 20))
            .Fields("Description") = objExcelS.Cells(intRowCount, 21)
            .Fields("TransDate") = objExcelS.Cells(intRowCount, 22)
            .Fields("VoucherDate") = objExcelS.Cells(intRowCount, 23)
            .Fields("Period") = objExcelS.Cells(intRowCount, 24)
            .Update
        End With
        intRowCount = intRowCount + 1
    Loop
       
'

[Alphonsus]

Based on your post, my understanding of your quesiton is that you need to delete the cells or the columns. You can use range.delete method to implement this. For more information about automating excel, you can read the excel tasks and Excel Object Model Overview in msdn.

Code Block

Imports Microsoft.Office.Interop

Public Class Form5

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim oExcel As New Microsoft.Office.Interop.Excel.Application

Dim oBook As Microsoft.Office.Interop.Excel.Workbook

Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

oBook = oExcel.Workbooks.Open("d:\temp\Book1.xlsx")

oSheet = oBook.Worksheets(1)

MessageBox.Show(oSheet.Cells(1, 1).Value)

'Dim rg As Excel.Range = oSheet.Cells(1, 1) ' delete the specific cell

Dim rg As Excel.Range = oSheet.Rows("1:1") ' delete the specific row

'Dim rg As Excel.Range = oSheet.Columns("A:A") ' delete the specific column

rg.Select()

rg.Delete()

oBook.Save()

oExcel.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

oBook = Nothing

oExcel = Nothing

End Sub

End Class

[Geo3213]

Hey i think you need something like this!