☰

自动从单独的 Excel 文件读取数据

tag-icon • tag-icon microsoft-excel
自动从单独的 Excel 文件读取数据

假设我有一个包含可预测文件名和格式的 Excel 文件的文件夹。我如何才能自动将所有文件读入单独的 Excel 文件？我欢迎 VBA 答案，但只欢迎那些足够完整、无需经验即可实现的答案。我知道版本在行为上可能会有所不同，我个人使用的是 Excel 2007。

详细示例

为了绘制详细的图景，假设我有一些文件，其名称如下，一直到某个已知数字。

  • 1.xls
  • 2.xls
  • 3.xls
  • 4.xls

为简单起见，假设它们都有一个名为 Sheet1 的工作表，并且 A1 中只有一个整数值。在同一个文件夹中，我希望有一个 Excel 文件，其中有一列包含来自每个文件的整数。为了确保我的请求 100% 清楚，我想要一个如下所示的文件：

问题

其中 Value 列是从相应文件中自动获取的。

什么不起作用

我将称之为“手动链接解决方案”。此解决方案不够完善，因为它需要输入所有单元格。如果我有来自政府网站的 1000 个文件，这不是一个实用的选择。

  • 在上图中，转到单元格 B2
  • 类型 =1.xls!a1
  • 按 Enter 键
  • 对其他 3 行重复上述操作

我将下一个称为“间接失败”。它有效，但前提是在计算值时其他文件处于打开状态。同样，如果打开 1000 个 Excel 文件会导致计算机崩溃，更不用说必须关闭那么多文件，那么这也是不切实际的。

  • 在上图中，转到单元格B2
  • 类型=INDIRECT(A2&"!A1")
  • 打Enter

这种方法似乎让我非常接近解决方案，但我不知道如何让它做我想做的事情。哦，我还需要指定使用完整文件名也不能解决问题，这意味着，输入='C:\[1.xls]Sheet1'!A1有同样的问题，即必须打开文件才能工作（假设 1.xls 存储在 C 盘顶层）。

无法完全解决问题的链接

您可以在互联网上找到针对此问题的其他一些尝试。一例如，涉及工作表（而不是文件），其功能不透明，并且没有完整描述如何实施解决方案。

我在超级用户上找到的最接近的答案是我可以使用 SQL 从其他 Excel 文件构建 Excel 数据表吗？ 这并不能满足我的要求，因为我并不特别想要 SQL 解决方案，我想要尽可能简单直接的解决方案。这也是我尝试提供失败解决方案的原因，这样我们就不会因为已经见过的半成品解决方案而烦恼。

这是我在超级用户上的第一个问题，我相信这是提出有关 Excel 的此类问题的正确地方因为我的问题我们使用导向。

答案1

抱歉，我问了太多问题。我习惯于回答一个问题，然后被告知他们想问的是其他问题。

尝试这个：

