|
|
原帖由 ly7aaa7 于 2011-7-10 20:53 发表 
顶！请版主解决1楼问题
1楼没有附件，下面代码只能做到导入所有文本文件到access指定数据表中：
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 |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2008-10-28 17:19
楼主
