Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Question Unanswered: Help!!! Importing Excel data ---> Access

    I'm developing a database for my finance department, and it's more involved than anything else I've done... I'm a little overwhelmed, and I need some help!!! Here's the scenario

    - Every week, I need to be able to run reports (pivot tables) in our data warehouse and export them to Excel (I can't export them directly to Access)

    - I need to be able to import the data to Access in as automated a process as possible

    Here are the problems I'm having:

    - Since the reports are formatted as pivot tables, they don't import correctly into Access

    - When the data is imported, it only has data for the current months... Jan-Sep, then a total.

    The problem is, when we get data for the next month (say Oct 1), a new column is added for it, and I don't know how to set up the table in Access to allow for that.

    So.... do I have to create a macro in Excel to format the data correctly before importing it into Access? Or is there a more automated and quicker way of doing this, since it needs to be done each week?

    Thanks in advance for any and all help!!

  2. #2
    Join Date
    Aug 2004
    A possible solution could involve writing a macro in Excel.
    Produce another worksheet by copying the relevent cells from the pivot table so that you eliminate the information indicating a pivot table.
    Then import the new worksheet into Access.

    This is a suggestion that I have not tried but I use it to combine data from pivot tables in 50+ Excel files.

  3. #3
    Join Date
    Jul 2004
    Thanks for your suggestion. Anybody have any ideas as to how I can format the table that I import the data to? (see orig post) Is the only way to accomplish this by creating a macro in Excel to add the necessary columns (fields) with no data so that it will "fit" into the Access 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