I have a program that save in excel automatically. However, I have noticed
that some of these files were opened, changed and saved with the changes
accidentally.
I wonder if there is way to protect the file and kill the macros after the
file is been saved.

Thanks for your help in advance.
Maperalia.

Hi Maperalia,

I wonder if there is way to protect the file

See VBA help for information an example of the Protect method.


... and kill the macros after the file is been saved.

See Chip Pearson's page on VBE programming at:

http://www.cpearson.com/excel/vbe.htm

See particularly the sections entitled:

'Deleting All Code From A Module'

and

'Deleting All VBA Code In A Project '


---
Regards,
Norman



"maperalia" wrote in message
...
I have a program that save in excel automatically. However, I have noticed
that some of these files were opened, changed and saved with the changes
accidentally.
I wonder if there is way to protect the file and kill the macros after the
file is been saved.

Thanks for your help in advance.
Maperalia.

Norman;
Thanks for the web page you sent me. I pick the program:
€œDeleting All VBA Code In A Project". However, when I run it I got this
window message:

"Complie Error
User-Defined type not defined"

Then is highlighting in the following row of the program:

"Dim VBComp As VBIDE.VBComponent"

I when to tool reference to find out what is the available reference I need
to pick up, but I could not figure which one I have to get. I have chosen
"Visual Basic for Application" so far but did not work.

Could you please tell me what reference I have to pick up to avoid this
window message again?..

Thanks in advance.
Maperalia






"Norman Jones" wrote:

Hi Maperalia,

I wonder if there is way to protect the file

See VBA help for information an example of the Protect method.


... and kill the macros after the file is been saved.

See Chip Pearson's page on VBE programming at:

http://www.cpearson.com/excel/vbe.htm

See particularly the sections entitled:

'Deleting All Code From A Module'

and

'Deleting All VBA Code In A Project '


---
Regards,
Norman



"maperalia" wrote in message
...
I have a program that save in excel automatically. However, I have noticed
that some of these files were opened, changed and saved with the changes
accidentally.
I wonder if there is way to protect the file and kill the macros after the
file is been saved.

Thanks for your help in advance.
Maperalia.

Hi Maperalia,

At the top of the link page, Chip Pearson says:

'========================
Before using these procedures, you'll need to set a reference in VBA to the
VBA Extensibility library. In the VBA editor, go to the Tools menu, choose
the References item, and put a check next to "Microsoft Visual Basic For
Applications Extensibility" library. This enables VBA to find the
definitions of these objects. If you are using Excel97, this library will
appear in the References list without a version number: "Microsoft Visual
Basic For Applications Extensibility". If you are using Excel 2000 or 2002,
it will appear with a version number: "Microsoft Visual Basic For
Applications Extensibility 5.3". It is very important that you reference
the proper library. If you reference the wrong library, you will receive
"Type Mismatch" errors. If you don't reference the extensibility library at
all, you will receive "User Defined Type Not Defined Error".

'========================

---
Regards,
Norman



"maperalia" wrote in message
...
Norman;
Thanks for the web page you sent me. I pick the program:
"Deleting All VBA Code In A Project". However, when I run it I got this
window message:

"Complie Error
User-Defined type not defined"

Then is highlighting in the following row of the program:

"Dim VBComp As VBIDE.VBComponent"

I when to tool reference to find out what is the available reference I
need
to pick up, but I could not figure which one I have to get. I have chosen
"Visual Basic for Application" so far but did not work.

Could you please tell me what reference I have to pick up to avoid this
window message again?..

Thanks in advance.
Maperalia






"Norman Jones" wrote:

Hi Maperalia,

I wonder if there is way to protect the file

See VBA help for information an example of the Protect method.


... and kill the macros after the file is been saved.

See Chip Pearson's page on VBE programming at:

http://www.cpearson.com/excel/vbe.htm

See particularly the sections entitled:

'Deleting All Code From A Module'

and

'Deleting All VBA Code In A Project '


---
Regards,
Norman



"maperalia" wrote in message
...
I have a program that save in excel automatically. However, I have
noticed
that some of these files were opened, changed and saved with the
changes
accidentally.
I wonder if there is way to protect the file and kill the macros after
the
file is been saved.

Thanks for your help in advance.
Maperalia.

