Populating table or range to listbox in userform to get the values of the columns inside the table or range in closed workbook

Question

he 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

Answer 1

1. Because rs.RecordCount is of type LongLong, which VBA cannot automatically convert into a long, you receive your type mismatch error. Using CLng, you may convert it explicitly (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