I am working with Access/Excel 2010 and would like to automate as much as possible the following (or eliminate steps not needed):
- In Access:
- Import a .txt file (a saved import is used to do this, but have to browse/select file each time since name changes each day because of date stamp in end of filename.)
- Run Query 1 - Raw Data from the .txt file minus lines that meet defined criteria
- Run Query 2 - Summarized Data (This query is unique combos from Raw data based on select fields)
- In Excel:
- Tab 2 - has data connection to query 2 - so easily refreshed once file is opened and user clicks "data" / "refresh all"
- Tab 3 - has data connection to query 1 - refreshed as noted above
- Tab 1 - has a dashboard with countif/sumif formulas driven by the Summary data now in Tab2. I'm not using a pivot since I want a placeholder for all possible summary combos, even when none exist at that particular time. I can also create this view within the original access database if it would help.
- Load file to Sharepoint
- In Outlook:
- Email link to consolidated file out to all regional owners with dashboard view pasted as pic in body of email so owners can see status from email without opening file on Sharepoint, but they can go to file to see details, if needed.
- Questions - Is there a way to quickly split the file into 4 separate workbooks by region, where each of those would have the three worksheets? I've seen macros to split one worksheet into multiple workbooks, but not one that does so for three worksheets.
- Do you have any suggestions on distributing the file that would save time? (Is there a macro to generate emails for each of these files to a distribution list? And this is asking a lot, but could I include the screenprint for a cell range within the body of the email?)
- Also, I'm really not sure if I should be trying to do more with this in Access or excel. I think I could also approach this by creating an excel file with multiple tabs from access data. I have the access data available, just not sure how to automate the creation of an excel file with multiple worksheets in it.