• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Migrating datasheets from Excel 97 to Excel 2010

Ankur Jain

Ankur Jain

New Member
Hello Chandoo and Experts,

I am one of the ex-members of Chandoo's excellent Excel training program.

I also run a website & forum dealing with QTP (automated testing tool by HP). This tool uses VB Scripting as it core language.

One of my forum members have asked a question which I feel is more closely related to Excel than anything else. Please see if you can answer.

If there is anything you wish to clarify please let us know. For your ease, I am reproducing the question here:

Hi Experts,
I'm maintaining a very large QTP test solution (close to 100 reusable actions/tests) that was developed in the days of Excel 97. These tests/actions use very long names greater than 31 characters and relies extensively on Global and Local datasheets (dtLocalSheet) for its data. I have one master spreadsheet in Excel 97 that has all the data (both global and local) required for the test suite. I now want to migrate this spreadsheet from the outdated Excel 97 to Excel 2010. There is one major problem - Excel 97 supported sheet names with greater than 31 chars while Excel 2010 supports a max of 31 chars for its sheet names. So all (dtLocalSheet) references in my test suite fails for tests with names greater than 31 chars (as saving the master spreadsheet in Excel 2010 truncates all long sheet names down to 31 chars).
What might be the best way to migrate the master spreadsheet to Excel 2010 with minimal changes to the code itself?
Click to expand...
 
Hi Ankur ,

I have not really understood what the question is ; can you clarify the problem ?

As I have understood it , the problem is that there is an Excel 97 workbook which has sheet tabs with names that are more than 31 characters long ; when this workbook is opened in Excel 2010 , there is a problem since Excel does not accept tab names longer than 31 characters.

Is my understanding correct ?

Narayan
 
Yes. Your understanding is correct.

The problem is, the code is referencing sheet names greater than 31 characters. Since the test were designed for Excel 2007, most of them use sheet names with greater than 31 chars. For obvious reasons when he try to run the test against Excel 2010, they are failing since Excel 2010 is trimming the sheet names to 31 chars.

Now with this migration to Excel 2010, the forum member is trying to find the "best way to migrate the master spreadsheet to Excel 2010 with minimal changes to the code itself" or in simple words is there any way whereby he can use the same sheet names in Excel 2010 without changing them in his huge collection of code.

I hope I was able to clarify. Please let me know if you need more explanation.
 
Hi Ankur ,

The only explanation is why was it necessary to have sheet names longer than 31 characters !?

You can write a macro which will replace each sheet name with what is called its code name ; if this is done , will the QTP code still work ?

Since the 31 character limit cannot be overcome , what can be done is only workarounds ; only the poster can clarify whether this kind of renaming of all the sheet tabs will work or not.

Narayan
 
You must log in or register to reply here.
Back
Top