Saving & Removing VB Code from WB

[NoodNutt]

Hi everyone

1. I'm looking for something in VB that will save a copy of my workbook "in
the background" to another location on a network drive.

2. During the SaveAs process delete all VB code from the "Saved As Workbook"

So, would it be easier to just save the WB, then run Chip Pearsons
"DeleteAllVBACode()" whilst the Saved As file is closed before the intended
other user has an opportunity to open it, or can this process be done during
the SaveAs process.

I know Ron DeBruin has handy bits of code that can create a "TempFile" when
e-mailing to others, then kill that "TempFile".

I was toying with the idea of modding it so that I save my file as a
TempFile
Then do the "DeleteAllVBACode()" on the TempFile
Then do the SaveAs over to the other network location
Then kill the "TempFile"

But I'm unsure were I would insert the "DeleteAllVBACode()" command-line.

And what would really be super handy is to have it all automated when I
close the workbook at the end of the day by inserting something into the
Workbook_BeforeClose(Cancel As Boolean) function.

Appreciate any help

TIA
Mark.

 

[Ron de Bruin]

Try this for the activeworkbook
copy in in a standard module
Chnage this line to the folder where you want the file
TempFilePath = "C:\Users\Ron Desktop\Test\"



Dim wb2 As Workbook

Sub TestDeleteVBA()
'Working in 2000-2010
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim I As Long

Set wb1 = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it//Delete VBA
'If you want to change the file name then change only TempFileName
TempFilePath = "C:\Users\Ron Desktop\Test\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
Call DeleteAllVBA
.Close SaveChanges:=True
End With

Set wb2 = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = wb2.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub








NoodNutt wrote:

Saving & Removing VB Code from WB
20-Apr-10

Hi everyone

1. I am looking for something in VB that will save a copy of my workbook "in
the background" to another location on a network drive.

2. During the SaveAs process delete all VB code from the "Saved As Workbook"

So, would it be easier to just save the WB, then run Chip Pearsons
"DeleteAllVBACode()" whilst the Saved As file is closed before the intended
other user has an opportunity to open it, or can this process be done during
the SaveAs process.

I know Ron DeBruin has handy bits of code that can create a "TempFile" when
e-mailing to others, then kill that "TempFile".

I was toying with the idea of modding it so that I save my file as a
TempFile
Then do the "DeleteAllVBACode()" on the TempFile
Then do the SaveAs over to the other network location
Then kill the "TempFile"

But I am unsure were I would insert the "DeleteAllVBACode()" command-line.

And what would really be super handy is to have it all automated when I
close the workbook at the end of the day by inserting something into the
Workbook_BeforeClose(Cancel As Boolean) function.

Appreciate any help

TIA
Mark.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx

 

[NoodNutt]

Hi Ron

Thank you for your reply.

The code seems to hang on this command-line

Set VBComps = wb2.VBProject.VBComponents

Not sure why, unless it has something to do with the network, and or the
Remote Citrix server.

Also couldn't see where this statement relates to:

Dim I As Long


Thx again
Cheers
Mark.




Try this for the activeworkbook
copy in in a standard module
Chnage this line to the folder where you want the file
TempFilePath = "C:\Users\Ron Desktop\Test\"



Dim wb2 As Workbook

Sub TestDeleteVBA()
'Working in 2000-2010
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim I As Long

Set wb1 = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it//Delete VBA
'If you want to change the file name then change only TempFileName
TempFilePath = "C:\Users\Ron Desktop\Test\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", ,
1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
Call DeleteAllVBA
.Close SaveChanges:=True
End With

Set wb2 = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = wb2.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub








NoodNutt wrote:

Saving & Removing VB Code from WB
20-Apr-10

Hi everyone

1. I am looking for something in VB that will save a copy of my workbook "in
the background" to another location on a network drive.

2. During the SaveAs process delete all VB code from the "Saved As Workbook"

So, would it be easier to just save the WB, then run Chip Pearsons
"DeleteAllVBACode()" whilst the Saved As file is closed before the intended
other user has an opportunity to open it, or can this process be done during
the SaveAs process.

I know Ron DeBruin has handy bits of code that can create a "TempFile" when
e-mailing to others, then kill that "TempFile".

I was toying with the idea of modding it so that I save my file as a
TempFile
Then do the "DeleteAllVBACode()" on the TempFile
Then do the SaveAs over to the other network location
Then kill the "TempFile"

But I am unsure were I would insert the "DeleteAllVBACode()" command-line.

And what would really be super handy is to have it all automated when I
close the workbook at the end of the day by inserting something into the
Workbook_BeforeClose(Cancel As Boolean) function.

Appreciate any help

TIA
Mark.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx

 

[Ron de Bruin]

Have you copy this on top of your module

Dim wb2 As Workbook


You can remove

Dim I As Long

I changed a existing macro and forgot to delete the dim line

 

[NoodNutt]

Thx again Ron,

This is how the Code looks:
---------------------------------------------------------------------------------------

Option Explicit

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim VBComp As Object
Dim VBComps As Object
---------------------------------------------------------------------------------------

Sub BackupWowSchedule()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String

Set wb1 = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = "T:\Wow Vic\Wow Scheduler\"
TempFileName = wb1.Name & " " & Format(Now, "dd-mmm-yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


With wb2

Call DeleteAllVBA
.Close SaveChanges:=True

End With

Set wb2 = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
---------------------------------------------------------------------------------------

Public Sub DeleteAllVBA()


Dim VBComp As Object
Dim VBComps As Object

(--> Set VBComps = wb2.VBProject.VBComponents <--)
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, 2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub
---------------------------------------------------------------------------------------

One other minor thing redarding the SaveAs (FileName) which saves as: Wow
Schedule.xls 23-Apr-10.xls.

Is it possible to have it save as Wow Schedule - 23-Apr-10.xls plz

Thx again Ron

Cheers
Mark.

 

[Ron de Bruin]

Remove the duplicate dim lines in the subs

 

[NoodNutt]

Thx again for all your assistance Ron, I decided not to worry about it as It
was still hanging on that command-line.

I will just instruct the other user not to mess around with the
command-buttons, or suffer.......

Thx heaps again.

Regards
Mark.