Solved

Migrating to Excel 2010 - Have workbook that uses Excel 10 worksheets and OWC10 in VBA

Posted on 2010-11-15
5
793 Views
Last Modified: 2012-08-14
I'm thinking that there's a relatively smooth way to do this, but maybe not.

I have an Excel workbook with extensive VBA that was developed using OWC10 charts and a few instances of the Excel Worksheet control on some forms.

When I moved from Excel 2002 to Excel 2003, things were relatively painless. Opening the workbook for the first time on the new system worked - no error message about missing controls or compile errors that indicated a missing OWC10.dll.

Opening the same workbook on an Excel 2007 machine worked, as long as I made sure that OWC10.dll was installed. (at least that's what I remember - it's been a few months.)

All this time the workstations were running WinXP SP3.

I'm now testing this workbook on a Win7 machine with Office 2010 and running into some issues when I first open the workbook. I get multiple "Could not load some objects because they are not available on this machine." messages - these seem to be one for each instance where I use the Excel Worksheet object. This is not available by default, apparently. When I open the code, and then the form, these instances of the xlworksheet object are gone.

I also get a couple of "Compile error in xxxxx module..." errors that I know can be solved by defining the location of the OWC10.dll in the VBA References.

My question is this:
How can I predispose Excel 2010 to accept this workbook? Is there some setup that I can do in Excel that will make sure these references and objects are available to all workbooks before they are opened for the first time? If not that, is there some remedy I can apply after opening the first time that will preserve those xlworksheet instances?

I understand that OWC is NOT the way to go into the future, and we're working on eliminating these controls, but in the meantime I also have read that with proper preparation I should be able to use these legacy controls until I complete the changes.

Any help please?
Thanks.
0
Comment
Question by:thomasedooley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 34143012
As long as you're NOT using Office 64 bit, you should be able to install the OWC10.dll (provided you have it). Then the workbook should work as expected (maybe you'll have to change the reference first).
If you've installed 64 bit Office, you've now reached the moment in time that you HAVE to update your file to get rid of the legacy control.
0
 

Author Comment

by:thomasedooley
ID: 34144489
OWC10.dll is installed. I change the reference, but then when saving, it's telling me that there are components in use that are not available on this machine (the xlworksheet objects). Those objects disappear from the form.

I'm looking for a way to not have to recreate the objects on the form... Any ideas?
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34144676
Normally those references are picked up by Excel, as long as the dll is properly registered and of the same version. So after registering the dll with Windows (a one time step obviously), you should be able to open the Excel file and work with it without problems.

Since this is not the case I am wondering what else is going on here. You did install the 32 bit version of Office 2010 (this is the default when you run setup)?
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34144685
NB: There are two things at play here, referencing the dll from the workbook and registering the dll with Windows. the dll  must be installed (=registered) on each and every maching the workbook is to be used. But I expect you knew this already :-)
0
 

Author Closing Comment

by:thomasedooley
ID: 34146398
Thanks for jogging my memory. What I had installed on the Win7/Office2010 machine was not the same version of OWC10.dll as I was using in development.

Once I registered the newest version of OWC10.dll, the workbook opened under Win7/Office2010 without issue.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question