Link to home
Start Free TrialLog in
Avatar of bcolour79
bcolour79

asked on

vb.net Excel to Access

Dear all,

How do i select a few rows from excel and export it access (vb)?

Can i do it in a dataview ways, with checkbox. If can't, what is the easiest way to export Excel info to Access using vb.net?
Avatar of Sancler
Sancler

Here's a direct cut and paste from one of my utitlity modules.  It is not what you want, but it may provide a basis for you to work from.

    Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)

        'Sub transfers all records from .xls sourcefile.sourcesheet ...
        '... to .mdb targetfile.targettable
        'It is assumed that the .mdb targettable definition already ...
        '... exists, with the same number and types of fields, ...
        '... in the same order, as the .xls worksheet columns.
        'It does not depend on the .mdb field names being the same ...
        '... as the .xls column headings: although it does assume ...
        '... that the .xls columns are named.

        If Not sourceSheet.EndsWith("$") Then
            sourceSheet &= "$"
        End If

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT * FROM " & targetTable

        'use dataadapter for target and command builder to ...
        '... create insert command, including parameter collection
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

        'now do the work
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 0 To sourceReader.FieldCount - 1
                'load values into parameters
                targetCommand.Parameters(i).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next
            '... or here
            'then write to target
            targetCommand.ExecuteNonQuery()
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

    End Sub

It is not what you want because - see comments at start of the code - it transfers full sheets rather than selected ranges.  But it shouldn't be too difficult for you to modify it in that respect.  And this bit

            For i As Integer = 0 To sourceReader.FieldCount - 1
                'load values into parameters
                targetCommand.Parameters(i).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next
            '... or here

can be replaced by specific lines for each element of data that you want to transfer, like

           targetCommand.Parameters(0).Value = sourceReader(1)
           targetCommand.Parameters(1).Value = sourceReader("ThisName")

rather than just cycling through all fields/columns in order.

Roger
Avatar of bcolour79

ASKER

how bout gettin those data from a datagrid to access?

from my understanding of asp.net i can actually use "For Each dgi As DataGridItem In dgStock.Items" to update/insert datagrid info into ms access db.

but unfortunately this function can't be use in vb.net.

i was able to import all excel info to datagrid but fail to export it back to a access files.

please advise
I don't do Web/ASP stuff, only WinForms/VB, but my understanding is that a significant difference is that, on the ASP side, the DataGrid effectively acts as the data store whereas, on the VB side, the DataGrid is just a display/editing mechanism for data which is actually stored in a DataTable.

So, how were you "able to import all excel info to datagrid"?  If, as would be normal in VB, you did so by reading it into a DataTable and then binding the DataGrid to that DataTable, any export to Access would be from the DataTable not the DataGrid.  So it would be on the lines of

   For Each dr As DataRow In MyTable.Rows '... etc

and then the cell/field values would be accessible within dr by Item(Index) although, as Item is the default property of a datarow, you could simply put

      outputValue(0) = dr(0) '... etc

Roger
Erm... these are my code.... how i select the coloumn in the datagrid and export it to my access db?

            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source=" & files & "; " & "Extended Properties=Excel 8.0;")

            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Attendence")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
           
            DataGrid1.DataSource = DtSet.Tables(0)

            Dim r As Integer
            r = DataGrid1.CurrentCell.RowNumber
            Dim i As Integer = DataGrid1.DataSource.rows.count()
            lblerror.Text = "i = " & i
           
            Dim x As Integer = 0
            While i <> x
                DataGrid1.Item(x, 14) = Now().Day & "-" & Now().Month & "-" & Now().Year
                DataGrid1.Item(x, 15) = System.IO.Path.GetFileName(OpenFile.FileName.ToString)
                x = x + 1
            End While
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial