Importing Excel File and Comparing Tables in VBA

[riggsd]

I have two questions about tables.

1. I have a report generated from a SQL database that is being saved to the user's D: drive. The report is run from within Access, which calls the report using information the user entered in the Access form.

Once the report is saved as an Excel file, how do I import that file into Access using VBA? Because I have multiple users, the table has to be a temporary one, prefixed with the user's userid, which I'm getting using ENVIRON, followed by the Excel filename, i.e., riggsd_BSR_365_Maturity_Report.

2. Next, I need to run a query from the Access database, save it to another temporary table, prefixed with the user's userid, i.e., riggsd_Maturity_Report, then compare the two tables. The result of the compare needs to be exported to an Excel file and the temporary tables deleted. How do I do this in VBA?

Thanks in advance.

 

[ezakovich_35]

DoCmd.TransferSpreadsheet

Look at the DoCmd.TransferSpreadsheet command in VBA. It's for importing or exporting and you can specify the table name for import. Are the queries you need to run static or dynamic? I.e. are they already built and will look for your imported table names? Also, if your tables are temporary, within VBA you can use the DoCmd.DeleteObject command to get rid of them.