• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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?
0
bcolour79
Asked:
bcolour79
  • 3
  • 2
1 Solution
 
SanclerCommented:
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
0
 
bcolour79Author Commented:
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
0
 
SanclerCommented:
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
0
 
bcolour79Author Commented:
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
0
 
SanclerCommented:
I'm assuming that what you want to export to MS Access are the two fields you mention in your example in

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21856452.html#16716702

and that you know the names or numbers of the columns in the DataTable - DtSet.Tables(0) - that contain the values that are displayed in your DataGrid for Name and Age.

You have two choices.  You can either follow the approach from the code I first posted and use a dataadapter and command builder to generate an InsertCommand for you.  Or you can create your own InsertCommand.

The bits of code you would need from my first posting to follow the first approach would be

        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT Name, Age 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

For the second approach it would be something like

        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetCommand As New OleDbCommand
        With targetCommand
            .Connection = targetCon
            .CommandText = "INSERT INTO " & targetTable & " (Name, Age) VALUES (?, ?)"
            .Parameters.Add("0", OleDbType.VarWChar)
            .Parameters.Add("1", OleDbType.Integer)
        End With

You may need to change the type declarations in the .Parameters.Add statements.

Then, with either approach, the code would go like this

        targetCommand.Connection.Open()
        For Each dr As DataRow in DtSet.Tables(0).Rows
            targetCommand.Parameters(0).Value = dr(<Index or name of column containing Name>)
            targetCommand.Parameters(1).Value = dr(<Index or name of column containing Age>)
            targetCommand.ExecuteNonQuery()
        targetCommand.Connection.Close()

If my initial assumption was incorrect, then the detail of the code will differ.  But the same principles will apply.

Roger
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now