Use VBA to import and export tables from Excel to access

Source: Internet
Author: User
Option compare Database

Private sub command0_click ()
Msgbox "hello"
End sub

Private sub commandementclick ()
Docmd. BEEP
Docmd. transferspreadsheet acexport, acspreadsheettypeexcel9, "Table1", "C: myexcel", true, "ruix"
End sub

Private sub command2_click ()

'Import an Excel file to a specified table
Docmd. transferspreadsheet acimport, acspreadsheettypeexcel9 ,_
"Employees", currentproject. Path & "est.xls", true

End sub

Private sub command3_click ()
'Export all tables to an Excel file
Exporttabletoonexls currentproject. Path & "empexeg.xls"
End sub


Public Function exporttabletoonexls (byval xlspath as string)

'Here, you can use ADO to retrieve all the table names. Of course, you can use the query system table method.

Dim rstschema as ADODB. recordset
Dim cnn2 as ADODB. Connection
Set cnn2 = currentproject. Connection
Set rstschema = cnn2.openschema (adschematables)
Dim I as long
Do until rstschema. EOF
If rstschema ("table_type") = "table" then
'Table_type can be set to "table" "system table" "Access Table"
'Here we list the information of all user tables, which are not listed in the system table.
For I = 0 to rstschema. Fields. Count-1
Debug. Print rstschema (I). Name & "->" & rstschema. Fields (I). Value
If rstschema (I). Name = "table_name" then
'Use transferspreadsheet to export data. Of course, you can use the in clause to export data.
'Query, how do I query tables in other databases (in Clause instances )?
'Http://access911.net/index.asp? U1 = A & U2 = 71fab21e17dc
Docmd. transferspreadsheet acexport, acspreadsheettypeexcel9, rstschema. Fields (I). Value, xlspath, true
End if
Next
End if
Rstschema. movenext
Loop
Rstschema. Close
Cnn2.close
End Function


1. Double-click to open my. MDB
2. Click "window"
3. Click "Window 1"
4. Click "ADO export" to export to the same folder "tempexep.xls"
5."“import: Import temp.xls in the same folder to the emploees table.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.