Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
I posted on a similiar topic yesterday about code running fine on
Excel 2003/XP and not running on Excel 2007/Win7. Well, I got everything to run on 2007 if the Excel 2003 file is saved in .xlsm format (xlsx bombs) Now the problem is the .xlsm file sent back to Excel 2003 loads fine, except the custom menu pulldown can no longer find the macros linked to it. If the user launches the macros from the the Tools/Macro/Macros path, everything works fine. However, the custom menu on the menubar is broken. So, to recap. The .xls file runs fine on Excel 2003, but the not on 2007. The .xlsm file runs fine on 2007, but not on 2003. The code is identical in both versions. Does anyone have a method to enable cross version compatibility (so that I only relelase and maintain one file) or do I have to caution the user base to use a version aligned to their version of Excel (and distribute .xls and .xlsm versions)? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
On 31/01/2012 21:41, c1802362 wrote:
I posted on a similiar topic yesterday about code running fine on Excel 2003/XP and not running on Excel 2007/Win7. Well, I got everything to run on 2007 if the Excel 2003 file is saved in .xlsm format (xlsx bombs) Now the problem is the .xlsm file sent back to Excel 2003 loads fine, except the custom menu pulldown can no longer find the macros linked to it. If the user launches the macros from the the Tools/Macro/Macros path, everything works fine. However, the custom menu on the menubar is broken. So, to recap. The .xls file runs fine on Excel 2003, but the not on 2007. The .xlsm file runs fine on 2007, but not on 2003. The code is identical in both versions. Does anyone have a method to enable cross version compatibility (so that I only relelase and maintain one file) or do I have to caution the user base to use a version aligned to their version of Excel (and distribute .xls and .xlsm versions)? Ways and means to isolate the unfortunate incompatibilities inside modules that are only called after checking that you are running the right version of Excel have been discussed here before. But if you use any significant number of graphical objects you will go quite quite mad trying to have one version that works on both platforms. Early versions of XL2007 had a number of race conditions in charting that made it almost impossible to run old XL2003 VBA code on it even if the code was notionally correct. That is you could have code which would work on 2007 if you stepped through in the debugger or added judicious waits, but put it on a faster PC and all hell broke loose. I chose to fork the distribution to retain my sanity. -- Regards, Martin Brown |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
Art,
I was following your original post and IMO the problem is raised when your XL2003 file was saved to the later version. It could have just as easily been saved as the early version so it continues to work in both environments. For example, all my addim projects are XLAs whether they run in early or late versions of Excel. This is doable because addins rarely get saved during runtime. Rather, the workbooks they act on get saved in whatever version the addin runs in. The result is there's no forward/backward compatibility issues with the VBA project. Is there any reason why you can't distribute the VBA part of your project separately from the workbook part it acts on? Normally, addin solutions will always start out using their own pre-design workbook templates, and continue in that context each time a user makes a new file based on the pre-designed template. The addin would normally handle the template implementation, but if your project doesn't require a pre-designed file to start with then your even better off to not need a template. Thus, your VBA solution can act on any workbook the user chooses. Going this route will require that you revise your project such that it's 'version-aware', and so runs code appropriate for the current host version of Excel. This is a trivial concern since there's really not a whole lot that's different about core functionality, and so focus needs only to include extended features in the later versions. IMO, this approach is much better than having to manage and maintain 2 versions of your VBA project. In a nutshell.., I'm trying to convey the idea of separating the 'programming tier' of your solution from the 'business tier' that it processes! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
I don't know if this is stuff you already know or not, but the xl2007
extensions now have meaning. *.xlsx=NO macros; *.xlsm=Has macros. One problem I had when going from 2003 to 2007 is I had made the mistake of using the controls toolbar for buttons in 2003. After converting them to the Forms toolbar in 2007, they worked fine. Also, when in 2007 and saving as a 2003, make sure you do "Save as xl97/xl2003". HTH. -pb |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
cubbybear3 explained :
I don't know if this is stuff you already know or not, but the xl2007 extensions now have meaning. *.xlsx=NO macros; *.xlsm=Has macros. One problem I had when going from 2003 to 2007 is I had made the mistake of using the controls toolbar for buttons in 2003. After converting them to the Forms toolbar in 2007, they worked fine. Also, when in 2007 and saving as a 2003, make sure you do "Save as xl97/xl2003". HTH. -pb I rarely use worksheet controls but when I do it's usually Forms controls. Mostly, I use custom menus/toolbars. Using early XLAs to run VBA in either environment has no bearing on how my project workbooks get saved. The point I was attempting to convey here is that if the VBA is separate from the files the macros act on (termed 'project files' in Excel developer land<g) then there's no compatibility issue at all! The result of my suggestion produces workbooks with no VBA macros and so the SaveAs process is much simpler because Excel processes how the file gets saved in its normal fashion same as if you opened a new workbook and saved it. It's when workbooks being saved include macros that the issue arises! Ergo.., no code, no compatibility issues!!! It's imperative that, as you say, we make sure our early files with VBA get saved as early files if we want them to continue to work in early versions. In cases where my projects use code in the same workbook that code acts on, the project manages the save issue via the Auto-Close procedure I use in place of the Workbook_BeforeClose event. This is a more convenient way to handle changes to the file so shutdown is a smooth and graceful process without any nagging prompts. That's made possible by hooking all of Excel's shutdown/close events so they run my code in place of the built-in processes. Another concern with distributing workbooks with VBA is the macro security. Most corporate clients of my run Group Policy over their networks to prohibit their staff from opening any files with macros that are not in the Trust list. Addins don't suffer this anoying inconvenience because they are handled by the Addins Manager and so do not fall under macro security scrutiny. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
On Jan 31, 5:41*pm, GS wrote:
cubbybear3 explained : I don't know if this is stuff you already know or not, but the xl2007 extensions now have meaning. *.xlsx=NO macros; *.xlsm=Has macros. One problem I had when going from 2003 to 2007 is I had made the mistake of using the controls toolbar for buttons in 2003. *After converting them to the Forms toolbar in 2007, they worked fine. Also, when in 2007 and saving as a 2003, make sure you do "Save as xl97/xl2003". HTH. -pb I rarely use worksheet controls but when I do it's usually Forms controls. Mostly, I use custom menus/toolbars. Using early XLAs to run VBA in either environment has no bearing on how my project workbooks get saved. The point I was attempting to convey here is that if the VBA is separate from the files the macros act on (termed 'project files' in Excel developer land<g) then there's no compatibility issue at all! The result of my suggestion produces workbooks with no VBA macros and so the SaveAs process is much simpler because Excel processes how the file gets saved in its normal fashion same as if you opened a new workbook and saved it. It's when workbooks being saved include macros that the issue arises! Ergo.., no code, no compatibility issues!!! It's imperative that, as you say, we make sure our early files with VBA get saved as early files if we want them to continue to work in early versions. In cases where my projects use code in the same workbook that code acts on, the project manages the save issue via the Auto-Close procedure I use in place of the Workbook_BeforeClose event. This is a more convenient way to handle changes to the file so shutdown is a smooth and graceful process without any nagging prompts. That's made possible by hooking all of Excel's shutdown/close events so they run my code in place of the built-in processes. Another concern with distributing workbooks with VBA is the macro security. Most corporate clients of my run Group Policy over their networks to prohibit their staff from opening any files with macros that are not in the Trust list. Addins don't suffer this anoying inconvenience because they are handled by the Addins Manager and so do not fall under macro security scrutiny. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc If you desire, send me your xl2003 version with this msg and I'll take a look on xl2003 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
Garry,
its only getting more frustrating. I've solved one issue, namely I can get the Excel 2003 file with the menus and code as an add-in to work flawlessly on either Excel 2003/XP or Excel 2007/win7. The user version will be a template file. Unfortunately, the problem is now that I don't have a location on our corporate network that will let me store the add-in for everyone's use (ya, I know, but this is a big company with lots of security) and my user base has too many neophytes who won't be able to follow add-in installation instructions if I ship them the .xla file. So, I think the workaround is to carry two versions of the code (2003 and 2007) and warn the user base about which one to use until the entire system is migrated to one version of excel. Art |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
on 2/1/2012, c1802362 supposed :
Garry, its only getting more frustrating. I've solved one issue, namely I can get the Excel 2003 file with the menus and code as an add-in to work flawlessly on either Excel 2003/XP or Excel 2007/win7. The user version will be a template file. Unfortunately, the problem is now that I don't have a location on our corporate network that will let me store the add-in for everyone's use (ya, I know, but this is a big company with lots of security) and my user base has too many neophytes who won't be able to follow add-in installation instructions if I ship them the .xla file. So, I think the workaround is to carry two versions of the code (2003 and 2007) and warn the user base about which one to use until the entire system is migrated to one version of excel. Art Yep.., corporate nonsense drives me nuts too. It's as if they 'think' they're doing **TOP SECRET** stuff and nobody can do anything without whatever level of access! It's pretty much a no brainer to create 1 common folder that everyone can access, or better yet, assign a folder for this use that's covered by network Group Policy. Even if you do get given a folder to store the addin, it's still going to be a nightmare making sure everyone installs it properly so they all use the copy on the server. By default, the Addin Manager will try to copy it to the local machine so everyone could end up with individual copies anyway. Might be better to put the addin & template in a self-extracting utility (zip/exe) that extracts its contents to the same place on everyone's workstation. (Preferrably C:\Documents and Settings\<user name\<addin name) You can use a Windows utility named "iexpress.exe" to do this. It's located in the '~WINDOWS\System32\' folder. To use iexpress: Click the 'Next' button when it opens; Select Option2 (Extract files only) in the wizard; Complete the rest of the wizard as desired; Distribute resulting EXE to users. When users run the EXE, they are prompted for where to extract the files. There's no provision for creating a folder on-the-fly so if a special dedicated folder is desired then the folder must already exist before running the extractor. This is the best way when users need to install addins themselves via Excel's Addins Manager because they can simply browse to the location they just extracted the files to and select the XLA. Once the addin is installed, updates are a simple matter of redistributing a revised version of the project. Users must not have Excel open when the XLA is overwritten, -OR- they must uncheck it in Addins Manager (so the XLA is not in use) before updating with the new version. Note that any custom menus/toolbars you create will be assigned to the Addins tab for versions of Excel using the MSO Ribbon. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
forwards/backward compatibility Excel 2003-2007
Garry,
thanks for the advice and the shoulder to cry on :-) Art On Feb 1, 6:45*pm, GS wrote: on 2/1/2012, c1802362 supposed : Yep.., corporate nonsense drives me nuts too. It's as if they 'think' they're doing **TOP SECRET** stuff and nobody can do anything without whatever level of access! It's pretty much a no brainer to create 1 common folder that everyone can access, or better yet, assign a folder for this use that's covered by network Group Policy. Even if you do get given a folder to store the addin, it's still going to be a nightmare making sure everyone installs it properly so they all use the copy on the server. By default, the Addin Manager will try to copy it to the local machine so everyone could end up with individual copies anyway. Might be better to put the addin & template in a self-extracting utility (zip/exe) that extracts its contents to the same place on everyone's workstation. (Preferrably C:\Documents and Settings\<user name\<addin name) You can use a Windows utility named "iexpress.exe" to do this. It's located in the '~WINDOWS\System32\' folder. To use iexpress: Click the *'Next' *button when it opens; Select Option2 (Extract files only) in the wizard; Complete the rest of the wizard as desired; Distribute resulting EXE to users. When users run the EXE, they are prompted for where to extract the files. There's no provision for creating a folder on-the-fly so if a special dedicated folder is desired then the folder must already exist before running the extractor. This is the best way when users need to install addins themselves via Excel's Addins Manager because they can simply browse to the location they just extracted the files to and select the XLA. Once the addin is installed, updates are a simple matter of redistributing a revised version of the project. Users must not have Excel open when the XLA is overwritten, -OR- they must uncheck it in Addins Manager (so the XLA is not in use) before updating with the new version. Note that any custom menus/toolbars you create will be assigned to the Addins tab for versions of Excel using the MSO Ribbon. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table in Excel 2007 & 2000 (backward compatibility problem) | Excel Discussion (Misc queries) | |||
Excel 2007 Color Backward-Compatibility Issue | Excel Discussion (Misc queries) | |||
Excel 2007 backward compatibility re colors | New Users to Excel | |||
Backward compatibility for VB6 COM Excel Add-In | Excel Programming | |||
VBA - Macro backward compatibility from excel 2002 to excel 2000 | Excel Programming |