Norman;
Thanks for the tip. The program is running now, however, it is killing the
macros from my "template" no for the file I "save as" named " & Filename &
".xls" (see program below)
Could you please tell me what I am doing wrong?

Thanks in advance
Maperalia
-------------------------------------------------------------------
Option Explicit
Public Sub SaveArchiveAndKillMacros()
SaveExcelFile
DeleteAllVBA
End Sub

Sub SaveExcelFile()
Dim boError As Boolean
Dim strError As String
Dim Tract As String
Dim WO As String
Dim Supplier As String
Dim Dates As String
Dim Time As String
Dim sFilename As String
Dim Progname As String
Dim Filename As String

strError = ""
boError = False
With Worksheets("Gradation Form")
If .Range("G2") = "" Then
boError = True
strError = strError & "WORK ORDER # "
End If
If .Range("G3") = "" Then
boError = True
strError = strError & "TRACT #"
End If
If .Range("C6") = "" Then
boError = True
strError = strError & "SUPPLIER "
End If
If .Range("G4") = "" Then
boError = True
strError = strError & "DATE "
End If
If .Range("G5") = "" Then
boError = True
strError = strError & "TIME"
End If
If boError = True Then
MsgBox "The Following Ranges have not been Typed Yet - " & strError
Exit Sub
Else
WO = Worksheets("Gradation Form").Range("G2")
Tract = Worksheets("Gradation Form").Range("G3")
Supplier = Worksheets("Gradation Form").Range("C6")
Dates = Worksheets("Gradation Form").Range("G4")
Time = Worksheets("Gradation Form").Range("G5")
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates &
"_" & Time & ""
Progname = "S:\Test Lab\Gradation\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname

Call ListOfFileSave(Filename)


End If
End With
End Sub


Sub ListOfFileSave(Filename As String)
Dim nRow As Integer

Workbooks.Open Filename:="S:\Test Lab\Gradation\Gradation Log.xls"
nRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Cells(nRow, 1) = Filename
ActiveWorkbook.Save
ActiveWindow.Close
End Sub


Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

---------------------------------------------------------









"Norman Jones" wrote:

Hi Maperalia,

At the top of the link page, Chip Pearson says:

'========================
Before using these procedures, you'll need to set a reference in VBA to the
VBA Extensibility library. In the VBA editor, go to the Tools menu, choose
the References item, and put a check next to "Microsoft Visual Basic For
Applications Extensibility" library. This enables VBA to find the
definitions of these objects. If you are using Excel97, this library will
appear in the References list without a version number: "Microsoft Visual
Basic For Applications Extensibility". If you are using Excel 2000 or 2002,
it will appear with a version number: "Microsoft Visual Basic For
Applications Extensibility 5.3". It is very important that you reference
the proper library. If you reference the wrong library, you will receive
"Type Mismatch" errors. If you don't reference the extensibility library at
all, you will receive "User Defined Type Not Defined Error".

'========================

---
Regards,
Norman



"maperalia" wrote in message
...
Norman;
Thanks for the web page you sent me. I pick the program:
"Deleting All VBA Code In A Project". However, when I run it I got this
window message:

"Complie Error
User-Defined type not defined"

Then is highlighting in the following row of the program:

"Dim VBComp As VBIDE.VBComponent"

I when to tool reference to find out what is the available reference I
need
to pick up, but I could not figure which one I have to get. I have chosen
"Visual Basic for Application" so far but did not work.

Could you please tell me what reference I have to pick up to avoid this
window message again?..

Thanks in advance.
Maperalia






"Norman Jones" wrote:

Hi Maperalia,

I wonder if there is way to protect the file

See VBA help for information an example of the Protect method.


... and kill the macros after the file is been saved.

See Chip Pearson's page on VBE programming at:

http://www.cpearson.com/excel/vbe.htm

See particularly the sections entitled:

'Deleting All Code From A Module'

and

'Deleting All VBA Code In A Project '


---
Regards,
Norman



"maperalia" wrote in message
...
I have a program that save in excel automatically. However, I have
noticed
that some of these files were opened, changed and saved with the
changes
accidentally.
I wonder if there is way to protect the file and kill the macros after
the
file is been saved.

Thanks for your help in advance.
Maperalia.