Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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 wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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. "Kam" wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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 On Wed, 9 Dec 2009 03:28:01 -0800, Kam wrote: 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. "Kam" wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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. "Gord Dibben" wrote: 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 On Wed, 9 Dec 2009 03:28:01 -0800, Kam wrote: 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. "Kam" wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
Hello,
any luck with the below. Best Regards, Kam "Kam" wrote: 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. "Gord Dibben" wrote: 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 On Wed, 9 Dec 2009 03:28:01 -0800, Kam wrote: 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. "Kam" wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically run a macro everyday
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 On Sat, 12 Dec 2009 06:45:01 -0800, Kam wrote: Hello, any luck with the below. Best Regards, Kam "Kam" wrote: 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. "Gord Dibben" wrote: 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 On Wed, 9 Dec 2009 03:28:01 -0800, Kam wrote: 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. "Kam" wrote: 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:=xlDatab ase, SourceData:="Temp_Data!A:AK") Set PT_9 = PT_9_Cache.CreatePivotTable(TableDestination:="Piv ot_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("Depar tment") .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("Statu s").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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auditing excel for everyday tasks | Excel Worksheet Functions | |||
Auditing excel sheets for everyday task | Excel Worksheet Functions | |||
Display Date that does not change everyday. | Excel Discussion (Misc queries) | |||
Unique names in a list reported everyday | Excel Worksheet Functions | |||
Formula help ... moving to next cell everyday | Excel Worksheet Functions |