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?
tshuvaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dhwanilshahCommented:
'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

    rsCases.AddNew

    '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)
   
    rsCases.Update
    'just to check on the progress of the code    
    Debug.Print mRow
   
    mRow = mRow + 1
   
Loop

Exit Sub

ErrorH:
    ' 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
    Else
        MsgBox Err.Description, vbInformation, "Error - " & Err.Number
        Stop
        Resume
    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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.