Forum Discussion

jan_muczyn's avatar
jan_muczyn
New Contributor
11 years ago

ACE driver limited to 255 Excel columns?

Hi Team



I am creating a Keyword Test using Data-Driven Loop operation. Everything works fine, but Test Complete seems to be unable to read past Excel column 255, even the Variable dropdown list with colum names ends on position no 255.



I have used SmartBear support page in order to write two very simple scripts. One created an Excel Driver (DDT.ExcelDriver) and counted columns - the result was 255. The other one, created as COM object (Sys.OleObject) gave proper result.



Is there something I can do to make ACE Driver read more than 255 columns? If not - is there an easy way to change the Keyword Tests to use COM object (as opposed to converting tests to script and rewriting)?



Thanks

9 Replies

  • Ryan_Moran's avatar
    Ryan_Moran
    Valued Contributor
    11 years ago
    I believe this is a common misconception about Excel and the ACE driver. You need to have both Office 64 bit and the ACE 64 bit driver installed in order to import more than 255 characters. However TestComplete is only a 32 bit application and you will not be able to connect to the 64 bit ACE driver from a 32 bit application.

    This is a 32 bit limitation. Cell values are also limited to 255 characters.





    Q: Is there something I can do to make ACE Driver read more than 255 columns?



    A: Use more sheets and less columns :)



  • Ryan_Moran's avatar
    Ryan_Moran
    Valued Contributor
    11 years ago
    Applied the registry change suggested by Helen and it appears to have worked.

    Verified that prior to registry change fields were truncated to 255 characters.

    Verified that applying the change to the TypeGuessRows and rebooting allows for more columns/characters to be selected.

    Though I won't ever be storing this much data in a single spreadsheet I am happy to be wrong in this case :).

    Thanks Helen!



    *

    ACE - 32 bit driver

    EXCEL 2010 - 32 bit

    Windows 8 - 64 bit

    *
  • Marsha_R's avatar
    Marsha_R
    Icon for Champion Level 3 rankChampion Level 3
    11 years ago
    I'm with Ryan.  Why so many columns in one sheet?  That makes it difficult on the human interface too.  ;)



    We use multiple sheets in one file with descriptive sheet names so we can get at the data easily.
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    11 years ago
    Think that driver is provided by Microsoft?



    I also have a feeling that the 255 column restriction was removed in newer versions. Office 2013 onwards I think?



    What version of Office (and thus the ACE driver) do you have installed?
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    11 years ago
    Helen - no. That's the type of cell it interprets it as. Not the number of columns it's capable of reading.



    I think Ryan is right and it's simply that the driver MS provides is not very good.



    Considering they updated Excel to allow far more than 255 columns several years ago, it does seem a bit stupid that they didn't update the connection driver in line with it.
  • HKosova's avatar
    HKosova
    Icon for Alumni rankAlumni
    11 years ago
    Ryan - TypeGuessRows solves the 255 character per cell limit, but unfortunately not the 255 column limit (I misread the original question). Anyway, glad I could help you. :)

  • Ryan_Moran's avatar
    Ryan_Moran
    Valued Contributor
    11 years ago
    Hmmm well. It seemed to work for me, but could just be that I did not test it thoroughly enough. Either way it did allow me to pull over 255 characters from a cell.



    The column truncation at 255 columns is likely just a limitation of the ACE driver from what I can find. Probably not a good practice to use more than 255 columns in a sheet anyhow...

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    11 years ago
    lol @ the columns/characters confusion.



    You can also use dummy data instead of a registry hack to also get round the character truncation.



    But yeah, the column limit appears to be tied to the ACE driver supplied by MS. And I also agree that 255 columns does seem excessive. But if needs must ....