Excel 2007 Workbook doesn't open in Excel 2010

My client has an Excel workbook that they have used for many years. It has vba macros that use querytables.add to connect to a Sybase database via ODBC and populate cells on a worksheet that they have named "DataSheet".  Other worksheets use the data in the DataSheet.  This workbook has survived through the various new versions of MS Office through Office 2007.  However it does not open properly with Office 2010.  The CPU stays stuck at 50% utilization forever and the program is unresponsive to user clicks on tabs, buttons, and menus.  It stays frozen. I have stripped out the DataSheet worksheet, and the workbook opens and at least the user can navigate through the tabs, buttons, and menus.  I have stripped out all worksheets other than the DataSheet and the problem occurs.  500 points for anyone who provides a solution.  TIA.
glentekAsked:
Who is Participating?
 
glentekAuthor Commented:
Here is what worked. I opened the Excel 2007 .xlsm workbook in Excel 2007.  I inserted a new worksheet and named it "DataSheet_fix".  I globally replaced cell references to "DataSheet" (the original worksheet that stored the ODBC-retrieved data)  in all worksheets (there are dozens) with "DataSheet_fix".  I changed the VBA code to populate the "DataSheet_fix" worksheet instead of the "DataSheet" worksheet. I deleted the "DataSheet" worksheet.  I saved the workbook. I opened the workbook in Excel 2010 and everything worked correctly. Coincidentally, the new workbook takes up one tenth the disk space as the old one.
0
 
bromy2004Commented:
Have you try re-building the DataSheet worksheet manually?
And then see if the same problem occurs.

Excel 2010 may have made some changes in the ODBC section of Data imports.
0
 
glentekAuthor Commented:
Do you mean insert a new worksheet and delete the old DataSheet?  Or clearing out the DataSheet somehow? Please provide more details about your suggestion.  Thanks.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
gowflowCommented:
However it does not open properly with Office 2010
>>> Make sure you have installed SP1 for office 2010 as known several issue with undesired behaviour like hanging, not accept edit, freeze that are solved with SP1. An other suggestion run diagnostics to make sure your installed version is error free goto the left top office icon click on it choose Excel option then choose ressources then click on Run diagnostics.

Hope above helps.
gowflow
0
 
glentekAuthor Commented:
OK Guys.  I need explicit instructions. @gowflow: how do I check to see if SP1 is installed? Also, when Excel is running I see no office icon in the top left.  I only see an Excel icon and I see no resources option.  @bromy2004: please provide explicit instructions about what you are talking about.

I am a software guy who just happens to be the point person for this Excel workbook.  It is not my main focus.  That being said. I hate Office 2007 and 2010.  I hate the "ribbon". I still can't find anything in the MS Office 2007 and 2010 products, nor do I have time to relearn where they have hidden all the functionality that I used to use. To check out your solutions I need explicit instructions on how to navigate through the non-intuitive mess that MS Office has become.  Thanks.
0
 
gowflowCommented:
What Excel version you have in this workbook if you don't see the icon it seems you are running Excel 2003 !! to know Help about will tell you excel version
gowflow
0
 
glentekAuthor Commented:
@gowflow: running Excel 2010.
0
 
gowflowCommented:
Well I hv 2007 and presume 2010 should be the same don't you have a round icon the office icon in the top left corner of Excel like in the attached pic ?
gowflow
officebutton.jpg
0
 
gowflowCommented:
1) What is the extention of the file you are running .xls or .xlsx / .xlsm ??
2) Do you have Excel 2003 installed on your PC as well ?
3) When you try opening your workbook do you have other workbook opened or its the only workbook that is opened ?
gowflow
0
 
glentekAuthor Commented:
@gowflow: The button you are talking about does not exist in my 2010 installation.  I spoke with a help desk rep at this company and he confirmed that it does not exist.

1. The extension is .xlsm and was saved using Excel 2007.
2. No, Office 2003 is not installed.
3. No other workbook is opened.  Or another workbook could be opened.  Either way, same problem.

Thanks.
0
 
gowflowCommented:
ok fine here it is
Excel 2010 known to have issues again you need to make sure SP1 is intalled to know

1) open control panel choose programs and Features
2) look in the list till you find office 2010 and check if there is an item SP1 (make sure show updates is ticked on top
3) in your excel 2010 there should be a help menu try help/about and it should display the Excel / office version or show you a repair button or diagnostics

gowflow
0
 
glentekAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for glentek's comment http:/Q_27382766.html#36940284

for the following reason:

My answer gives the correct and most complete solution.
0
 
glentekAuthor Commented:
@gowflow - I asked bromy2004 for more clarification, and got no response. Therefore, no points. Thanks to all who who responded, but I stand by my decision.
0
 
bromy2004Commented:
Sorry for the lack of response, I was on a temporary deployment and forgot to take my laptop.

What I meant was
1. Create a new workbook in Excel 2010
2. Copy the formulas over to the new workbook manually (open the cell and copy the formula text)
3. Re-add any formatting/data connections required
4. Attempt to re-open the new workbook (After saving of course)

Although I didn't respond immediately, doesn't mean you should discredit/not try the other suggestions.

Regarding comment #36925020
The button in Excel 2010 is there, it is just different.
In 2010 it is a green tab (like any other tab on the ribbon) with the product name (Excel for excel etc)

0
 
glentekAuthor Commented:
@bromy2004  - No problem with the delayed response.  Thank you for your efforts. As described in my chosen solution (#36940284) I solved the problem without having to generate a new workbook.  Your proposed solution probably would not have worked in this case, or would have been excessively cumbersome.  Here's why. As described in my first post, the 2007 workbook could not be opened in 2010, so there would be no way to copy/paste into the new 2010 workbook that you suggest.  My client does not keep multiple versions of office installed on workstations, so there would be no way to open the original 2007 workbook in 2007 and copy/paste the cells to a new 2010 workbook (not sure if you can have both version open at the same time anyway, or if you could be able to copy/paste cell contents from one to another if you could). Also, the workbook in question is quite large. Dozens of worksheets that are fed from the cells in the DataSheet. Manually copy/pasting would introduce too many opportunities for error.  The solution I have provided took less than 5 minutes to implement, and worked correctly the first time.
0
 
glentekAuthor Commented:
My answer gives the correct and most complete solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.