Unanswered: Importing Excel into Access every month and run reports??
I have a requirement. My company generates Excel datafiles every month. I need to automatically load this Excel into Access database. (Populate one table with the Excel data). Then I need to run some reports based on this table. How can I schedule an automatic import from an Excel file on the network into an Access table every month?
Then, how do I write a report based on different tables each month? That is, next month's datafile will be loaded into a different table from the current month's table. But my report should be able to run based on the new table. Can I create a report based on a dynamic table name or query?
All you have to do is link the excel file from access via the linking manager. You can have different locations for the different months in case you need to keep the old excel files, or you can rename the by month. Hope this is of help.