使用 VBA 将 Excel txt 文件导入（特定列）到表格中

Excel txt file import (specific columns) into a table with VBA

提问by Samuel

I found this forum while I was trying to sort out my VBA code. I only have very little experience in VBA programming so far. That is why I have researched since two days for a solution for my problem, without success. However, I am very interested in impoving my programming skillsa and I hope you can help me to do so.

我在整理 VBA 代码时发现了这个论坛。到目前为止，我只有很少的 VBA 编程经验。这就是为什么我两天以来一直在研究解决我的问题的方法，但没有成功。然而，我对提高我的编程技能非常感兴趣，我希望你能帮助我这样做。

Basically I would like to import a TXT file into the active Excel workbook, sheet named "DataImport" at the end of the table named "TblDataImport".

基本上我想将一个 TXT 文件导入到活动的 Excel 工作簿中，在名为“TblDataImport”的表的末尾，名为“DataImport”的工作表。

The TXT file consists of 13 columns which are separated by tabs:

TXT 文件由 13 列组成，这些列由制表符分隔：

1. row: CompanyName
2. row: DateNameCustomerGroup CustomerNo SalesOrderItemNumber ItemGroup LineStatus QuantityPriceDiscountDiscountPercentage NetAmount
3. row: All corresponding values...

1. 行：公司名称
2. 行：日期名称CustomerGroup CustomerNo SalesOrderItemNumber ItemGroup LineStatus Quantity Price DiscountDiscountPercentage NetAmount
3. 行：所有对应的值...

I dont't need to import the first and the second row since the table where I would like to import the data to aready exists including headings. Also I only need 7 out of the 13 columns, those that are formated in bold. It would be perfect if the user could choose the text file using an open file dialogue.

我不需要导入第一行和第二行，因为我想将数据导入到 aready 的表存在，包括标题。此外，我只需要 13 列中的 7 列，即以粗体格式显示的列。如果用户可以使用打开的文件对话框选择文本文件，那将是完美的。

If I open the TXT file manually and paste its content into Excel, it is already included
in the right columns. So there should not be any pitfalls with regard to formatting.

如果我手动打开 TXT 文件并将其内容粘贴到 Excel 中，它已包含
在正确的列中。所以在格式化方面不应该有任何陷阱。

I am curious about the solutions you come up with.

我很好奇你提出的解决方案。

With the macro recorder I can only fulfil some of my conditions:

使用宏记录器我只能满足我的一些条件：

Sub DataImport()  
' DataImport Makro  
    Sheets("DataImport").Select  
    With ActiveSheet.QueryTables.Add(Connection:= _  
        "TEXT;C:\Users\Sales.txt", Destination:=Range _  
        ("$A"))  
        .Name = "AxaptaSales"  
        .FieldNames = True  
        .RowNumbers = False  
        .FillAdjacentFormulas = False  
        .PreserveFormatting = True  
        .RefreshOnFileOpen = False  
        .RefreshStyle = xlInsertDeleteCells  
        .SavePassword = False  
        .SaveData = True  
        .AdjustColumnWidth = True  
        .RefreshPeriod = 0  
        .TextFilePromptOnRefresh = False  
        .TextFilePlatform = 850  
        .TextFileStartRow = 1  
        .TextFileParseType = xlDelimited  
        .TextFileTextQualifier = xlTextQualifierDoubleQuote  
        .TextFileConsecutiveDelimiter = False  
        .TextFileTabDelimiter = True  
        .TextFileSemicolonDelimiter = False  
        .TextFileCommaDelimiter = False  
        .TextFileSpaceDelimiter = False  
        .TextFileColumnDataTypes = Array(4, 1, 9, 9, 1, 9, 9, 9, 1, 1, 1, 9, 1)  
        .TextFileTrailingMinusNumbers = True  
        .Refresh BackgroundQuery:=False  
    End With  
End Sub  

However this code only pastes all columns into cell A1 (not at the end of the table). Also it includes the first and second row as well as the columns I don't need.

但是，此代码仅将所有列粘贴到单元格 A1 中（不在表格末尾）。它还包括第一行和第二行以及我不需要的列。

回答by Gazza

If you add the following to the code above this will add it to the end of the data already in the worksheet

如果您将以下内容添加到上面的代码中，这会将其添加到工作表中已有数据的末尾

Sub DataImport()

   Dim LastRow As Integer
   Dim LastRow2 As integer
    LastRow = Range("A65536").end(xlup).row
    LastRow = LastRow + 1  
 ' DataImport Makro  
Sheets("DataImport").Select  
With ActiveSheet.QueryTables.Add(Connection:= _  
    "TEXT;C:\Users\Sales.txt", Destination:=Range _  
    ("$A" & LastRow))  
    .Name = "AxaptaSales"  
    .FieldNames = True  
    .RowNumbers = False  
    .FillAdjacentFormulas = False  
    .PreserveFormatting = True  
    .RefreshOnFileOpen = False  
    .RefreshStyle = xlInsertDeleteCells  
    .SavePassword = False  
    .SaveData = True  
    .AdjustColumnWidth = True  
    .RefreshPeriod = 0  
    .TextFilePromptOnRefresh = False  
    .TextFilePlatform = 850  
    .TextFileStartRow = 1  
    .TextFileParseType = xlDelimited  
    .TextFileTextQualifier = xlTextQualifierDoubleQuote  
    .TextFileConsecutiveDelimiter = False  
    .TextFileTabDelimiter = True  
    .TextFileSemicolonDelimiter = False  
    .TextFileCommaDelimiter = False  
    .TextFileSpaceDelimiter = False  
    .TextFileColumnDataTypes = Array(4, 1, 9, 9, 1, 9, 9, 9, 1, 1, 1, 9, 1)  
    .TextFileTrailingMinusNumbers = True  
    .Refresh BackgroundQuery:=False  
End With
Range("A" & LastRow & ":A" & LastRow + 1).entireRow.delete
LastRow2 = Range("A65536").end(xlup).row
Range("H" & LastRow & ":M" & LastRow2).entirecolumn.delete  

End Sub

结束子

I think this should do as you require(But I am still a novice myself)

我认为这应该按照您的要求进行（但我自己仍然是新手）