Auto refresh and email

[akmalkhan]

Hi Gurus,


I have a challenging requirement in my work and want your expert advice on this.


I have a report that I need to run every one hour to check on the team performance on an hourly basis.

Basically, i have a excel file that is connected to the CRM database using a ODBC connection that is set to refresh the data dump every 1 Hr( Sheet name= Data Dump). I have a pivot table in another sheet ( sheet name =pivot) that gives me the view that i need to monitor.


the requirement is to

-Refresh the dump every 1 Hr ( managed to do this by using the refresh every 60 mins feature in the data connection settings)


- refresh the pivot after dump refresh( not accomplished )


- send an email of the pivot to abc@xyz.com every one hour after the pivot is refreshed.( not accomplished)


please share your expert advice on is this requirement can be managed through a macro.


Regards,

Ak

 

[DaveTurton]

The below will refresh your pivot table every hour


If for example your macro is called "Refresh_Pivot_Table" and your Pivot Table is called "My_Pivot_Table" then


In Thisworkbook module add the following

[pre]

Private Sub Workbook_Open()
Call Refresh_Pivot_Table
End Sub

Then in a new module:


Sub Refresh_Pivot_Table()
Application.OnTime Now + TimeValue("01:00"), "Refresh_Pivot_Table"
ActiveSheet.PivotTables("My_Pivot_Table").RefreshTable
End Sub

[/pre]
With regards to emailing it, are you wanting to email the workbook or a picture of the pivot table?

 

[DaveTurton]

If you are wanting to send the active workbook, a copy of the workbook or an individual sheet by email please see the below:


http://msdn.microsoft.com/en-us/library/office/ff458119(v=office.11).aspx

 

[akmalkhan]

Thanks for the inputs Dave.. I will need to mail in the values from the pivot. without the data link to the dump sheet. ( similar to copy-paste only values)

 

[DaveTurton]

try this:


as before:


In Thisworkbook module add the following

[pre]

Private Sub Workbook_Open()
Call Refresh_Pivot_Table
End Sub

Then in a new module:

Sub Refresh_Pivot_Table()

'refresh table

Application.OnTime Now + TimeValue("01:00"), "Refresh_Pivot_Table"
ActiveSheet.PivotTables("My_Pivot_Table").RefreshTable

'send email

ActiveSheet.PivotTables("My_Pivot_Table").TableRange2.Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = "Current Pivot Table Data"
.Item.To = "abc@xyz.com"
.Item.Subject = Pivot Table"
.Item.Send
End With

End Sub

[/pre]

 

[akmalkhan]

Hi Dave.

Made some minor changes and it works like a charm.


Application.OnTime Now + TimeValue("01:00") is changed to Application.OnTime Now + TimeValue("01:00:00") ' to run the macro every hour .


Thanks Much!