Seeking Solutions to Automate Excel Weekly Report

**Helal** · 03-14-2014, 06:49 PM

I have a report that I need to update it on weekly basis. The report contains the raw data and bunch of Tables (Sumifs) and Pivot ones that are being fed by raw data. The current process is to extract data from SQL server and copy records to raw data tab, expand the fields (Months and formulas) to capture current month data. I like to be able to automate the process as much as possible. I did try using MS SQL Query in Excel with no avail and was a pain. I am using Excel 2010 and the SQL backend version is 2008R2. I appreciate any suggestions to help automate this process.

Thank You,

Helal

**AlKey** · 03-14-2014, 07:46 PM

Hi Hetal,

I think you could make the process much easier. I personally like to use MS Access to write queries and the export them into a spreadsheet. Then I use another sheet of the same workbook to which data was exported to create my report. In this sheet I wright formulas, use Pivot tables and Conditional Formatting (if necessary) to complete my report. That way every week or month my reports look the same all is changes is the data. You can easily automate MS Access to import/export data and this makes the entire process much faster. Even complex reports can be done in few minutes.

Hope this helps.

**Helal** · 03-15-2014, 01:57 PM

Hi Alkey,
Thanks for sharing your solution with me. If I understood correctly, your query in Access extract data from Database. You then, COPY that data to your Excel data sheet. Or do you link your Excel data sheet to Access Query? also, since my data are trend data by weeks, any suggestion as how to streamline different tabs on my report after data has been exported (either by just copying or linking it to Access query). If it helps, I can give a sample of the report.

Thanks for your help,

Helal

**AlKey** · 03-15-2014, 02:14 PM

You don't need to copy query to excel, you export query to excel. You can setup macro in Access or do with VB. When you export data to the spreadsheet, the old data will be replace with new one. You can also link excel directly to Access. This process has it's own advantages as the data in Excel will appear formatted as table and will expand or contract depending on the size. When exporting, you can export multiple queries to the same workbook and they will appear on different tabs and tabs will be labeled with query name. Additional information on how to do this just use Google and you will find all the answers.