Unanswered: Exporting parts of an Access 2007 table to Excel - Macro or VB required?
I'm still learning Access 2007 and have a work project that I could use some help with. I have a table, RA Data, that has 200,000 rows of data. One column in this table is called City. There are 258 unique cities. I have another table called List of Cities that list the 258 cities.
I need to find a way to export the data for each city into its own Excel spreadsheet. I know there is a way to write a macro that will find all the data with a city of My Town and export it into an Excel file and name it My Town.xlsx, then look for data with a city of Mayberry and export it into an Excel file and name it Mayberry.xlxs, etc, etc, until I've reached the end. I need the header row included with each file. But I don't know how to do this. And to be clear, I need all the data associated with each row in the table to be brought over into Excel. Essentially, I'm creating a file for each city so they can access a shared drive, find their file, and do with it whatever he/she needs to do. However, putting the Access db onto the shared drive is not an option.
If you want to help me get even fancier, I'd like to put each file into a folder based on State (another column within the RA Data table), but I can manually do that if needed once complete. I just don't want to have to manually create 258 Excel files.
I attached a Test file that contains an Access db with 2 tables similar to what I'm working with. However, I can't post the data I'm working with so my example is very generic, but I'm hoping it will help.