Prev: Charting the Sum of Two, or More, Series of Data
Next: pulling a list of hyperlinks from a worksheet
From: Ron de on 20 Apr 2010 13:30
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/tutorials/aspnet/6592d2d4-bbf4-4ecd-93df-52898c6aa5d7/iis-70-extensionless-url.aspx
From: NoodNutt on 21 Apr 2010 03:43
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/tutorials/aspnet/6592d2d4-bbf4-4ecd-93df-52898c6aa5d7/iis-70-extensionless-url.aspx


From: Ron de Bruin on 21 Apr 2010 13:34
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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"NoodNutt" <mclind1(a)bigpond.com> wrote in message news:OuDaTZS4KHA.5212(a)TK2MSFTNGP04.phx.gbl...
> 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/tutorials/aspnet/6592d2d4-bbf4-4ecd-93df-52898c6aa5d7/iis-70-extensionless-url.aspx
>
>
From: NoodNutt on 23 Apr 2010 04:16
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.



From: Ron de Bruin on 23 Apr 2010 12:29
Remove the duplicate dim lines in the subs


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm



"NoodNutt" <mclind1(a)bigpond.com> wrote in message news:evXDJ1r4KHA.5808(a)TK2MSFTNGP02.phx.gbl...
> 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.
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Charting the Sum of Two, or More, Series of Data
Next: pulling a list of hyperlinks from a worksheet