Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lose macros before saving
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lose macros before saving
On 20/02/2012 10:12 PM, stainless wrote:
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 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.Pare nt 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lose macros before saving
On Feb 20, 5:12*am, stainless wrote:
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 Don't know if this works for you but in xl2003 you can save as .xlsX which automatically strips ALL macros. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lose macros before saving
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lose macros before saving
On 22/02/2012 2:00 AM, stainless wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving excel document as a .csv and not lose formats? | Excel Discussion (Misc queries) | |||
Why does macros assigned to a toolbar lose its link when saved as | Excel Discussion (Misc queries) | |||
Saving macros | Excel Programming | |||
Saving Macros | Excel Discussion (Misc queries) | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming |