Excel export to Access

I need to export data on my excel sheet1.
Sheet1 is a hidden worksheet.
I want to create a command button in my sheet2 that will export
data on sheet1 to an existing Access database table.
I made it so that field name on my sheet1 is the same as the Access table
being
imported to.
Can someone help me with the code?

Here is the code.
Please add a reference to Microsoft ActiveX Data Object

Dim con as new ADODB.Connection
Dim strcon as String
Dim strSQL as String

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
strcon = strcon & "Data Source=C:\MWCI\Database\Dbase.mdb;"
strcon = strcon & Persist Security Info=False"

con.Open strcon

strSQL = "INSERT INTO TABLE1 "
strSQL = strSQL & "SELECT * FROM [Excel
8.0;Database=C:\book1.xls].[Sheet1$]"

con.Execute strSQL

set con = nothing


replace Table1 with the name of your table
in MsAccess same with the
Database=C:\book1.xls
Data Source=C:\MWCI\Database\Dbase.mdb
Sheet1

Hope this will help u.

Arthur

Thank you Arthur.

I've tried this, but could not get it to work.
I really do not know much about codes, although I'm trying to learn and
figure this out.

Would it be possible to explain like you would explain to a dummy?


"[email protected]" wrote:

> Here is the code.
> Please add a reference to Microsoft ActiveX Data Object
>
> Dim con as new ADODB.Connection
> Dim strcon as String
> Dim strSQL as String
>
> strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
> strcon = strcon & "Data Source=C:\MWCI\Database\Dbase.mdb;"
> strcon = strcon & Persist Security Info=False"
>
> con.Open strcon
>
> strSQL = "INSERT INTO TABLE1 "
> strSQL = strSQL & "SELECT * FROM [Excel
> 8.0;Database=C:\book1.xls].[Sheet1$]"
>
> con.Execute strSQL
>
> set con = nothing
>
>
> replace Table1 with the name of your table
> in MsAccess same with the
> Database=C:\book1.xls
> Data Source=C:\MWCI\Database\Dbase.mdb
> Sheet1
>
> Hope this will help u.
>
> Arthur
>
>

Goto Views>Toolbars>Control Toolbox then
In sheet2 of your workbook
add a command button then double click that button.
You will be directed to Visual basic editor.

Copy and paste this code inside the "Private Sub
CommandButton1_Click()"

Dim con As New ADODB.Connection
Dim strcon As String
Dim strSQL As String


strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
strcon = strcon & "Data Source=C:\db1.mdb;"
strcon = strcon & "Persist Security Info=False"


con.Open strcon


strSQL = "INSERT INTO TABLE1 "
strSQL = strSQL & "SELECT * FROM "
strSQL = strSQL & "[Excel 8.0;Database=C:\book1.xls].[Sheet1$]"


con.Execute strSQL


Be sure to replace the
Database=C:\book1.xls,
Data Source=C:\db1.mdb
TABLE1

Finally, RUN the code by pressing F5

Arthur,

Thank you.
I tried it but am still getting an error message:

Run-time error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.

And in the vba screen, con.Execute strSQL is hi-lighted yellow with a
yellow arrow to it.

I'm sorry to be a pain, hope you will help me.

Thank you

"[email protected]" wrote:

>
> Goto Views>Toolbars>Control Toolbox then
> In sheet2 of your workbook
> add a command button then double click that button.
> You will be directed to Visual basic editor.
>
> Copy and paste this code inside the "Private Sub
> CommandButton1_Click()"
>
> Dim con As New ADODB.Connection
> Dim strcon As String
> Dim strSQL As String
>
>
> strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
> strcon = strcon & "Data Source=C:\db1.mdb;"
> strcon = strcon & "Persist Security Info=False"
>
>
> con.Open strcon
>
>
> strSQL = "INSERT INTO TABLE1 "
> strSQL = strSQL & "SELECT * FROM "
> strSQL = strSQL & "[Excel 8.0;Database=C:\book1.xls].[Sheet1$]"
>
>
> con.Execute strSQL
>
>
> Be sure to replace the
> Database=C:\book1.xls,
> Data Source=C:\db1.mdb
> TABLE1
>
> Finally, RUN the code by pressing F5
>
>

please download this sample files

http://www.sharebigfile.com/download.php?id=23EFC5A2

extract that zip file to C:\ directory


About the error you encounter, i guess it has something to do with
the field type of the access table. Maybe you are saving a string to
integer type field.