Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable WorkBook AutoOpen
Repost... Thanks
I'm automating a report which pulls in data from a SQL Server... I've set up scheduler to run the report with the Autoopen.. Steps 1. Backup report 2. Refresh Data 3. Saves 4. Copy and paste values 5. Delete Supporting pivot tables 6. Saves a Copy to Drive in Final Folder It works perfect.. Refreshes the data and places on the drive.. However, the final report labled "report.mm.dd.yyyy" still attempts to run macro when opened.. Obviously it doesn't run because it fails on delete comand.. What can I add on the final step Save As to make sure the AutoOpen doesn't run on the Final Report.. Code is below.. Thanks Private Sub Workbook_Open() Dim FileName As String Dim Wkb As Workbook Dim WS As Worksheet Dim bdFileName As String Dim FullFileName As String Application.DisplayAlerts = False Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic FullFileName = ActiveWorkbook.FullName bdFileName = Left(ActiveWorkbook.Name, _ Len(ActiveWorkbook.Name) - 4) ActiveWorkbook.SaveCopyAs FileName:="G:\CommandCenterReport\Daily Reports\MTD STATS\FINAL\Backup\" & _ "BACK_UP_" & bdFileName & Format(Now, "_YYYY_MM-DD_H-MM-SS") & _ ".xls" On Error GoTo 0 ActiveWorkbook.RefreshAll ActiveWorkbook.Save Sheets("MTD Stats").Cells.Copy Sheets("MTD Stats").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False On Error GoTo 0 Sheets("Workbench").Delete Sheets("Enterprise Pivot").Delete Application.EnableEvents = False ' open the workbook Application.EnableEvents = True ActiveWorkbook.SaveAs FileName:="G:\CommandCenterReport\Daily Reports\MTD STATS\FINAL\" & _ bdFileName & Format(Now() - 1, "_MM.DD.YYYY") & _ ".xls" Workbooks.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable WorkBook AutoOpen
I can think of a couple of options
1) Test for the workbook name and if it's one thing allow Workbook_Open to execute, otherwise end the sub 2) Read the info here to delete the Workbook_Open sub http://www.cpearson.com/excel/vbe.aspx -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "THE_RAMONES" wrote: Repost... Thanks I'm automating a report which pulls in data from a SQL Server... I've set up scheduler to run the report with the Autoopen.. Steps 1. Backup report 2. Refresh Data 3. Saves 4. Copy and paste values 5. Delete Supporting pivot tables 6. Saves a Copy to Drive in Final Folder It works perfect.. Refreshes the data and places on the drive.. However, the final report labled "report.mm.dd.yyyy" still attempts to run macro when opened.. Obviously it doesn't run because it fails on delete comand.. What can I add on the final step Save As to make sure the AutoOpen doesn't run on the Final Report.. Code is below.. Thanks Private Sub Workbook_Open() Dim FileName As String Dim Wkb As Workbook Dim WS As Worksheet Dim bdFileName As String Dim FullFileName As String Application.DisplayAlerts = False Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic FullFileName = ActiveWorkbook.FullName bdFileName = Left(ActiveWorkbook.Name, _ Len(ActiveWorkbook.Name) - 4) ActiveWorkbook.SaveCopyAs FileName:="G:\CommandCenterReport\Daily Reports\MTD STATS\FINAL\Backup\" & _ "BACK_UP_" & bdFileName & Format(Now, "_YYYY_MM-DD_H-MM-SS") & _ ".xls" On Error GoTo 0 ActiveWorkbook.RefreshAll ActiveWorkbook.Save Sheets("MTD Stats").Cells.Copy Sheets("MTD Stats").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False On Error GoTo 0 Sheets("Workbench").Delete Sheets("Enterprise Pivot").Delete Application.EnableEvents = False ' open the workbook Application.EnableEvents = True ActiveWorkbook.SaveAs FileName:="G:\CommandCenterReport\Daily Reports\MTD STATS\FINAL\" & _ bdFileName & Format(Now() - 1, "_MM.DD.YYYY") & _ ".xls" Workbooks.Close End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoOpen in excel? | Excel Programming | |||
AutoOpen | Excel Programming | |||
How to disable autoopen() programmatically | Excel Programming | |||
Disable AutoOpen macro's | Excel Programming |