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.
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.
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!
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.
How about something like:
Code: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 '
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
Code: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
Hey i think you need something like this!
Bookmarks