VBA在同一工作簿中导入两个txt文件 [英] VBA import two txt files in the same workbook

问题描述

是否可以在同一工作簿中导入两个txt文件?

Is there a way to import two txt files in the same workbook?

我可以使用

ActiveSheet.QueryTables.Add(Connection:= _

但是，无论何时添加另一个，它都会忽略第一个txt文件.

However whenever I add another it just ignores the first txt file.

非常感谢

推荐答案

如果要在同一工作表中加载两个文本文件，则可以尝试使用ADO.
像这样:

If you want to load both Text Files in the same sheet, you can try using ADO.
Something like:

Sub conscious()
    Dim con As ADODB.Connection, rec As ADODB.Recordset
    Set con = New ADODB.Connection: Set rec = New ADODB.Recordset

    Dim datasource As String, txtfiles As Variant _
        , txt1 As String, txt2 As String
    txtfiles = Application.GetOpenFilename(FileFilter:="CSV Files, *.csv", _
        MultiSelect:=True)
    datasource = Left(txtfiles(1), InStrRev(txtfiles(1), "\"))
    txt1 = "[" & Dir(txtfiles(1)) & "]"
    txt2 = "[" & Dir(txtfiles(2)) & "]"

    Dim sconnect As String
    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & datasource & _
                ";Extended Properties=""Text;HDR=YES;FMT=Delimited(,)"";"
    con.Open sconnect

    Dim sqlstr As String
    sqlstr = "SELECT * FROM " & txt1 & _
            "UNION ALL SELECT * FROM " & txt2
    rec.Open sqlstr, con, adOpenStatic, adLockReadOnly

    With Sheets("Sheet1")
        Dim lrow As Long
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        If lrow > 1 Then .Range("A2:J" & lrow).ClearContents
        .Range("A2").CopyFromRecordset rec
    End With
    Application.ScreenUpdating = True

    rec.Close: con.Close
    Set rec = Nothing: Set con = Nothing
End Sub

基本上，这会提示您选择文件.
我使用了 CSV 文件作为示例.调整以适合.
您可以选择尽可能多的文件，但这仅处理前2个文件.
如果您需要处理更多内容，则必须遍历所选文件.HTH

Basically this prompts you to select files.
I used a CSV file as sample. Adjust to suit.
You can select as many files, but this handles the first 2 files only.
If you need to handle more, you'll have to loop through the selected files. HTH

这篇关于VBA在同一工作簿中导入两个txt文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！