英文:
populating table or range to listbox in userform to get the values of the columns inside the table or range in closed workbook
问题
问题在于代码与我不兼容，出现"类型不匹配错误"。
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=F:\Book1.xlsx;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = "select [date] ,[factory] ,[records] from [sheet1$]"
rs.Open
With Me.ListBox1
.ColumnCount = rs.RecordCount
.List = Application.WorksheetFunction.Transpose(rs.GetRows)
End With
rs.Close
cn.Close
英文:
The problem is that the code is not working with me and gives "type mismatch error"
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=F:\Book1.xlsx;" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = "select [date] ,[factory] ,[records] from [sheet1$]"
rs.Open
With Me.ListBox1
.ColumnCount = rs.RecordCount
.List = Application.WorksheetFunction.Transpose(rs.GetRows)
End With
rs.Close
cn.Close
答案1
得分: 1
(1) 由于 rs.RecordCount 的类型为 LongLong，而 VBA 无法隐式转换为 long，因此您会收到类型不匹配的错误。您可以使用 CLng(rs.RecordCount) 显式转换它。
(2) 当将Excel用作数据源时，RecordCount 总是为 -1，因为Excel引擎不支持 RecordCount（参见 https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/recordcount-property-ado?view=sql-server-ver16）。
(3) 您可能不希望将 RecordCount 作为列表视图中的列数，我假设您想要获取字段数作为列数：
使用以下代码更新 Me.ListBox1：
```vba
With Me.ListBox1
.Clear
.ColumnCount = rs.Fields.Count
.List = Application.WorksheetFunction.Transpose(rs.GetRows)
End With
```
英文:
(1) You get your type mismatch error because rs.RecordCount is of type LongLong which VBA cannot implicitly convert into a long. You could convert is explicitly with CLng(rs.RecordCount)
(2) When using Excel as data source, RecordCount will always be -1 as the Excel engine doesn't support RecordCount (see https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/recordcount-property-ado?view=sql-server-ver16).
(3) You probably don't want to have RecordCount as number of columns for your listview anyhow - I assume you want to get the number of fields as column count:
With Me.ListBox1
.Clear
.ColumnCount = rs.Fields.Count
.List = Application.WorksheetFunction.Transpose(rs.GetRows)
End With
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论