Lose macros before saving

  • Thread starter Thread starter stainless
  • Start date Start date
S

stainless

I am currently working on a project that is converting Excel 2003 .xls/
xlt files to work in Excel 2010.

What currently happens is an Excel 2003 worksheet is opened
programatically as a local instance on a users laptop i.e. an Excel
2003 .xls file with vba code opens up on their laptop regardless of
whether they have Excel 2003 or2010 installed.

For this to work in both environments, we are having to do the changes
in Excel 2003 so that its is backwards compatible (as opening an xlsm
file from a program would cause more issues than it would solve for
Excel 2003 users).

We have discovered an issue with our code that could be a problem
anyway in Excel 2003 but is more of an issue in 2010.

Currently, once our macros have completed formatting the data in Excel
on the users laptop, they can save these locally and this, sadly,
saves them with the macro automatically in Excel 2003. Not ideal as if
they opened the saved file, they may restart the macro which should
not happen as the data formatting has been completed.

It is clear our usesr have lived with this up ubtil now and I assume
ignore macro failures if they attempt to re-open a saved file.

However, in Excel 2010, they are being asked to save the formatted
file as an xlsx file (probably quite sensible) but this results in an
extra message regarding saving with VBA.

ie "... following features cannot be saved .... VB Project ..."

Although this is not a major issue, it is another potential layer of
confusion.

Note that we do not want to force a save as the user may simply print
and then close.

Therefore, I am wondering if, at the end of the Excel 2003 macro
process, I can add some code that:

1. Copies the now formatted workbook to one that does not contain the
macro (but with the same potentially saved name as the current
workbook, as this macro workbook is originally opened with a suggested
name - guess may need a rename of the running macro workbook before
creating the copy to allow for this).
2. Then shuts down the macro version, thus leaving just a macro free
version for saving

If this happened, then I believe in either Excel 2003 or 2010, there
would be no extra messages and no chance of saving the macro.

Any ideas how I could code for this

Cheers
 
I am currently working on a project that is converting Excel 2003 .xls/
xlt files to work in Excel 2010.

What currently happens is an Excel 2003 worksheet is opened
programatically as a local instance on a users laptop i.e. an Excel
2003 .xls file with vba code opens up on their laptop regardless of
whether they have Excel 2003 or2010 installed.

For this to work in both environments, we are having to do the changes
in Excel 2003 so that its is backwards compatible (as opening an xlsm
file from a program would cause more issues than it would solve for
Excel 2003 users).

We have discovered an issue with our code that could be a problem
anyway in Excel 2003 but is more of an issue in 2010.

Currently, once our macros have completed formatting the data in Excel
on the users laptop, they can save these locally and this, sadly,
saves them with the macro automatically in Excel 2003. Not ideal as if
they opened the saved file, they may restart the macro which should
not happen as the data formatting has been completed.

It is clear our usesr have lived with this up ubtil now and I assume
ignore macro failures if they attempt to re-open a saved file.

However, in Excel 2010, they are being asked to save the formatted
file as an xlsx file (probably quite sensible) but this results in an
extra message regarding saving with VBA.

ie "... following features cannot be saved .... VB Project ..."

Although this is not a major issue, it is another potential layer of
confusion.

Note that we do not want to force a save as the user may simply print
and then close.

Therefore, I am wondering if, at the end of the Excel 2003 macro
process, I can add some code that:

1. Copies the now formatted workbook to one that does not contain the
macro (but with the same potentially saved name as the current
workbook, as this macro workbook is originally opened with a suggested
name - guess may need a rename of the running macro workbook before
creating the copy to allow for this).
2. Then shuts down the macro version, thus leaving just a macro free
version for saving

If this happened, then I believe in either Excel 2003 or 2010, there
would be no extra messages and no chance of saving the macro.

Any ideas how I could code for this

Cheers
Click to expand...

Hi

I use this very handy code to remove and kill all VB coding from a workbook.

I call it after I have backed it up.

