disable/re-enable Workbook_Open() and howto save as Text()today

alienscript · #1 (**permalink**) 10-07-04, 11:40

Hi VB expert,

In my Excel 2002 template (let's say template.xls) I have a macro that auto-populates the required cells. I name the macro as MacroStockingUpdate in the Standard Module.

This Excel 2002 template has an Auto-Start macro in ThisWorkbook as below:

Private Sub Workbook_Open()
Call MacroStockingUpdate
End Sub

After the auto-execution tasks are done, I save it as StockingStatus.xls.
My problem is that when the viewers open StockingStatus.xls, it also load Workbook_Open() and I do not want that.

(1) what VB code would disable the Workbook_Open() after I saved it as StockingStatus.xls ?
(2) what VB code would enable again the Workbook_Open() when I saved it back as template.xls after erasing the populated data ?
(3) what VB code to allow me to save the worksheet as "StockingStatus" with System ()today's date (eg. StockingStatus_10-Oct-2004, or any other date format as long as it's today) ?

Thanks in advance.

savbill · #2 (**permalink**) 10-08-04, 09:49

Quote:

Originally Posted by alienscript

Hi VB expert,

(1) what VB code would disable the Workbook_Open() after I saved it as StockingStatus.xls ?
(2) what VB code would enable again the Workbook_Open() when I saved it back as template.xls after erasing the populated data ?
(3) what VB code to allow me to save the worksheet as "StockingStatus" with System ()today's date (eg. StockingStatus_10-Oct-2004, or any other date format as long as it's today) ?

Thanks in advance.

Here's some examples to do what you asked.

Code:

' (1) and (2)

Private Sub Workbook_Open()
' Put this line in your workbook_open
' Event.

If Not ThisWorkbook.Name = "template.xls" Then
    Exit Sub
End If
End Sub

' (3)
Sub SaveWorkbook()

   strAppend = Format(Date, "DD-MMM-YYYY")
   strPath = "C:\Documents and Settings\Owner\My Documents\"
   fSaveName = strPath & "StockingStatus_" & strAppend

   ThisWorkbook.SaveAs fSaveName
End Sub

alienscript · #3 (**permalink**) 10-12-04, 11:46

It works great !
Thanks so much.

Internet Services

Web Development

Digital Marketing

Consumer Tech