Solved: Saving Copy of File but deleting userform/vb on copy.

[nickirvine]

Hi,

I wonder if anyone can help me im sure this is easy enough.

I have created some vb code that saves a copy of file but I want the vb and/or the userform to be deleted on the copy, the first page to print and the original to be deleted after the new file has been saved into a sub directly.

Is there anyway of doing this. The code I have so far, saves and prints page one but doesnt delete the vb code or the original I have:


Private Sub Generatebutton_Click()

Dim Rec As Integer
Dim ToBeSaved As String
Dim SaveName As String
Dim SaveAsName As String

'Set Drive\Folder to save to
Const SaveRoot = "C:\Documents and Settings\"

'Create name for document
SaveName = Surname & " " & Firstname & " " & No
'Create name for file
SaveAsName = SaveName & ".doc"

'Check for previous instances of the SaveName, if found add incremental suffix
With Application.FileSearch
.NewSearch
.LookIn = ActiveDocument.Path
.SearchSubFolders = False
.FileName = SaveName & "*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "This customer has already been refered. Check Duplicate."
ActiveDocument.Close SaveChanges:=False
Else

ActiveDocument.SaveAs FileName:=SaveAsName

Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="1", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

MsgBox "File has been saved.Please refer.
End If
End With

End Sub



Any help really appreciated. Thanks

[fumei]

Run your code from a global template. The code will be there, not in the document.

You seem to have some issues with your variables.

What is Rec for? You declare it, but never use it.
Where does Surname come in? You use it, but never declare it?

Are you using Option Explicit?

As it stands, if a document has VBA code, and you do a SaveAs, that new SavedAs document will also have the VBA code. This is right and proper. It is a SaveAs. There is no reason for it to not have the VBA.

Yes, you can write VBA that will delete procedures. But it is FAR easier to simply have your executing code not in the document in the first place.

[nickirvine]

Hi,

Thanks for your reply.

Rec can be removed yes, Firstname, Surname and No are used in the earlier code.

Yes I understand it will be saving a copy therefore the code will appear in the copy, because it is quite literally a copy.

I wonder if there is anyway around this? could i just save the actual word file minus the vb?

I run it through a word template so it creates a new file. I would like it to create a copy (minus the vb) and delete the original.

Is this possible?

[fumei]

"Yes I understand it will be saving a copy therefore the code will appear in the copy, because it is quite literally a copy.

I wonder if there is anyway around this?"

Yes.

"I run it through a word template so it creates a new file. I would like it to create a copy (minus the vb) and delete the original.

Is this possible?"

Yes.

And I have already explained how.

In fact if you are indeed: "I run it through a word template so it creates a new file. " then that new file will NOT have the VBA code that is in the template. It will however have access to that code, if the template file is available.

Which is why...I will say it again...use a global template.

You have expressed that you understand if you copy a document with (SaveAs), any VBA in that document is also in the SavedAs document.

That is the way it is. If you do not want the VBA code in a document you are sending...then do not HAVE the VBA code in the document. Simple.

[nickirvine]

Thanks Fumei for being thoroughly un helpful.

[fumei]

Shrug. You are welcome for being so thoroughly un listening. I will try again.

"could i just save the actual word file minus the vb?"

No, you can not. Not unless you write VBA code somewhere else, that is NOT in the document to delete the VBA code from the document.

A template does create a new (cloned) document. That cloned document does NOT have the VBA code that is in the template. if the document has code, then it was put there.

Any document cloned from a template can access any code in that template if the template (.dot file) is accessible.

If you want any document to not have code, and you do not want even the template used to clone a new document to have code, then...again...use a global template.

That is what global templates are for. Code containers. You can execute code (load userforms, run procedures) from a global template and its code will never be in any document.

The point is, yes, you CAN delete code, by code. But that code must not be in the document the code is being deleted from. In other words, code can NOT delete itself.

[fumei]

Option Explicit

Sub RemoveVB()
Dim myCodeModule As CodeModule
Dim aDoc As Document
Dim var

Set aDoc = Documents("HasCode1.doc")
For var = 1 To aDoc.VBProject.VBComponents.Count
Set myCodeModule = aDoc.VBProject.VBComponents(var).CodeModule
myCodeModule.DeleteLines 1, myCodeModule.CountOfLines
Next
End Subdeletes all lines of code from the document HasCode1.doc - which MUST be open (thus in the Documents collection).

It does not remove the code modules, it simply deletes all lines IN the code modules.

Happy now?

There.

[lucas]

Thanks Fumei for being thoroughly un helpful.

Maybe you just didn't try hard enough to understand what Gerry was saying.

I have templates with code in them that run on new documents created from the template. The code does not travel with the documents created from the template.

You can still run the code from the document created from the template as long as it is on your machine. That is because it contains a reference to the template.

If you send it to someone else there will be no code in it. Try it. Move it to another computer that doesn't have the template.

Additionally, you can even remove the reference to the template if you so desire.....

Gerry tried to help you but you insist on doing the hardest thing because you are somehow locked into it. Try thinking about alternatives, there are some and they are better than deleting code......

[nickirvine]

Thanks lucas and fumei.

I just thought fumei you were being a bit blunt. I understand what you mean and appreciate your help. I'll have another go, know i understand you.

It is appreciated thanks.

[fumei]

Yeah, sorry. I know I can be overly blunt. I really was trying to help.

[nickirvine]

I see! It all makes sense now! the code isnt actually on the clone. You said this a million times (i know) but it has finally hit home.

I'm a plum, I'm an idiot, I was confused what you were saying and I humbly apologise.

Thanks for helping a newbie. It is much appreciated, thanks loads.

[fumei]

Don't knock yourself too hard. We all, and I mean ALL, were newbies. I cringe when I think of the ignorant and sometimes over-the-top stupid things I did when I was starting.

Good for you to persist.

[fumei]

As an aside, you can also delete entire modules, not just the code lines.

Dim myVB As VBComponent
Set myVB = ActiveDocument _
.VBProject.VBComponents("Module1")

ActiveDocument.VBProject.VBComponents().Remove myVB
NOTE! It does not, repeat NOT, ask you if you want to export it first. If you manually delete a module you get asked first. Using code does not ask, it just does it.