Sub KillVBCode()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim StartLine As Long
Dim NumLines As Long
Dim ProcName As String
Dim WillRobinson As Integer


WillRobinson = MsgBox("[ DANGER WILL ROBINSON ] YOU ARE ABOUT
TO DELETE ALL VITAL CODES FROM THIS FILE, YA REALLY WANNA DO
THAT..????", vbYesNo)
If WillRobinson = vbYes Then


With Application.VBE
If Not .ActiveCodePane Is Nothing Then
Set .ActiveVBProject =
..ActiveCodePane.CodeModule.Parent.Collection.Parent
End If
End With

Call StopTimer

Set VBProj = ActiveWorkbook.VBProject


Set VBComp = VBProj.VBComponents("Module1")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module2")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module3")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module4")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module5")
VBProj.VBComponents.Remove VBComp

Set VBComp = VBProj.VBComponents("Module6")
VBProj.VBComponents.Remove VBComp

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If

Next VBComp

Else
Cancel = True
End If



End Sub

HTH
Mick
 
I am currently working on a project that is converting Excel 2003 .xls/
xlt files to work in Excel 2010.

What currently happens is an Excel 2003 worksheet is opened
programatically as a local instance on a users laptop i.e. an Excel
2003 .xls file with vba code opens up on their laptop regardless of
whether they have Excel 2003 or2010 installed.

For this to work in both environments, we are having to do the changes
in Excel 2003 so that its is backwards compatible (as opening an xlsm
file from a program would cause more issues than it would solve for
Excel 2003 users).

We have discovered an issue with our code that could be a problem
anyway in Excel 2003 but is more of an issue in 2010.

Currently, once our macros have completed formatting the data in Excel
on the users laptop, they can save these locally and this, sadly,
saves them with the macro automatically in Excel 2003. Not ideal as if
they opened the saved file, they may restart the macro which should
not happen as the data formatting has been completed.

It is clear our usesr have lived with this up ubtil now and I assume
ignore macro failures if they attempt to re-open a saved file.

However, in Excel 2010, they are being asked to save the formatted
file as an xlsx file (probably quite sensible) but this results in an
extra message regarding saving with VBA.

ie  "... following features cannot be saved .... VB Project ..."

Although this is not a major issue, it is another potential layer of
confusion.

Note that we do not want to force a save as the user may simply print
and then close.

Therefore, I am wondering if, at the end of the Excel 2003 macro
process, I can add some code that:

1. Copies the now formatted workbook to one that does not contain the
macro (but with the same potentially saved name as the current
workbook, as this macro workbook is originally opened with a suggested
name - guess may need a rename of the running macro workbook before
creating the copy to allow for this).
2. Then shuts down the macro version, thus leaving just a macro free
version for saving

If this happened, then I believe in either Excel 2003 or 2010, there
would be no extra messages and no chance of saving the macro.

Any ideas how I could code for this

Cheers
Click to expand...

Don't know if this works for you but in xl2003 you can save as .xlsX
which automatically strips ALL macros.
 
Thanks for this Mick.

I will have to see whether we can use it though as there are warnings
online regarding some anti-virus software dealing harshly with code
that uses VBProject. I am not sure what our company's anti-virus
software would do with this.

Cheers
 
Thanks for this Mick.

I will have to see whether we can use it though as there are warnings
online regarding some anti-virus software dealing harshly with code
that uses VBProject. I am not sure what our company's anti-virus
software would do with this.

Cheers
Click to expand...

I use this code at work and the company's AV/Spam filtering is up there
with the best.

My workbook in question is located on a network drive and subject to
screening, the only time any of my VB Codes are blocked is when I attach
them to an email body and the "Mail Marshall" intercepts it, I simply
shoot an email off to the IT dept and they release it.

You should be fine, I simply explained to my ITD that 99% of the codes I
use are contained locally and showed them that there is no external
links which appeased them...

Explain to them the benefits and common sense should prevail, good luck.

Mick
 
Back
Top