Auto Open + Disable on Final Product
 

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

The final step works however AutoOpen still affects the final product.. Is
there a way to eliminate the code on my final report.. Code Below

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.SaveCopyAs FileName:="G:\CommandCenterReport\Daily
Reports\MTD STATS\FINAL\" & _
bdFileName & Format(Now() - 1, "_MM.DD.YYYY") & _
".xls"

Workbooks.Close

End Sub

Auto Open + Disable on Final Product
 

Create a function that extracts a bool from a text file that determines
whether or not the auto_open event should continue to run it's code.
--
http://www.ExcelHelp.us

888-MY-ETHER ext. 01781474



"THE_RAMONES" wrote:

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

The final step works however AutoOpen still affects the final product.. Is
there a way to eliminate the code on my final report.. Code Below

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.SaveCopyAs FileName:="G:\CommandCenterReport\Daily
Reports\MTD STATS\FINAL\" & _
bdFileName & Format(Now() - 1, "_MM.DD.YYYY") & _
".xls"

Workbooks.Close

End Sub

Auto Open + Disable on Final Product
 

I believe I have to eliminate the code from my final report.. Reason being is
that report template needs to continue running daily and final report should
be static, only to be viewed by the user... Wouldn't the function stop
it(template) from running the following day?

"galimi" wrote:

Create a function that extracts a bool from a text file that determines
whether or not the auto_open event should continue to run it's code.
--
http://www.ExcelHelp.us

888-MY-ETHER ext. 01781474



"THE_RAMONES" wrote:

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

The final step works however AutoOpen still affects the final product.. Is
there a way to eliminate the code on my final report.. Code Below

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.SaveCopyAs FileName:="G:\CommandCenterReport\Daily
Reports\MTD STATS\FINAL\" & _
bdFileName & Format(Now() - 1, "_MM.DD.YYYY") & _
".xls"

Workbooks.Close

End Sub