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

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) 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