automatically run a macro everyday

[Kam]

Dear all,

I am going for vacation in two days. I need to run my VBA code automatically.

Everyday, I get one excel workbook by using another macro. This workbook
contains some values.

I have written a vba code in Excel_Macro.xls which does the work for me.
This vba code generates Pivot table in sheets in to Excel_Macro.xls.

Script In Excel_Macro.xls
Sheets("Pivot_Table").Select

With Worksheets("Pivot_Table")

Range("A:K").Select
With Selection
Cells.Clear
End With

Dim PT_9_Cache As PivotCache, PT_9 As PivotTable
Set PT_9_Cache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="Temp_Data!A:AK")
Set PT_9 =
PT_9_Cache.CreatePivotTable(TableDestination:="Pivot_Table!R1C1",
TableName:="FACT")
With PT_9
.PivotFields(1).Orientation = xlRowField 'Department
.PivotFields(2).Orientation = xlRowField 'Status
.PivotFields(3).Orientation = xlDataField 'SeqNo
End With
With ActiveSheet.PivotTables("FACT").PivotFields("Department")
.Orientation = xlRowField
.Position = 1
End With
Range("A2").Select
ActiveSheet.PivotTables("FACT").PivotFields("Count of
SeqNo").Function = xlCount
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B2").Select
ActiveSheet.PivotTables("FACT").PivotFields("Status").Subtotals = _
Array(False, False, False, False, False, False, False, False, False,
False, False, False)
Range("A2").Select
With ActiveSheet.PivotTables("FACT")
.ColumnGrand = False
.RowGrand = False
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
End With

Range("A:K").Select
With Selection
.Font.Size = 10
.Font.Name = "Verdana"
End With

Selection.Columns.AutoFit

End Sub

Once this script is done I want macro to send it by email to sender list.Can
this be done...I would appreciate your help in this Matter.

Best Regards,
Kam.

 

[Dave Peterson]

First, you're going to have to leave your pc on. Have some kind of scheduler
program that starts excel and your macro workbook (lower your security settings
to allow macros to run without prompts) and modify your macro to send the
emails.

Ron de Bruin has lots of email code samples here.
http://www.rondebruin.nl/tips.htm
(look for mail)

Personally, I don't think I could get all this stuff up and running in 2 days.

Instead, I'd find a trusted co-worker and ask them to do the work for me. If I
was the only one who has access to the files/data, I'd share my id's and
passwords with him/her. Then I'd type up some instructions on what to do.

And for the next day (or so) sit with them while you let them do the work. Try
not to tell them what to do next, but make notes on your instructions to
eliminate the troublesome areas.

 

[Kam]

Hello,

Sorry for chasing..I am going on holiday from Tomorrow..It would be great if
any can guide me or give me the solution to below request.

Best Regards,
Kam.

 

[Gord Dibben]

Will Excel be running at all times?

Will the workbook with the macros be open at all times?

A macro can be run at a certain time using the OnTime method but Excel and
the workbook must be open.

Alternative..................

Windows Task Scheduler to start Excel at a given time with the workbook to
open and Workbook_Open code to do the macro work is one method.

You would not have to use OnTime method.


Gord Dibben MS Excel MVP

 

[Kam]

Hi,

Thanks for your reply..Yes I can keep Excel open & macro file as well.

This seems to me good idea...I would like to run a macro weekly except
Saturday & Sunday...

Best Regards,
Kam.

 

[Kam]

Hello,

any luck with the below.

Best Regards,
Kam

 

[Gord Dibben]

Why aren't you on Vacation?

I would use Task scheduler to open the workbook when scheduled rather than
goof around with a whole bunch of OnTime coding which excludes the weekend
dates.

I have no clue how to code OnTime to miss certain days.

But Chip Pearson has some sample OnTime code at his site.

http://www.cpearson.com/excel/OnTime.aspx

Maybe you can cobble something together.

For Task scheduler........................

Have your code in Thisworkbook module and use Workbook_Open event.

For the emailing part see Ron de Bruin's site for code.

http://www.rondebruin.nl/sendmail.htm


Gord