Interesting issue today with Excel 2007 and a little bit of VBA.
Have a client with issues with an excel addin not interacting well with the bloomberg addin.
So to get around this we used the old toolbar in the addins option.
Problem is that Excel 2007 deselects the Chart, so the active Chart code doesn't work.
This issue doesn't occur in Excel 2010 or Excel 2003.
We were first getting this ambiguous message:
"The specified dimension is not valid for the current chart type"
Another reason to go straight to Excel 2010 to crunch your data and charts.
Regards,
Tom Bizannes
Excel and Database Magician
Wednesday, April 27, 2011
Saturday, April 16, 2011
Migration to Excel 2010 and Access 2010 the easy way
Migrating to a new version of Office used be quite painless. With the major openxml format changes and major overhaul to both Microsoft Access and Excel, there are many breaking changes that can break many an existing application.
With the right amount of planning, most issues can be dealt with prior or when the migration goes ahead.
What is disturbing is the amount of mis-information about the different versions of Microsoft Office.
This article focuses on what you need to be aware of, and what are the best choices when upgrading the desktops and notebooks of all your users.
Recently a client asked me to assist with migrating to Excel 2007 using a $20K tool.
This appeared to have been written in ancient vb and doesn't seem to be built to cater for the two biggest issues with Excel Migrations.
What I couldn't believe was how developers make tools without knowing the product or add extensions to their products as an afterthought.
The Tool did work okay against Microsoft Access databases, although still haven't put this under pressure to test how it deals with issues we have discovered in simple access databases like fields not being big enough so forms just crash erratically.
Firstly there's the choice between the Microsoft Office 2010 32 bit or the Microsoft Office 2010 64 bit version.
Recommendation from Microsoft is to upgrade to the 32 bit version.
Only a few super power users will need the 64 bit version, and that's most likely due to badly designed cubes or lack of good database guys to roll up information nicely.
Why not 64 bit version of Microsoft Office?
Because that's causes alot of applications with code to break. Also many addons will not work, due to library differences between 32 and 64 bit office. It's not because the 64 bit version is buggy. It's different.
Version of Windows 7 to go to?
Go to the 64 bit version of Windows 7. Any 32 bit office app that doesn't work in Windows 7 64 bit version should be trashed or the developers given a talking to. There are some issues about the Windows 7 Service Pack 1 causing problems for users who don't have the same service pack. Microsoft has a few workarounds.
On first glance it appears to be mainly office 64 bit users who would be effected. The solution being to change the code to use late binding on the adodb (MDAC) libraries. Thes are used mainly to link to sql and other dobc databases. Most Access developerws went back to dao development, unless there were sql links, so alot of code can be fixed quickly and easily.
Do you upgrade your Microsoft Access databases to SQL Server?
No. Not unless you know what you are doing. If the Access databases only have 5 to 10 users, then keep them in Access. If you have more, be ware that sql can cause more locking and other issues, if you do not know what you are doing.
What about Excel files. Should we upgrade them?
Only if you want to as many excel workbooks might be linked to others.
The solution we came up was to add an addon in the Excel ribbon that dealt the linking and the other major issues so the users could do their conversions with less pain, or one click of the button as they say.
What items are missed or done the wrong way?
The new colouring themes are often forgotten about or not done correctly.
Ironically, the word guys try to dictate the styles, but Excel and PowerPoint users are mostly effected by the colouring. Some clients have spent thousands on graphic designer ideas for styles and fonts, but these guys don't always have a grasp on Office 2007 or Office 2010 themes as they live in their adobe software world.
###
Tom Bizannes is a Microsoft Certified Professional with a business background.
He works for a Microsoft Gold Partner in Sydney, Australia
http://www.macroview.com.au
With the right amount of planning, most issues can be dealt with prior or when the migration goes ahead.
What is disturbing is the amount of mis-information about the different versions of Microsoft Office.
This article focuses on what you need to be aware of, and what are the best choices when upgrading the desktops and notebooks of all your users.
Recently a client asked me to assist with migrating to Excel 2007 using a $20K tool.
This appeared to have been written in ancient vb and doesn't seem to be built to cater for the two biggest issues with Excel Migrations.
What I couldn't believe was how developers make tools without knowing the product or add extensions to their products as an afterthought.
The Tool did work okay against Microsoft Access databases, although still haven't put this under pressure to test how it deals with issues we have discovered in simple access databases like fields not being big enough so forms just crash erratically.
Firstly there's the choice between the Microsoft Office 2010 32 bit or the Microsoft Office 2010 64 bit version.
Recommendation from Microsoft is to upgrade to the 32 bit version.
Only a few super power users will need the 64 bit version, and that's most likely due to badly designed cubes or lack of good database guys to roll up information nicely.
Why not 64 bit version of Microsoft Office?
Because that's causes alot of applications with code to break. Also many addons will not work, due to library differences between 32 and 64 bit office. It's not because the 64 bit version is buggy. It's different.
Version of Windows 7 to go to?
Go to the 64 bit version of Windows 7. Any 32 bit office app that doesn't work in Windows 7 64 bit version should be trashed or the developers given a talking to. There are some issues about the Windows 7 Service Pack 1 causing problems for users who don't have the same service pack. Microsoft has a few workarounds.
On first glance it appears to be mainly office 64 bit users who would be effected. The solution being to change the code to use late binding on the adodb (MDAC) libraries. Thes are used mainly to link to sql and other dobc databases. Most Access developerws went back to dao development, unless there were sql links, so alot of code can be fixed quickly and easily.
Do you upgrade your Microsoft Access databases to SQL Server?
No. Not unless you know what you are doing. If the Access databases only have 5 to 10 users, then keep them in Access. If you have more, be ware that sql can cause more locking and other issues, if you do not know what you are doing.
What about Excel files. Should we upgrade them?
Only if you want to as many excel workbooks might be linked to others.
The solution we came up was to add an addon in the Excel ribbon that dealt the linking and the other major issues so the users could do their conversions with less pain, or one click of the button as they say.
What items are missed or done the wrong way?
The new colouring themes are often forgotten about or not done correctly.
Ironically, the word guys try to dictate the styles, but Excel and PowerPoint users are mostly effected by the colouring. Some clients have spent thousands on graphic designer ideas for styles and fonts, but these guys don't always have a grasp on Office 2007 or Office 2010 themes as they live in their adobe software world.
###
Tom Bizannes is a Microsoft Certified Professional with a business background.
He works for a Microsoft Gold Partner in Sydney, Australia
http://www.macroview.com.au
Subscribe to:
Posts (Atom)
