I'm running a SQL SELECT query through an ADO connection to an Excel 2007 workbook with the following code (using a custom version of VBScript)
我正在通过ADO连接到Excel 2007工作簿运行SQL SELECT查询，其中包含以下代码（使用自定义版本的VBScript）
dim ado, rs
set ado = CreateObject("ADODB.Connection")
ado.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=workbook.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
ado.open()
set rs = ado.execute("SELECT * FROM [sheet1$]")
which is straighforward. The problem is that any cell that has text longer than 255 characters is truncated; is there any way around this? Is there a property in the connection string that will support this or is it an option I need to change in the excel document itself? I have tried MSSQL's CAST() function but this just causes an error when executed.
这是直截了当的。问题是任何文本长度超过255个字符的单元格都会被截断;有没有办法解决？连接字符串中是否有支持此属性的属性，还是我需要在excel文档中更改的选项？我已经尝试过MSSQL的CAST（）函数，但这只会在执行时导致错误。
Any help would be greatly appreciated.
任何帮助将不胜感激。
2 个解决方案
#1
1
I think you're running into a variant of a long-standing limitation in Excel's data access provider. See http://support.microsoft.com/default.aspx?scid=kb;EN-US;189897 for an example or google for thousands more.
我认为您在Excel的数据访问提供程序中遇到了长期存在的限制。有关示例，请参阅http://support.microsoft.com/default.aspx?scid=kb;EN-US; 189897;有关数千条，请参阅Google。
#2
0
Instead of trying to use CAST(), have you tried to use the CONVERT() function?
您是否尝试使用CONVERT（）函数而不是尝试使用CAST（）？
#1
1
I think you're running into a variant of a long-standing limitation in Excel's data access provider. See http://support.microsoft.com/default.aspx?scid=kb;EN-US;189897 for an example or google for thousands more.
我认为您在Excel的数据访问提供程序中遇到了长期存在的限制。有关示例，请参阅http://support.microsoft.com/default.aspx?scid=kb;EN-US; 189897;有关数千条，请参阅Google。
#2
0
Instead of trying to use CAST(), have you tried to use the CONVERT() function?
您是否尝试使用CONVERT（）函数而不是尝试使用CAST（）？