在Excel中使用VBA的文本文件(ADO)导入数据
下面的过程可用于从文本文件获取ADO记录集并将结果填写在工作表中。
Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range) ' example: GetTextFileData "SELECT * FROM filename.txt", _ "C:\FolderName", Range("A3") ' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _ "C:\FolderName", Range("A3") Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer If rngTargetCell Is Nothing Then Exit Sub Set cn = New ADODB.Connection On Error Resume Next cn.Open "Driver={Microsoft Text Driver (.txt; .csv)};" & _ "Dbq=" & strFolder & ";" & _ "Extensions=asc,csv,tab,txt;" On Error GoTo 0 If cn.State <> adStateOpen Then Exit Sub Set rs = New ADODB.Recordset On Error Resume Next rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText On Error GoTo 0 If rs.State <> adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later 'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
该过程可以这样使用:
Sub TestGetTextFileData() Application.ScreenUpdating = False Workbooks.Add GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3") ' GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _ "C:\FolderName", Range("A3") Columns("A:IV").AutoFit ActiveWorkbook.Saved = True End Sub
将filename.txt替换为要从中获取数据的文本文件的名称。
将C:\ FolderName替换为保存文本文件的文件夹的名称。
文本文件中的第一行将用作列标题/字段名称。
带有datwa的每一列都必须用“控制面板”的区域设置中使用的列表分隔符分隔。在挪威,这通常是分号(;),在其他国家/地区则可以是逗号(,)。
单击此链接,您将找到过程RS2WS。
该宏示例假定您的VBA项目已添加对ADO对象库的引用。
您可以通过在VBE中选择“工具”,“引用”并选择“ Microsoft ActiveX数据对象x.x对象库”来执行此操作。