CODEWITHSUNDEEP
excelvbacsvms-accesspowerpivot
Oggy
Oggy

Reputation: 31

Emailed Excel Spreadsheet to Multiple CSV Files

Scenario: I receive a daily Excel spreadsheet in Outlook. It contains numerous rows of customer data. Each day, I must append the new spreadsheet records to a master spreadsheet, then create multiple pivot tables in both the master spreadsheet and the new spreadsheet in order to get statistics from different columns to input into multiple CSV files which each supply data to a business intelligence dashboard widget I connect to through DropBox.

Data can also be supplied to the business intelligence dashboard (Geckoboard.com) using Google Sheets, but CSV seems to be the best data format for this given the data originally comes from Excel.

I want to automate this process as much as possible. I've posed a similar question recently and it seems I would have to use VBA to trigger an Outlook macro based on an email subject heading, then within this macro kick off macros in Excel to create the PivotTables and resulting CSVs which are then overwritten each day in my DropBox folder.

But is there a better way to do this? I'm looking into PowerPivot and Access as potential solutions. Maybe Pivot Tables are not the right way to go either. I currently use it to get things like counts of each row label or counts of customers who meet a certain condition.

If you would like clarification, let me know. Thank you for reading and contemplating my question.

Upvotes: 0

Views: 121

Answers (2)

rwilson
rwilson

Reputation: 2135

Once you find a way to automate Outlook and pull your Excel attachment effectively, you definitely want to look into Power Query (A free Addin for Excel 2010/2013 and built into Excel 2016).

Power Query gives you an easy way to establish connections to different data sources, in which you can then transform the data (Insert Columns, perform aggregations, etc) and then load it into Excel or Power Pivot if you choose to go down that route. Once you perform the task once, it will automate all your steps when you refresh.

For example, you can set up a connection to a folder that will append all Excel files in the folder to a master file. So each time an Excel file gets dropped into that folder, all you need to do is refresh the master file.

There are a lot of other awesome things Power Query can do. I recommend checking out some of Mike Girvin's (The most enthusiastic Excel teacher of all time) videos. He has a whole video series on Power Query. The link below is to the video where he walks you through connecting to a folder:

https://www.youtube.com/watch?v=a7E29H5ZUmE

Chris Webb also blogs about Power Query pretty often on his site and has written a book on it:

http://blog.crossjoin.co.uk/category/power-query/

Upvotes: 1

Rdster
Rdster

Reputation: 1872

You can certainly do it that way. I used to have an Outlook macro that would trigger when my morning data dumps would come in, save the data file and then open Excel and launch VBA code that opened the master file & the data file...copied over the data, either refreshed or deleted and recreated the pivot tables, formatted everything, and then either saved files, or created emails with the reports ready to go...I just had to review them. Cut my work day down to only a couple of hours of work. I had just about everything automated that ran on a schedule.

It took time to get all the macros working correctly and consistently, but once they did...good times!

"A better way" may be subjective...does better mean faster coding time, faster processing, cheaper? Its hard to beat using MS Office products generally as most everyone already has them...VBA is well documented and fairly easy to learn.

Upvotes: 1

Related Questions