Author : Karthick @ The Hindu
Date Submitted : 5/7/2006
Category : Database
Compatibility : VB 6,VB 5
This code has been accessed 12219 times.
Task : Getting the excel file in to the grid and store that data in Access DB
Declarations
Code
//here iam using the Msflex grid with the name datagrid1
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