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.

Have you actually tested the performance with TypeGuessRows = 0? It would make sense to first see how much of an impact that has on the performance... — user2140173
– user2140173, Commented Sep 12, 2014 at 7:20
btw. you can insert values using cn.Execute("INSERT INTO [namedRange] Values ('" & String(255, "i") & "'))" but it will not accept a String longer than 255 characters therefore don't rely on this as a solution. — user2140173
– user2140173, Commented Sep 12, 2014 at 8:17
I have not tested the performance with TypeGuessRows = 0 BUT my files are very big, with a lot of information, a lot of rows, some of them are shared AND I do not want to mess with the registry settings. So this is not a solution for me. — Martin Patsov
– Martin Patsov, Commented Sep 12, 2014 at 8:52
i am wondering why Microsoft not addressing this silly issue. — Yash
– Yash, Commented Aug 27, 2019 at 14:09
@user2781180 I think the accepted answer gives us the reason: "old Excel binary formats became slowly obsoleted". I would not expect this to get fixed. — Martin Patsov
– Martin Patsov, Commented Aug 27, 2019 at 17:01

Rachel Hettinger · Accepted Answer · 2014-09-12 15:40:05Z

5

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.

edited Sep 12, 2014 at 15:40

answered Sep 11, 2014 at 20:57

Rachel Hettinger

8,4872 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Patsov Over a year ago

I want to read information from from a closed workbook. I want to write to closed excel workbooks and save the changes. Closed workbook. I do not want to open them. If I wanted I wouldn't have used ADODB. Thanks for the answer though.

xmojmr Over a year ago

@МартинП. although there are some formula hacks described e.g. at vba4all.wordpress.com/2013/10/11/… opening the workbook invisibly in Excel (stackoverflow.com/questions/579797/…) looks like the easiest solution to me. Perhaps the whole use case (not described in the question) (and its constraints) that your are solving should be re-thought. Pumping the "lot of information" from Excel into a local SQL Express engine might be one such option

Community · Accepted Answer · 2017-05-23 11:52:04Z

1

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:

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

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Sep 12, 2014 at 15:30

xmojmr

8,1765 gold badges33 silver badges54 bronze badges

1 Comment

Martin Patsov Over a year ago

Thank you very much. I guess I should start learning C#. I wish I was able to do this only using VBA...

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related