Import Excel data with Access VBA (complex)

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • colintis
    Contributor
    • Mar 2010
    • 255
    #1

    Import Excel data with Access VBA (complex)

    Before asking the question I've been digging a hole that is deep enough to bury myself in google....Most of the results are simply importing the excel file to table with the same table structure.

    What I need to ask, is looking for solution to import the excel data with specific column to specific fields in the tables. For example, I have excel file contains all student information, including their academic results, payments, etc..without separating into multiple worksheets. While in the access database I had split those information into tables, with new field names, ID and so on to setup the relationships.

    My progress so far is able to open the excel file and get its workbook name. But I'm having trouble with importing the data into the tables one by one. Any help is greatly appreciate.
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      1. Why can't you simply Import the Excel data in Normal fashion, and then use a series of Append Queries to push the Data wherever you want?
      2. Link to the Excel Worksheet, then manipulate the Data?
      3. Access the Excel Workbook directly via Automation Code, directly adding to Tables via Recordsets?

        Comment

        • colintis
          Contributor
          • Mar 2010
          • 255
          #3
          The excel file also contains some additional descriptions to some blank cells, which will mislead the database to recognized as part of the actual table data. It is something i want to skip, as there's also blank spaces in between the records and the descriptions.

          Something new from the target user. The target user preferred to remove the excel file once everything have merged into Access, and there's some extra files that required to group all necessary data together in Access. So its breaking down those data into a new set of structure....
          Last edited by colintis; Oct 26 '10, 05:38 AM. Reason: new information from target user

            Comment

            • ADezii
              Recognized Expert Expert
              • Apr 2006
              • 8834
              #4
              How about a Sample Excel File, along with the Descriptions of the Access Tables (with Field Names, Data Types, etc) to which the Data will be pushed. Without a detailed explanation of all Components involved, a possible solution will not be forthcoming.

                Comment

                • colintis
                  Contributor
                  • Mar 2010
                  • 255
                  #5
                  I had found out a method with the link table idea ADezii written in post #2. Basically I use vba to import the excel as a link table into Access, and make 2 recordsets (1 for excel 1 for access's table) to add them into the access table, after that vba will remove the link table.

                  I'm still organising this with more than one Access table to store the link table's data, i'll put the codes up later either it success or not

                    Comment

                    Previous template Next
                    Working...