在Excel中访问和获取多个txt文件的值 [英] Access and get values from multiple txt files in Excel

问题描述

我有一个txt文件在一年中的每一天的表格（行和选项卡），即2013-01-01.txt，2013-01-02.txt等。

I have a txt file organized in tables (lines and tabs) for each day of the year, i.e. 2013-01-01.txt, 2013-01-02.txt and so on.

我想在这个文件中 VLOOKUP（）以获得价格的产品价格。我的工作表将如下所示：

I want to VLOOKUP() inside this files to get the product prices in column Price. My worksheet will look like this:

+------------+------------+------------+------------+------------+
|   Price    | 2013-12-26 | 2013-12-27 | 2013-12-30 | 2013-12-31 |
+------------+------------+------------+------------+------------+
| Watermelon | 1          | 1.5        | 1.35       | 1.85       |
| Botato     | 0.55       | 0.65       | 0.55       | 0.8        |
+------------+------------+------------+------------+------------+

我可以想象两个选项：

1. 使用打开哪个文件Workbooks.Open（）获取我需要的值，然后关闭文件：

1. Open which file using Workbooks.Open() get the values that I need then close file:

Workbooks.Open (Path)
'Get values
Workbooks.Close

在

Open update files in

With ActiveSheet.QueryTables.Add(Connection:= ...)
    .name = path
End With
'Get values
ActiveWorkbook.Connections(Path).Delete

有人以另一种方式思考实现吗？

Does someone think in another way to implement it? Which one is faster and better to understand?

推荐答案

如果要打开Excel中的每个文件而不使用TextStream，那么以下代码应该可以帮助您：

If you want to open each file in Excel and not using TextStream, then the following code should help you:

Sub OpenTxtFileInExcel()

Dim basicWorkbook, actWorkbook As Workbook
Dim basicSheet, actSheet As Worksheet
Dim filePath As String
Dim actDate As Date
Dim searchedProduct As String
Dim counterColumn, counterRow As Integer
Dim products As Variant
products = Array("Lemon", "Mango", "Durian")

Set basicWorkbook = Application.ActiveWorkbook
Set basicSheet = basicWorkbook.Worksheets(1)

actDate = CDate("01-01-2013")
counterColumn = 0

Do While actDate < CDate("01-01-2014")
    filePath = Format(actDate, "yyyy-MM-dd") & ".txt"
    Workbooks.OpenText Filename:=filePath, Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
        True, Space:=False, Other:=True, OtherChar:=False

    Set actWorkbook = Application.ActiveWorkbook
    Set actSheet = basicWorkbook.Worksheets(1)

    basicSheet.Range("B1").Offset(0, counterColumn).Value = Format(actDate, "yyyy-MM-dd")
    For counterRow = 0 To UBound(products)
        basicSheet.Range("A2").Offset(counterRow, 0).Value = products(counterRow)
        basicSheet.Range("B2").Offset(counterRow, counterColumn).Value = _
            Application.WorksheetFunction.VLookup(products(counterRow), actSheet.Range("A:B"), 2, False)
    Next counterRow

    actWorkbook.Close

    counterColumn = counterColumn + 1
    actDate = DateAdd("d", 1, actDate)
Loop

End Sub 

命令 Workbooks.OpenText 非常依赖于您的文本文件的结构，您应该记录此命令（在Excel 2007/2010：视图>宏>记录Macor ...），当通过Excel打开文本文件在某种程度上，vlookup可以正常工作。

The command Workbooks.OpenText is very much dependend on the structure of your text files and you should record this command (in Excel 2007/2010: View > Macros > Record Macor ...), when opening the text file just via Excel in a way that the vlookup can work.

这篇关于在Excel中访问和获取多个txt文件的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！