Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import excel datasheet (Access 2000)

    Is there any formula with which to import an Excel datasheet amd comvert it into a table?
    The Excel file is named BPreise and has the following columns:

    Artikel-Nr Spund-Produkt DDU Neu DDU


    I want to make a table with the same titles and fields.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import excel datasheet (Access 2000)

    Thank for thr reply Your code :
    DoCmd.TransferSpreadsheet TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel8, _
    TableNameL="tblSomething", FileName:="F:ExcelBPreise.xls", _
    HasFieldNames:=True

    Gives me red leters and Access does not acceptit.May be there is something small that makes the letters red.
    Your code will createa new table where the first field is called F1 and the second F2.
    i want the first field to be called

    "Code" and the second field "grade".Also

    i want the first field to be a number and not txt and to have a primary key.

    Can it be done in one and same line ?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import excel datasheet (Access 2000)

    Typo corrected by HansV

    You can import an Excel worksheet by selecting File | Get External Data | Import... and selecting Microsoft Excel (*.xls) from the Files of type dropdown list.
    You can link an Excel worksheet by selecting File | Get External Data | Link Tables... and selecting Microsoft Excel (*.xls) from the Files of type dropdown list.

    In VBA code, you can use DoCmd.Transferspreadsheet:

    DoCmd.TransferSpreadsheet TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel8, _
    TableName:="tblSomething", FileName:="F:ExcelBPreise.xls", _
    HasFieldNames:=True

    If you want to link the table instead of importing it, use acLink instead of acImport. The first worksheet will be imported, and the data should begin in cell A1, unless you also specify the Range argument.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import excel datasheet (Access 2000)

    There was a mistake in my original code. <code>TableNameL="..."</code> should be <code>TableName:="..."</code>.

    Your Excel worksheet should have a first row containing the field names Code and Grade.

    You can create an empty table tblSomething in Access with the correct field names and types, and with a primary key on Code. The code will import the data into this table.

Posting Permissions

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