添加一个按钮和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…"