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?
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?
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
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
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
ASKER
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.OleDbDat aAdapter
MyConnection = New System.Data.OleDb.OleDbCon nection("p rovider=Mi crosoft.Je t.OLEDB.4. 0; " & _
"data source=" & files & "; " & "Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDat aAdapter(" select * from [sheet1$]", MyConnection)
MyCommand.TableMappings.Ad d("Table", "Attendence")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGrid1.DataSource = DtSet.Tables(0)
Dim r As Integer
r = DataGrid1.CurrentCell.RowN umber
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.T oString)
x = x + 1
End While
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDat
MyConnection = New System.Data.OleDb.OleDbCon
"data source=" & files & "; " & "Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDat
MyCommand.TableMappings.Ad
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGrid1.DataSource = DtSet.Tables(0)
Dim r As Integer
r = DataGrid1.CurrentCell.RowN
Dim i As Integer = DataGrid1.DataSource.rows.
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
x = x + 1
End While
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.OL
Dim sourceSQL As String = "SELECT * FROM [" & sourceSheet & "]"
Dim targetConStr As String = "Provider=Microsoft.Jet.OL
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(targetConS
Dim targetDA As New OleDbDataAdapter(targetSQL
Dim cb As New OleDbCommandBuilder(target
Dim targetCommand As OleDbCommand = cb.GetInsertCommand
'now do the work
Dim sourceCon As New OleDbConnection(sourceConS
Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
targetCommand.Connection.O
sourceCon.Open()
Dim sourceReader As OleDbDataReader
sourceReader = sourceCommand.ExecuteReade
While sourceReader.Read()
'for each row from source
For i As Integer = 0 To sourceReader.FieldCount - 1
'load values into parameters
targetCommand.Parameters(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.ExecuteNonQu
End While
sourceReader.Close()
sourceCon.Close()
targetCommand.Connection.C
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
'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
targetCommand.Parameters(1
rather than just cycling through all fields/columns in order.
Roger