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

VBA Excel copy text file to worksheet

提问by Jesse Smothermon

I'm trying to take statistics of a specific column in a text file and I thought the best way to do it might be to copy all contents from the text file into an excel worksheet and then count it from there (otherwise I would need to try and read only that one line directly from the excel file). Here's the code of what I have so far:

我正在尝试获取文本文件中特定列的统计信息，我认为最好的方法可能是将文本文件中的所有内容复制到 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

Thank you,

谢谢，

Jesse Smothermon

杰西·斯莫瑟蒙

采纳答案by stema

I am doing a similar thing, here is my sub for this:

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

I open a txt file with |as separator. Then copy the content of the sheet to my destination workbook (global variable). Then I close the first workbook that contains the original txt file without saving.

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

The code for the Workbooks.OpenText is basically from recording a macro and adapting it to my needs.

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

回答by Jesse Smothermon

I found a bit of code that does the copying and it seems to be correct (only tested once though, sorry)

我发现了一些进行复制的代码，它似乎是正确的（虽然只测试过一次，抱歉）

Sorry for posting this question, it was just that the code I was finding wasn't working correctly but I was able to figure it out.

很抱歉发布这个问题，只是我发现的代码无法正常工作，但我能够弄清楚。

'' 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

Thank you,

谢谢，

Jesse Smothermon

杰西·斯莫瑟蒙