Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Import Excel Worksheet ( WSheet looks like crosstab query)

    Hi I'm trying to import an Excel worksheet that is set out looking rather like an Access crosstab query or summary report.
    Using import wizard I get a table ( or tables ) which list what is actually a record as a field name

    Worskheet is like this

    ---------CourseTitle1 CourseTitle2 CourseTitle3 and so on

    Staff1---Date Taken----Date Taken--Date Taken
    Staff2---Date Taken----Date Taken--Date Taken
    Staff3---Date Taken----Date Taken--Date Taken
    Staff4---Date Taken----Date Taken--Date Taken


    Access sees column 1 as a field ( Staff names/id ) which is fine but row 1 is seen as separate fields when it is in fact 1 field = Course Title

    In my database Date Taken is a field in a related table with StaffID + CourseTitleID as foreign keys

    What I want is an imported table from the above worksheet that looks like this

    StaffNameField --CourseTitleField---DateTakenField

    Staff1------------CourseTitle1------Date Taken
    Staff1------------CourseTitle2------Date Taken
    Staff1------------CourseTitle3------Date Taken
    Staff2------------CourseTitle1------Date Taken
    Staff2------------CourseTitle2------Date Taken
    Staff2------------CourseTitle3------Date Taken

    Which I can easily amend to fit into my current tables

    Any help please, I have trawled through heaps of forum replies on importing worksheets but can't find a reference to what I thought would be a commonly encountered problem ?
    ( My knowledge of Visual Basic is at a very basic level )

    Thanks
    Keith

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can link the Excel sheet to your application, either using ODBC (linked table) or COM-Automation (to create an instance of Exel and open the .xls file with it), then read the values of the cells and process and format them before inserting them into the table(s).
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Thanks Sinndho, though not sure how that solves my problems, I can get the data into Access but that doesn't 'unscramble' the Excel column headings treated as fields as opposed to records of single field.
    Cheers
    Keith

Posting Permissions

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