Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you. That looks helpful. Andy.

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by andrewgibsonsw View Post
    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.
    Regards
    Don

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    .. 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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Francois

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •