Reading and working with strings longer than 255 with ADODB Excel 2010 VBA

Question

Here' a thing for you guys:

I want to read information from from a closed workbook using ADODB in VBA EXCEL. It happens that the strings in the cells in excel sometimes are with a length bigger than 255.

And then here is this limitation: http://support.microsoft.com/kb/189897

"Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column."

There is a "solution" for this: setting the TypeGuessRows value to zero (0) in the registry, but:

"For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column. "

So here's my question:

Is there a way that I can tell (lie) the driver to read more than 255 chars (except putting a dummy string in the first row of each column or setting the TypeGuessRows value to zero (0)).

And if I can't; is there a way to write to closed excel workbooks and save the changes so I can insert dummy first row at the top of each column before I read the information from the closed workbook.

Answer 1

EDIT:

Unless you are willing to go through the arduous process of unzipping and editing the underlying XML data, the answer is no.

If however, you can relax your constraint of not ever opening these source files, then the process below will work.

Original answer:

Since you are already working in Excel VBA and are willing to add dummy rows of data, add the following to your macro before you query the data:

• open the file natively in Excel
• insert the dummy row(s)
• close & save the file

Then you can proceed to query the data from the closed Excel files with ADODB.

Answer 2

Your question would be very interesting and up-to-date several years ago.

Since 2007 (see MSDN: Introducing the Office (2007) Open XML File Formats) old Excel binary formats became slowly obsoleted, left behind in the closed-source space and some legacy APIs (together with their limitations) were replaced by alternatives, especially by the Microsoft: Open XML SDK 2.5

I don't know if you can hack a legacy ADODB driver to behave differently, but there are certainly other approaches and libraries that can get the work done.

Some related Stack Overflow questions with links to perhaps useful solutions:

• Reading Excel files from C#
• Interop Excel is slow

Some related APIs (for C#) replacing the ADODB and removing its limitations:

• http://closedxml.codeplex.com/
• http://freenetexcel.codeplex.com/
• http://epplus.codeplex.com/
• http://npoi.codeplex.com/
• https://github.com/ExcelDataReader/ExcelDataReader