7

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.

Martin Patsov
  • 366
  • 5
  • 10
  • 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... –  Sep 12 '14 at 07: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. –  Sep 12 '14 at 08: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 Sep 12 '14 at 08:52
  • 1
    i am wondering why Microsoft not addressing this silly issue. – Yash Aug 27 '19 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 Aug 27 '19 at 17:01

2 Answers2

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.

Rachel Hettinger
  • 7,927
  • 2
  • 21
  • 31
  • 1
    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. – Martin Patsov Sep 11 '14 at 22:33
  • 3
    @МартинП. although there are some formula hacks described e.g. at https://vba4all.wordpress.com/2013/10/11/various-ways-to-pull-data-out-of-a-closed-or-opened-workbook-using-excel-formulas-and-vba/ opening the workbook invisibly in Excel (http://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display) 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 – xmojmr Sep 12 '14 at 16:54
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:

Community
  • 1
  • 1
xmojmr
  • 8,073
  • 5
  • 31
  • 54