Split data with MULTIPLE worksheets within a workbook

lkjcpa

New Member
Joined
Apr 28, 2013
Messages
3
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
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

New Member
Joined
Apr 28, 2013
Messages
3
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,634
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top