sql - VBA Excel SQL 查询将字段限制为 255 个字符

Question

对 Excel 工作簿执行简单的 select * 查询时，它会在 255 个字符后截断文本。记录集字段是否限制为 255 个字符？如果没有，我怎样才能从记录集中获取完整的字段文本？


测试行应有超过 1400 个字符
所有其他行项目少于 255 个字符，并且是正确的。

Sub ExportCallLogs()

Dim conn As Object
Dim rs As Object
Dim dbpath As String

' Create the connection string.
dbpath = "mypathhere.xlsx"

' Create the Connection and Recordset objects.
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' Open the connection and execute.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
conn.Open sConnString
Set rs = conn.Execute("SELECT * FROM [Sheet1$]")

' Check we have data.
If rs.EOF Then
    MsgBox "Error: No records returned.", vbCritical
    Exit Sub
End If

'print headers and recordset
Workbooks.Add
For i = 0 To rs.Fields.Count - 1
    Cells(1, i + 1).Value = rs.Fields(i).Name
Next
Range("A2").CopyFromRecordset rs

Columns.AutoFit
Columns(i).ColumnWidth = 55

' Clean up
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

Answer 1

您通常可以通过将 Excel 列的格式从 Text 更改为 General 来解决此问题。