Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Oklahoma City, Oklahoma, USA
    Thanked 0 Times in 0 Posts

    Importing to a Table from Excel (A2K)

    I have a file that resides in folder CocumentsRostersjrost3.xls.
    I need to copy it into a database table named jrost3 which I emptied. Located in CATAMAR.BE.
    The jrost3.xls is already formatted to match the table, everything is in the correct columns with headers to match the table headers.
    How do I get this to import the jrost.xls file automatically? I now go to File; Import; In an existing table; drop down to the jrost3 table and finish.
    Is this something that can be done with VBA? I have bought some books and they show how to do simple things, trap errors, get focus, etc., but never anything that lists the names of objects, properties, etc., with examples, so I can see what they do.
    I am trying to learn but old age is catching up with me and it isn't as easy as it once was.

    I cannot just link the table because I must manipulate the data in Access and leave it alone in Excel.

    Help Please!

    Jail Administrator Medical

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Importing to a Table from Excel (A2K)

    Use DoCmd.TransferSpreadsheet to import Excel data:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "jrost3", "CocumentsRostersjrost3.xls", True

    The last argument specifies whether the Excel worksheet contains column headers; I gather from your description that it does, so I used True; if the worksheet doesn't contain column headers, use False.

    You can use the above line of code in the OnClick event procedure of a command button on a form, for example - importing will be only one click.

Posting Permissions

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