问题描述
这是给你们的东西：
我想在VBA EXCEL中使用ADODB从封闭的工作簿中读取信息。
碰巧excel中的单元格中的字符串有时长度大于255。
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.
然后是此限制：
如果您要截断的字段的前8条记录包含255个或更少的字符，则您的数据可能被截断为255个字符。默认情况下，Microsoft Excel ODBC驱动程序将扫描数据的前8行以确定
"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."
对此有一个解决方案：在注册表中将TypeGuessRows值设置为零（0），但是：
There is a "solution" for this: setting the TypeGuessRows value to zero (0) in the registry, but:
出于性能原因，如果您的Excel表很大，建议不要将TypeGuessRows值设置为零（0）。当该值设置为零时，Microsoft Excel将进行扫描表中的所有记录，以确定每一列中的数据类型。
"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. "
所以这是我的问题：
是是一种我可以告诉（说谎）驱动程序读取超过255个字符的方式（除了在每列的第一行中放置一个虚拟字符串或将TypeGuessRows值设置为零（0）之外）。
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)).
如果不能的话；有没有一种方法可以写入已关闭的excel工作簿并保存更改，因此我可以在从已关闭的工作簿中读取信息之前，在每列的顶部插入虚拟第一行。
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.
推荐答案
几年前，您的问题将非常有趣并且是最新的。
Your question would be very interesting and up-to-date several years ago.
自2007年以来（请参见），旧的Excel二进制格式已逐渐过时，被留在了封闭源代码空间中，并且某些旧式API（及其局限性）已被替代方法替代，尤其是被
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
我不知道您是否可以破解旧版ADODB驱动程序以改变行为，但是肯定还有其他可以完成工作的方法和库。
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
一些相关的API（用于C＃）替换了ADODB并消除了其局限性：
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
这篇关于使用ADODB Excel 2010 VBA读取和使用长度超过255的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！