[Webinar] Streamline your web hosting managementRegister Today


read Excel file

Posted on 2000-01-04
Medium Priority
Last Modified: 2010-05-02
I want to read an Excel file (given a cell range) to an Access table. Can somebody help me with that?

Thank you very much.
Question by:guosong

Accepted Solution

dhwanilshah earned 300 total points
ID: 2324764
'here is an example to help u out

'first refrence the excel library into the vb or access project
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

Set xlapp = GetObject(, "Excel.Application")
If xlapp Is Nothing Then
    Set xlapp = New Excel.Application
End If

xlapp.Visible = True

xlapp.Workbooks.Open ("C:\WINDOWS\Profiles\Shreyas\Desktop\Case Register\dees_Mact_Cases.xls")

Set datasheet = xlapp.ActiveWorkbook.Sheets("data sheet")

'here u can connect to another database also, it is just an connection obj. I made in access so i have used this statement
Set curdb = CurrentProject.Connection

'open the destination recordset
rsCases.Open "cases", curdb, adOpenDynamic, adLockPessimistic

'specify from where do u want to start
'i.e the first row where the data starts
mRow = 2

'spcify upto where it should continue reading data
Do While mRow <> 319

'read data into respective colums    
    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)
    Debug.Print mRow
    mRow = mRow + 1

Exit Sub

    If Err.Number = 429 Then
        Resume Next
        MsgBox Err.Description, vbInformation, "Error - " & Err.Number
    End If

End Sub

'this proc. was written by me to migrate data from an excel file to the access db, it was never intended to be a generic app. so it is a little crude but it sure did the wrok for me.

Author Comment

ID: 2326469

I cannot get this segment code to work.

I got a compile error for the statement:

Set xlapp = GetObject(, "Excel.Application")

also, what is the "data sheet" in statement:

 Set datasheet =xlapp.ActiveWorkbook.Sheets("data sheet")

The excel files I want to read vary from file to file in terms of number of rows. How can I detect the end of the record when it is being read?

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question