Export to Access

Topic

New Reply

Hello. (Excel and Access 2003)
Has anyone an example of a procedure that exports an Excel list to a (new) Access table?
I want to do this without using Access’s DoCmd.TransferSpreadsheet if possible.
Thanks, Andy.

Reply | Quote

Replies

Have a look at the procedure at Export data from Excel to Access (ADO) using VBA in Microsoft Excel

Reply | Quote

Thank you. That looks helpful. Andy.

Reply | Quote

Hello. (Excel and Access 2003)
Has anyone an example of a procedure that exports an Excel list to a (new) Access table?
I want to do this without using Access’s DoCmd.TransferSpreadsheet if possible.
Thanks, Andy.

I was under the impression that a single sheet Excel workbook was treated as an Access table by Access.

Reply | Quote

.. although that procedure adds records to an existing table, rather than creating a new one?
When using code to create a new table, do I have to define each field name and data type – or can
Access assume these? Thanks, Andy.

Reply | Quote

See VBA Tips & Tricks: Create Database with ADO / ADO Create Database for code how to create your table.
You’ll have to create the table and each field. Access can’t assume anything.

Reply | Quote

You can use something like this (depending on version):

Code:

Sub ExportDataFromWorkbookToAccess2003()
   ' Sample demonstrating how to export data from an Excel 97-2003 format workbook to a 2003 format database
   Dim cn As Object, strQuery As String
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.JET.OLEDB.4.0"
      .ConnectionString = "Data Source=C:Test1.mdb;"
      .Open
   End With
   strQuery = "SELECT Header2, Header4, Header5, Header7 INTO tblNewData FROM [Sheet1$] IN 'C:ADO Source.xls' 'Excel 8.0;HDR=Yes;'"

   cn.Execute strQuery
   cn.Close
   Set cn = Nothing
End Sub

Reply | Quote