Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-02-2009
Member
 
Join Date: Jan 2009
Posts: 38
Connecting Ms Excel to vb.net retrieval & storage of data?

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.
Reply With Quote
  #2  
Old 21-02-2009
Member
 
Join Date: Aug 2008
Posts: 145
Re: Connecting Ms Excel to vb.net retrieval & storage of data?

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!
Reply With Quote
  #3  
Old 21-02-2009
Member
 
Join Date: Jan 2008
Posts: 24
Re: Connecting Ms Excel to vb.net retrieval & storage of data?

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.
Reply With Quote
  #4  
Old 21-02-2009
Member
 
Join Date: May 2008
Posts: 115
Re: Connecting Ms Excel to vb.net retrieval & storage of data?

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
       
'
Reply With Quote
  #5  
Old 21-02-2009
Member
 
Join Date: May 2008
Posts: 63
Re: Connecting Ms Excel to vb.net retrieval & storage of data?

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
Reply With Quote
  #6  
Old 21-02-2009
Member
 
Join Date: May 2008
Posts: 44
Re: Connecting Ms Excel to vb.net retrieval & storage of data?

Hey i think you need something like this!
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Connecting Ms Excel to vb.net retrieval & storage of data?"
Thread Thread Starter Forum Replies Last Post
How to insert the corresponding data into multiple worksheets from a range of data in Excel ZaidaB Windows Software 2 22-01-2012 11:59 AM
Nokia X6 disconnects randomly after connecting in mass storage mode Arima Portable Devices 6 16-09-2011 10:11 PM
Cannot access Memory Card after connecting LG Optimus 2X USB storage Arima Portable Devices 6 19-08-2011 11:16 PM
How to check data validation compatibility of excel 2010 on excel 2007 Zoello Windows Software 6 17-05-2011 10:00 PM
Checkbox array storage and retrieval in Php Harper Software Development 3 03-08-2009 11:34 PM


All times are GMT +5.5. The time now is 09:00 AM.