如何使用vb6读取csv或excel文件并将数据写入数据库表

Question

我想从excel文件中读取数据并将数据写入表格。我无法使用excel.application，因为没有安装microsoft office。

Public Function ImportExcel(CommonDialog1 As Object)
Dim sFilePath As String
Dim fso As New FileSystemObject
Dim txtStream As TextStream
Dim sRowRange As Range
Dim nRows As Integer
Dim str As String
'Set the settings for the Common dialogue box and Show
Set CommonDialog1 = CreateObject("MSComDlg.CommonDialog.1")
CommonDialog1.Filter = "Microsoft Excel Workbook(*.xls)"
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> "" Then
sFilePath = CommonDialog1.FileName
If UCase(Right(Trim(sFilePath), 3)) = "XLW" Or UCase(Right(Trim(sFilePath), 3)) = "XLS" Or UCase(Right(Trim(sFilePath), 4)) = "XLSX" Or UCase(Right(Trim(sFilePath), 3)) = "CSV" Then
Set fso = New FileSystemObject
Set txtStream = fso.OpenTextFile(sFilePath, ForReading, False)

Do While Not txtStream.AtEndOfStream
str = txtStream.ReadLine
Loop
txtStream.Close
Else
MsgBox "Make sure your selected file has file extension xlw or xls", vbOKOnly + vbInformation
Exit Function
End If
End If

End Function

Answer 1

正如评论中所建议的，您可以使用ADO和ACE OLEDB Provider的组合来读取Excel文件。这里有一个简单的例子：

Private Sub ImportExcel()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Projects" & _
"Read Excel File With ADOsample.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"

Dim rs As ADODB.Recordset
Set rs = cn.Execute("SELECT * FROM [Sheet1$]")

Do While Not (rs.BOF Or rs.EOF)
'do whatever you need with the data
MsgBox rs.Fields(0).Value & ", " & rs.Fields(1).Value
rs.MoveNext
Loop

rs.Close
cn.Close
End Sub

我在Excel365上运行了这个例子，但我怀疑它也适用于其他版本。记录集将Excel文件中的每一行视为一条记录，将每一列视为一个字段。

您需要添加对Microsoft ActiveX Data Objects X.X Library的引用，并在您的计算机上安装ACE OLEDB提供程序。