Use VBA to import and export tables from Excel to access

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.