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

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