Option Explicit
Sub ReadFilesInSequence()

  Dim FileName As String
  Dim FileNumber As Long
  Dim PathCrnt As String
  Dim RowDestCrnt As Long
  Dim SheetDest As String
  Dim TgtValue As String
  Dim WBookSrc As Workbook

  PathCrnt = ActiveWorkbook.Path & "\AlanSE"

  SheetDest = "AlanSE"
  RowDestCrnt = 1

  With Worksheets(SheetDest)
    ' Delete current contents of destination sheet
    .Cells.EntireRow.Delete
  End With

  FileNumber = 1

  Do While True

    FileName = Dir$(PathCrnt & "\" & FileNumber & ".xls")
    If FileName = "" Then
      ' File does not exist
      Exit Do
    End If

    Set WBookSrc = Workbooks.Open(PathCrnt & "\" & FileName)
    With WBookSrc.Worksheets("Sheet1")
      TgtValue = .Cells(1, "A").Value
    End With
    WBookSrc.Close SaveChanges:=False
    With Worksheets(SheetDest)
      .Cells(RowDestCrnt, "A").Value = FileName
      .Cells(RowDestCrnt, "B").Value = TgtValue
    End With
    RowDestCrnt = RowDestCrnt + 1

    FileNumber = FileNumber + 1

  Loop

End Sub

我希望我已经添加了足够的解释。如果您需要更多信息，请询问。

答案2

看看这里：http://www.ibm.com/developerworks/linux/library/l-pexcel/ 我使用这些（以及 CPAN 示例）作为我的 Excel 冒险的起点。我创建了 3 个 Excel 工作簿，其中包含 sheet1 中的 0,0 中的数字。使用 IBM 网站上的代码片段（减去一些打印语句），我得到：

shiny:exceltest fl$ for f in $( find . -name book\*.xls ); do ../parse-excel.pl $f ; done
--------- SHEET:sheet1
( 0 , 0 ) =>3.1416
--------- SHEET:sheet1
( 0 , 0 ) =>678
--------- SHEET:sheet1
( 0 , 0 ) =>42

为此，我使用了这段 perl 代码片段，厚颜无耻地从 developerworks 网站上窃取而来：

#!/opt/local/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "--------- SHEET:", $oWkS->{Name}, "\n";
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
  }
 }
}

答案3

你好，我正在寻找解决类似问题的方法，从多个文件中收集数据。

我只能安装Excel 的 Morefunc 插件（注意：此链接直接下载带有插件的 zip 文件）。它有一个名为 INDIRECT.EXT 的功能，可以像 INDIRECT 一样读取信息，但无需打开文件！您可以在此处阅读更多信息：http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/。我已将那篇文章中的说明复制如下。

您可以按照下面提到的过程从多个封闭的 Excel 文件的特定单元格中提取数据。

  1. 调查报告文件夹中的所有文件都应关闭
  2. 在 Book1.xlsx 的 sheet1 中，在 A1:C1 中输入 C4、R9 和 C16
  3. 从单元格 D3 向下，输入调查报告文件夹中所有 Excel 文件的名称（以及它们的扩展名，即 xls、xlsx）。要自动生成此 Excel 文件名称列表，请参阅以下内容邮政。
  4. 从以下网址下载并安装 Morefunc 插件这里。此插件将允许我们使用 INDIRECT.EXT 函数。对于 MS Excel 2007 及更早版本，只需下载、安装和使用此插件即可。在 Excel 2007 中，安装此插件后，它将出现在公式 > Morefunc 下。对于 Excel 2010，安装此插件的过程如下：

a. 解压下载的文件夹并双击安装文件

b. 导航到以下文件夹 C:\Program Files\Morefunc 并复制三个文件 - Morefunc、Morefunc11 和 Morefunc12

c. 导航到以下文件夹 C:\Program Files\Microsoft Office\Office14\Library 并将文件粘贴到那里

d.打开 MS Excel 2010

e. 转到文件 > 选项 > 插件 > 管理 Excel 插件 > 转到

f. 勾选以下三个框 - Morefunc（附加功能）、Morefunc Tools 和 Morefunc12

g. 点击“确定”

h. 关闭 MS Excel 2010 并重新打开

i. Morefunc 现在应该出现在功能区中的“公式”选项卡下

  1. 在 Book1.xlsx 的 Sheet1 的 A3 单元格中，输入以下公式并复制至 C3 并向下

=INDIRECT.EXT("'C:\Users\Ashish\Desktop\test\Survey Reports["&$D3&"]Sheet1'!"&A$1)

您会发现，即使关闭了“调查报告”文件夹中的所有 Excel 文件，INDIRECT.EXT 函数仍会显示这些 Excel 文件各自单元格的数据。

由于数据是通过插件从封闭的 Excel 文件中提取的，因此工作簿的性能会受到影响。请先在有限的 Excel 文件上尝试此操作。

相关内容