将文本文件复制到Excel工作表的VBA代码

Question

我正在尝试统计文本文件中特定列的数据，我认为最好的方法是将文本文件的所有内容复制到Excel工作表中，然后从那里进行统计（否则我需要直接从Excel文件中尝试读取该行）。以下是我目前的代码：

Dim filePath As String
Dim currentValue As String
Dim iRow As Long
Dim iCol As Long
Dim badAddress As Long
Dim coverageNoListing As Long
Dim activeListing As Long
Dim noCoverageNoListing As Long
Dim inactiveListing As Long
Dim fso As Object
Dim f As Object

'' filePath would include entire file name (picked from a browser button)
filePath = ActiveSheet.Range("B2").Text

'' Makes sure there isn't a sheet named "Temp_Text_File"
For Each testSheet In ActiveWorkbook.Worksheets
    If testSheet.Name Like "Temp_Text_File" Then flag = True: Exit For
Next

'' If there is a sheet named "Temp_Text_File" it will be deleted
If flag = True Then
    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("Temp_Text_File").Delete
    Application.DisplayAlerts = True
End If

'' Recreate sheet
Sheets.Add.Name = "Temp_Text_File"
'' Here I would want to copy everything (similar to manually doing "Ctrl+A" then "Ctrl+C") from the text file

'' Then paste into worksheet (similar to manually doing "Ctrl+V") within this created worksheet range("A1")

'' Delete at the end (user has no need for it)
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Temp_Text_File").Delete
Application.DisplayAlerts = True

谢谢，

杰西·斯莫瑟蒙

- Jesse Smothermon

Answer 1

我正在做类似的事情，这是我的子程序：

我使用|作为分隔符打开一个txt文件。然后将工作表的内容复制到我的目标工作簿（全局变量）。然后关闭包含原始txt文件的第一个工作簿，不保存。

Workbooks.OpenText的代码基本上是从录制宏并根据我的需求进行调整。

Sub ImportTextFile(path As String)
    Dim SheetName As String
    Dim TMPWorkBook As Workbook
    Dim FilePath As String
    Dim TxtFilePath As String
    Dim TxtFileName As String

    Set WB = ThisWorkbook

    SheetName = "Test_Result"
    TxtFileName = path & "file.txt"

    Workbooks.OpenText FileName:= _
        TxtFileName _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), _
        Array(2, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", _
        TrailingMinusNumbers:=True

    Set TMPWorkBook = ActiveWorkbook
    TMPWorkBook.Sheets("file").Select
    Cells.Select
    Selection.Copy

    ResultWB.Activate

    ResultWB.Sheets(SheetName).Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveSheet.Range("A1").Select
    TMPWorkBook.Close savechanges:=False

End Sub

- stema

---

好的，我完全明白了，我看到了我所做的复制和粘贴，你的代码可能更好，因为其他人使用它们可以稍微改变代码以更好地满足他们的需求，而这是我的代码所不能做到的。谢谢你的回复。 - Jesse Smothermon

Answer 2

我找到了一段代码，它似乎可以正确地进行复制（虽然只测试过一次，抱歉）。

很抱歉发这个问题，只是因为我找到的代码没有正常工作，但我最终还是解决了。

'' The sheet is added here
Sheets.Add.Name = "Temp_Text_File"
'' Going through the code I think when you add a sheet it's automatically targetted, but this is a precaution -- makes "Temp_Text_File" the active sheet
ActiveWorkbook.Sheets("Temp_Text_File").Activate
'' Next three lines open and copy text file (not physically opened.... just opened to read)
Set fso = CreateObject("scripting.FileSystemObject")
Set f = fso.GetFile(filePath)
f.Copy (filePath)
'' This paste method will start at range "A1"
ActiveSheet.Paste

谢谢您，

Jesse Smothermon

- Jesse Smothermon

---

如果您的txt文件内容是由某些字符（逗号、分号等）分隔的，则应使用Workbooks.OpenText方法，以便将值放入正确的列中。（请参见我的解决方案） - stema

是的，这就是为什么我选择了你的答案而不是我的原因，你的答案最终更加灵活。 - Jesse Smothermon