[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007 Workbook doesn't open in Excel 2010

Posted on 2011-10-05
16
Medium Priority
?
458 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:glentek
  • 9
  • 5
  • 2
16 Comments
 
LVL 10

Expert Comment

by:bromy2004
ID: 36921901
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
 

Author Comment

by:glentek
ID: 36921942
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36922597
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:glentek
ID: 36923991
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36924549
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
 

Author Comment

by:glentek
ID: 36924702
@gowflow: running Excel 2010.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36925020
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36925035
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
 

Author Comment

by:glentek
ID: 36925257
@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
 
LVL 31

Expert Comment

by:gowflow
ID: 36925374
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
 

Accepted Solution

by:
glentek earned 0 total points
ID: 36940284
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
 

Author Comment

by:glentek
ID: 36948028
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
 

Author Comment

by:glentek
ID: 36940742
@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
 
LVL 10

Expert Comment

by:bromy2004
ID: 36948029
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
 

Author Comment

by:glentek
ID: 36949464
@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
 

Author Closing Comment

by:glentek
ID: 36991261
My answer gives the correct and most complete solution.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

873 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