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

Here are the guys:

I want to read information from a closed book using ADODB in VBA EXCEL. It happens that rows in cells in excel sometimes have a length greater than 255.

And here's the limitation:

"Your data may be truncated to 255 characters if the first 8 records for the truncated fields contain 255 characters or less. The Microsoft Excel ODBC driver by default scans the first 8 rows of your data for the data type 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 the Excel table is very large. When this value is set to zero, Microsoft Excel scans all records in your table to determine the data type in each column."

So here's my question:

Is there a way I can tell (false) the driver to read more than 255 characters (except for typing a dummy row 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 changes so that I can insert a dummy first row at the top of each column before I read the information from the closed workbook.


source to share

2 answers

Your question will be very interesting and relevant a few years ago.

Since 2007 (see MSDN: Introducing Open XML File Formats to Office (2007) ), the old Excel binary formats have slowly become obsolete, left behind in the closed source space and some of the legacy APIs (along with their limitations) have been replaced with alternatives, especially Microsoft: Open XML SDK 2.5

I don't know if you can damage the legacy ADODB driver to behave differently, but there are of course other approaches and libraries that can make this work.

Some related questions with links to possibly helpful solutions:

Some related APIs (for C #) replace ADODB and remove its limitations:




If you don't want to go through the difficult process of unzipping and editing the underlying XML data , the answer is no.

If, however, you can relax without opening these source files, then the following process will work.

Original answer:

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

  • open the file natively in Excel
  • insert dummy line (s)
  • close and save the file

Then you can continue querying data from closed Excel files using ADODB.



All Articles