|
|
昨天有位坛友要求：对一个2008年的老帖子进行答复：
如何在Excel中编写代码将.txt的文本文件导入到Access数据库中
http://club.excelhome.net/viewth ... p;page=1#pid5024954
要求将4个文本文件通过Excelvba导入到access数据库中
搜索了论坛上有关文本文件导入到access后发现，这个问题竟然没有一个明确的答案，当时我使用了：
1、分别建立文本和access两个连接，生成两个查询记录集，用AddNew方法循环读入到数据库：
Sub 循环导入文本文件() '引用Microsoft ActiveX Data Objects 2.x Library
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnnData As New ADODB.Connection
Dim rsData As New ADODB.Recordset
Dim myText As String
Dim myPath As String
Dim MyFile As String
Dim myData As String
Dim myTable As String
Dim myName As String, myType As Integer, mySize As Integer
Dim i As Long, j As Long
'循环查询获取所有文本文件全部数据
myPath = ThisWorkbook.Path & "\"
MyFile = Dir(myPath & "*.txt")
Do While MyFile <> ""
If SQL = "" Then SQL = "select * from " & MyFile Else SQL = SQL & " union all select * from " & MyFile
MyFile = Dir()
Loop
cnn.Open "Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & myPath
rs.Open SQL, cnn, adOpenKeyset, adLockOptimistic
'将查询到文本文件全部数据保存到指定数据库的数据表中
myTable = "成绩单"
cnnData.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\" & "学生成绩.mdb"
rsData.Open myTable, cnnData, adOpenKeyset, adLockOptimistic
For i = 1 To rs.RecordCount
With rsData
.AddNew
For j = 0 To rsData.Fields.Count - 1
.Fields(j) = rs.Fields(j).Value
Next
.Update
End With
rs.MoveNext
Next
MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
'关闭记录集和与文本文件以及数据库的连接
rs.Close
cnn.Close
rsData.Close
cnnData.Close
Set rs = Nothing
Set cnn = Nothing
Set rsData = Nothing
Set cnnData = Nothing
End Sub
循环导入文本文件到access.rar
(14.25 KB, 下载次数: 671)
[ 本帖最后由 zhaogang1960 于 2011-8-7 03:48 编辑 ] |
评分
-
6
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-7-11 22:16
楼主
