Split data with MULTIPLE worksheets within a workbook

[lkjcpa]

I am working with Access/Excel 2010 and would like to automate as much as possible the following (or eliminate steps not needed):

1. In Access:
   1. 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.)
   2. Run Query 1 - Raw Data from the .txt file minus lines that meet defined criteria
   3. Run Query 2 - Summarized Data (This query is unique combos from Raw data based on select fields)
2. In Excel:
   1. Tab 2 - has data connection to query 2 - so easily refreshed once file is opened and user clicks "data" / "refresh all"
   2. Tab 3 - has data connection to query 1 - refreshed as noted above
   3. 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.
   4. Load file to Sharepoint
3. In Outlook:
   1. 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.
4. 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.

 

[sergioMabres]

All you ask, that is a lot, can be done, it almost a day's work, with coding, testing, and recoding. Even more you can do all from excel you do not need to open Access or Outlook, you can send mails from Excel. But as you realize you are asking too much. You should ask by little pieces or find a Excel consulting guy to do it for you.

 

[lkjcpa]

I agree, but was afraid if i post in bits and pieces, then I would've missed some obvious shortcut like you are suggesting here, which is to do all of this within excel. From what I know myself, I could create a macro within Access to run separate queries and create separate workbooks initially, and then come back in excel and run a macro which would combine each group of files into workbooks by region, and then as part of that macro also email the combined files out to a distribution list. Also, I'm not tied to posting this on Sharepoint or including the screenprint in the email body, so am flexible in how this gets delivered to regional owners, but those would be nice to have.

All you ask, that is a lot, can be done, it almost a day's work, with coding, testing, and recoding. Even more you can do all from excel you do not need to open Access or Outlook, you can send mails from Excel. But as you realize you are asking too much. You should ask by little pieces or find a Excel consulting guy to do it for you.

 

[sergioMabres]

For instance here is a thread about sending email http://www.mrexcel.com/forum/excel-...le-emails-independent-values.html#post3451279

 

[lkjcpa]

Tks, I will check this out for the email component.