Link to home
Create AccountLog in
Avatar of prinsbj
prinsbjFlag for Netherlands

asked on

Need to export data from MS Excel 2003 to MS Access 2003 table

I need some help to export data from MS Excel 2003 Workbook to a MS Access 2003 table. I want to click on a button in the menu in my excel Workbook and the it should export some data to my Access DB.

I've tried the code below be I get the Error 13 Tyoe mismatch.

Can somebody help me?

Bart Jan
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
'On Error Resume Next
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("l:\caf\caf_be.mdb")
    ' open the database
    Set rs = db.OpenRecordset("tblCMR-formulier", dbOpenTable)
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            'Print .debug; Range("A" & r).Value
            .Fields("1_Afzender") = Range("A" & r).Value
            '.Fields("FieldName2") = Range("B" & r).Value
            '.Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Open in new window

Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

where does the error occur?
First and foremost thing...

Don't use DAO. Use ADO as DAO is already obsolete :)

The error could be in you defining the Access Field Types. For Example If you have defined any Access Field as Integer and you are trying to save text in it then you will get a Type mismatch error...

What is the Field Type of "1_Afzender"

Sid
Avatar of prinsbj

ASKER

Hello SiddharthRout:,

I'll change it to ADO, But witch Reference do I need for Excel 2003 and Access 2003.

Field Type of "1_Afzender" is text with 255 lenght.

Hope you can help me futher.

Bart Jan
Microsoft Activex Data Object library :)

Sid
Avatar of prinsbj

ASKER

I've change the reference to Microsoft ActiveX Data Object 2.8.

But what do I need to change in the code?

Bart Jan
Avatar of prinsbj

ASKER

Hello Sid,

I've changed to the other sample but now I get the error The component FROM contains a Syntax error.

Bart Jan
Show me the exact code the you are using...

Sid
Avatar of prinsbj

ASKER

Here It the code.

Bart Jan
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=l:\caf\caf_be.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "tblCMR-formulier", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("1_Afzender") = Range("A" & r).Value
            '.Fields("FieldName2") = Range("B" & r).Value
            '.Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

I don't see an error... Which line are you getting an error?

Also which ADO Library have you referenced?

Sid
Avatar of prinsbj

ASKER

How can I see where the fault is?

This is my reference: Microsoft ActiveX Data Object 2.8

Bart Jan
1. Don't you get any error message? And if yes, then which line is it pointing to?

2. Set the reference to 2.6

Sid
Avatar of prinsbj

ASKER

Now I get this error.

Bart Jan
Error.jpg
Sorry I don't understand the language...

Can you upload a sample of you database and the excel file. Let me check it for you...

Sid
Avatar of prinsbj

ASKER

There stays:

Error -2147217900 (8040e14)  runtime.

The information in the database is secure and in Dutch. Could you make a sample db with 1 field text and a excel 2003 workbook?

Bart Jan
Sure...

Give me few minutes... Which MS Office version do you have?

Sid
Avatar of prinsbj

ASKER

2003
Avatar of prinsbj

ASKER

thanks
In fact If you want... create a copy of your database and delete all the data from it and then upload the database.

Sid
Mean while I am writing the code in Excel...

Sid
Avatar of prinsbj

ASKER

Would you make a sample? Because all the text is Dutch.

ok :)

Sid
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of prinsbj

ASKER

Hello Sid,

This works great thank a lot.

Is it also posible to fill in a worksheet name?

Kind regards,

Bart Jan
Where do you want to fill in? in the database or for referring to the range?

If it is the latter then change the code

    Do While Len(Trim(Range("A" & r).Value)) > 0
        With rs
            .AddNew ' create a new record
            .Fields("1_Afzender") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            .Update
        End With
        r = r + 1
    Loop

to this

    Do While Len(Trim(Sheets("Sheet1").Range("A" & r).Value)) > 0
        With Rs
            .AddNew ' create a new record
            .Fields("1_Afzender") = Sheets("Sheet1").Range("A" & r).Value
            .Fields("FieldName2") = Sheets("Sheet1").Range("B" & r).Value
            .Fields("FieldNameN") = Sheets("Sheet1").Range("C" & r).Value
            .Update
        End With
        r = r + 1
    Loop

Open in new window


Sid
Avatar of prinsbj

ASKER

Hello Sid,

It works super!.

Can I also it insert into a query? And if I've a number field a get a error. What kind of type should I use in this code. (CInt)??

Bart Jan
Can I also it insert into a query?

Sorry I didn't get you...

And if I've a number field a get a error. What kind of type should I use in this code. (CInt)??

Yes

Sid
Avatar of prinsbj

ASKER

Sid,

You did a great job.

Bart Jan
Glad to be of help prinsbj :)

Sid