read data from an excel file and write it into access by VB

i am writing my project by visual basic and i need to read data from
a table that is on an ecxcel file and then add it to an
access file that allready excist, each cell from the excel file is important
so my question is how can i do it (and please give me a specific answer
like the code) and allso what will be the best way for me to do that?
Who is Participating?
dhwanilshahConnect With a Mentor Commented:
'add the ado library refrence
'add the excel library refrence
Public Sub TransferData1()
On Error GoTo ErrorH

Dim xlapp As Excel.Application
Dim datasheet As Excel.Worksheet
Dim mRng As Excel.Range

Dim curdb As ADODB.Connection
Dim rsCases As New ADODB.Recordset

Dim mRow As Long

'try to find if excel is already open, if it is open then use that app. otherwise create a new one and use that
Set xlapp = GetObject(, "Excel.Application")
If xlapp Is Nothing Then
    Set xlapp = New Excel.Application
End If

'if at times u want to see whats happeneing behind the scenes...
'xlapp.Visible = True

'open the excel file from which you want to extract the data
xlapp.Workbooks.Open ("C:\mydata\datafile1.xls")

'speficy from which sheet you want to get the data
Set datasheet = xlapp.ActiveWorkbook.Sheets("data sheet")

'i made this code in an access 2000 module, so I have used currentproject.connection to get the currently open database connection from access. but u can easily use any other connection through standart ado code.
Set curdb = CurrentProject.Connection

'open a dynamic recordset based on the table into which u want to insert data
rsCases.Open "cases", curdb, adOpenDynamic, adLockPessimistic

'specify from which row you want to start fetching the data
mRow = 2

'specify in the loop untill where do u want to retrive the data
Do While mRow <> 319


    'get the data from excel columns into ado recordsets
    ' note : i have directly used column no's to specify ado fields, u can easily specify field names there.
    ' note : i think that this is pretty clear : datasheet.Range("a" & mRow) , returns the data in column a2, if       ' mRow=2 ( as it initially is )
    rsCases(0) = datasheet.Range("a" & mRow)
    rsCases(1) = datasheet.Range("b" & mRow)
    rsCases(2) = datasheet.Range("c" & mRow)
    rsCases(3) = datasheet.Range("d" & mRow)
    rsCases(4) = datasheet.Range("e" & mRow)
    rsCases(5) = datasheet.Range("f" & mRow)
    rsCases(6) = datasheet.Range("g" & mRow)
    rsCases(7) = datasheet.Range("h" & mRow)
    rsCases(8) = datasheet.Range("i" & mRow)
    rsCases(9) = datasheet.Range("j" & mRow)
    rsCases(10) = datasheet.Range("k" & mRow)
    rsCases(11) = datasheet.Range("l" & mRow)
    rsCases(12) = datasheet.Range("m" & mRow)
    rsCases(13) = datasheet.Range("n" & mRow)
    rsCases(14) = datasheet.Range("o" & mRow)
    rsCases(15) = datasheet.Range("p" & mRow)
    rsCases(16) = datasheet.Range("q" & mRow)
    rsCases(17) = datasheet.Range("r" & mRow)
    rsCases(18) = datasheet.Range("s" & mRow)
    rsCases(19) = datasheet.Range("t" & mRow)
    rsCases(20) = datasheet.Range("u" & mRow)
    rsCases(21) = datasheet.Range("v" & mRow)
    rsCases(22) = datasheet.Range("w" & mRow)
    rsCases(23) = datasheet.Range("x" & mRow)
    rsCases(24) = datasheet.Range("y" & mRow)
    rsCases(25) = datasheet.Range("z" & mRow)
    rsCases(26) = datasheet.Range("aa" & mRow)
    rsCases(27) = datasheet.Range("ab" & mRow)
    rsCases(28) = datasheet.Range("ac" & mRow)
    rsCases(29) = datasheet.Range("ad" & mRow)
    rsCases(30) = datasheet.Range("ae" & mRow)
    rsCases(31) = datasheet.Range("af" & mRow)
    'just to check on the progress of the code    
    Debug.Print mRow
    mRow = mRow + 1

Exit Sub

    ' this error would occur when no instance of excel is running and u try to get one, so u should
      ' just ignore the error and continue with the code, and create a new instance of the excel app.
    If Err.Number = 429 Then
        Resume Next
        MsgBox Err.Description, vbInformation, "Error - " & Err.Number
    End If

End Sub

' a couple of points to note : I created this code for migrating data from an excel file to an access db. It was an one time run thing only. So if u are looking to import data into access db repetatively then you should look at other options like SQL Servers DTS services, also dont forget that access directly allows for imports of excel files into access dbs. I had to create this code, bec. somehow excel was treating a text data as a number data and then exporting the data in that format only.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.