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?
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.
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?