Migrating datasheets from Excel 97 to Excel 2010

[drmzfire]

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?

[Ankur]

This is a good question. I feel it is more to do with MS Excel than QTP. I have forwarded your question to an Excel expert group. Here is the thread. See if you may want to post your thoughts on it.

[drmzfire]

@Ankur: Thanks for looking into this! As the excel expert confirms, there may not be an easy fix for this.
Can I pick your brains on my proposed solutions then:
Option 1) Rename all tests greater than 14 chars (as well as the scripts where they are called) to names less than 14 chars. This will ensure that dtLocalSheet reference does not exceed 31 chars at any given time. This may also involve copying data from the old (long-named) sheets into new (shorter-named) sheets
Option 2) For tests with names greater than 14 chars, make use of named sheets in the excel workbook (eg. DataTable("DataEntry_CreditCardPaymentDetails","Payment") instead of dtLocalSheet. Modify the tests to replace dtLocalSheet with the respective assigned sheet ("Payment") for each of the tests.

Unless I'm missing something, I'm thinking Option 2 is the better bet. My motive is to make minimal changes to the code. I think I'd be better off making changes to spreadsheet names rather than the names of the tests themselves.

What do you think?

[Ankur]

1. I am wondering instead of having such long names can't you shorten the names for ex: CC for Credit Card; DE for Data Entry etc.
   
2. How is the test name affecting your local sheet name, I don't understand. Do you have some VB Script logic behind it?
   
3. Also as a suggestion, do estimate the manual effort and time you would need to spend to change the names vis-a-vis searching for workarounds.
   

[drmzfire]

On point 2: If my test name is "CreditCardPayment", my datatable local sheet name shows up as "CreditCardPayment [CreditCardPayment]". Therefore, my master spreadsheet sheet names are also named thus.

UPDATE: I must add that as these are resusable actions, they are all referenced in my master test as external actions. So that's how the local sheetnames appear is my master test datatable

[drmzfire]

@Ankur: I'm going to propose that changing the long (reusable/external) test names to shortened names would be the way to go about solving the problem; pretty much the same suggestion that you gave in point 1 above. Let me know if you see any issues with this proposal.