VB6.0 读取Excel2010简单方法(附源码和注释,可伸手)

添加一个按钮和CommonDialog控件 如下图
工程引用如下图

源代码:

Private Sub Command1_Click()
     
     Dim sourceFilePath As String
     Me.CommonDialog1.Filter = "Excel (*.xls;*.xlsx)|*.xls;*.xlsx|" '过滤文件
     Me.CommonDialog1.FilterIndex = 2
     Me.CommonDialog1.ShowOpen
     If Me.CommonDialog1.FileName <> "" Then
        sourceFilePath = Me.CommonDialog1.FileName  '获取文件路径
        MsgBox sourceFilePath
     Else
        MsgBox "未选择文件"
        Exit Sub
     End If
     
     Dim rs As ADODB.Recordset
     Set rs = GetExcelRs(sourceFilePath, "sheet1") '调用方法
     MsgBox rs.Fields(0)
End Sub

'连接Excel方法**

Function GetExcelRs(ByVal sFile As String, Optional ExcelSheetName As String = "sheet1", Optional ErrInfo As String) As ADODB.Recordset

   On Error GoTo Err
   Dim rs As ADODB.Recordset
   Set rs = New ADODB.Recordset
   Dim ConnStr As String
   Dim sql As String

   ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " & sFile & ";Extended Properties ='Excel 12.0;HDR=YES;IMEX=1'"  '调用oledb连接，并将Excel表格中的首行做为标题行

   rs.Open "SELECT * FROM [" & ExcelSheetName & "$]", ConnStr, 1, 3
   Set GetExcelRs = rs
   Set rs = Nothing
   Exit Function
Err:
   ErrInfo = Err.Description
   MsgBox ErrInfo
End Function

注意：rs.open语句里的$符号缺失会报"Microsoft Access数据库引擎找不到对象sheet1…"