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?

Excel export to Access
 

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

Excel export to Access
 

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?


" 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

Excel export to Access
 

Goto ViewsToolbarsControl 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

Excel export to Access
 

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

" wrote:


Goto ViewsToolbarsControl 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

Excel export to Access
 

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.