• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Getting the excel file in to the grid and store that data in Access DB

Getting the excel file in to the grid and store that data in Access DB

Getting the excel file in to the grid and store that data in Access DB

API Declarations


Dim con1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset



Rate Getting the excel file in to the grid and store that data in Access DB

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Private Sub Command1_Click()
con1.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\karthik\mydb.mdb;Uid=Admin;Pwd=;")
Set rs1 = New ADODB.Recordset
rs1.Open Source:="grid", ActiveConnection:=con1, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
'grid is the tablename in access
Do While Not rs1.EOF()
rs1.Delete
rs1.MoveNext
Loop

datagrid1.Cols = rs.Fields.Count
Dim increase, row As Integer
increase = rs.Fields.Count - 1
rs.MoveFirst
j = 1
Do While Not rs.EOF
rs1.AddNew
For i = 0 To increase
rs1(i) = datagrid1.TextMatrix(j, i)
Next

rs1(i) = Format(Now, "dd/mm/yyyy")
j = j + 1
rs.MoveNext
Loop

rs1.Update
rs1.Close
con1.Close
Set rs1 = Nothing
Set con1 = Nothing
datagrid1.Rows = 0
End Sub

Private Sub Command2_Click()
End
End Sub

Private Sub html_Click()

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\karthik\testfiles\ipo290406.xls;Extended Properties=""Excel 8.0;HDR=no;IMEX=1""")
Set rs = con.Execute("select * from [Sheet1$]")

i = 1
datagrid1.Rows = 2
datagrid1.Cols = rs.Fields.Count
Do While Not rs.EOF
For j = 0 To rs.Fields.Count - 1
    'datagrid1.TextMatrix(0, j) = IIf(IsNull(rs.Fields(j).Name), "", rs.Fields(j).Name)
    If j = rs.Fields.Count - 1 Then
        On Error Resume Next
            datagrid1.TextMatrix(i, j) = IIf(IsDate(CDate(rs.Fields(j).Value)), Format(CDate(rs.Fields(j).Value), "dd/mm/yyyy"), "")
            
    Else
        datagrid1.TextMatrix(i, j) = IIf(IsNull(rs.Fields(j).Value), "", rs.Fields(j).Value)
    End If
    
Next
i = i + 1
If datagrid1.Rows <= i Then
    datagrid1.Rows = datagrid1.Rows + 1
End If
rs.MoveNext
Loop
datagrid1.Rows = datagrid1.Rows - 1
datagrid1.AllowUserResizing = flexResizeBoth
End Sub