Автоматическое чтение данных из отдельных файлов Excel

Question

Скажем, у меня есть папка с файлами Excel с предсказуемыми именами и форматированием. Как я могу автоматически прочитать их все в отдельный файл Excel? Я приветствую ответы VBA, но только те, которые достаточно полны, чтобы реализовать их без опыта. Я знаю, что версия может дать некоторую разницу в поведении, я лично работаю с Excel 2007.

Подробный пример

Чтобы нарисовать детальную картину, представьте, что у меня есть файлы с именами, приведенными ниже, с некоторым известным номером.

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

Для простоты рассмотрим, что все они имеют один лист с именем Sheet1 и только одно целочисленное значение в A1. В этой же папке я хотел бы иметь файл Excel, в котором есть столбец с целым числом из каждого из этих файлов. Просто чтобы быть абсолютно уверенным, что мой запрос ясен на 100%, я хочу файл, который выглядит следующим образом:

эта проблема

Где столбец Значение был автоматически получен из соответствующих файлов.

Что не работает

Я назову это "решением ручной связи". Это решение терпит неудачу, потому что оно требует ввода для всех ячеек. Если у меня есть 1000 файлов с правительственного сайта, это не практичный вариант.

На изображении выше перейдите в ячейку B2
Тип = 1.xls!a1
Нажмите ввод
Повторите логически для остальных 3 строк

Я назову это следующим "косвенным провалом". Это работает, но только если другой файл открыт во время вычисления значений. Опять же, это также нецелесообразно, если открытие 1000 файлов Excel может привести к сбою компьютера, не говоря уже о необходимости закрыть столько файлов.

На изображении выше перейдите в ячейку B2
Type =INDIRECT(A2&"!A1")
Нажмите Enter

Кажется, что этот подход приближает меня к решению проблемы, но я не совсем понимаю, как заставить его делать то, что я хочу. О, мне также нужно указать, что использование полного имени файла также не решает проблему, то есть набрав ='C:\[1.xls]Sheet1'!A1 имеет ту же проблему, что файлы должны быть открыты, чтобы он работал (при условии, что 1.xls хранится на верхнем уровне диска C.)

Ссылки, которые не совсем исправить

Вы можете найти некоторые другие попытки решения этой проблемы в общем Интернете. Один, например, включает рабочие листы (не файлы), непрозрачен в том, что делает, и не полностью описывает, как реализовать решение.

Самый близкий ответ, который я нашел на Super User, был:Могу ли я использовать SQL для построения таблицы данных Excel из других файлов Excel? Это не удовлетворяет тому, что я ищу, потому что я не особенно хочу решение SQL, и я хочу что-то настолько простое, насколько это возможно. По этой же причине я попытался предложить свои неудачные решения, поэтому мы не будем спешить с теми решениями, которые я уже видел.

Это мой первый вопрос о суперпользователе, и я считаю, что это правильное место, чтобы задавать такие вопросы об Excel, потому что мой вопрос, который мы используем, ориентирован.

score 3 · Accepted Answer · 2011-03-09T19:38:42

Извините, если я попросил слишком много разъяснений. Я привык отвечать на вопрос, а потом мне говорили, что они хотят задать что-то другое.

Попробуй это:

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

Я надеюсь, что я добавил достаточно объяснений. Спросите, нужно ли вам больше.

score 1 · Answer 2 · 2011-03-09T19:38:42

посмотрите здесь: http://www.ibm.com/developerworks/linux/library/l-pexcel/ Я использовал эти (а также примеры CPAN) в качестве отправной точки для моих приключений в Excel. Я создал 3 книги Excel, содержащие число в 0,0 на листе 1. Используя фрагмент кода с веб-сайта 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);
  }
 }
}

score 0 · Answer 3 · 2011-03-09T19:38:42

Привет я искал, чтобы решить аналогичную проблему, собирая данные из нескольких файлов.

Я прибег к установке надстройки Morefunc в Excel (примечание: эта ссылка напрямую загружает zip-файл с надстройкой). Он имеет функцию INDIRECT.EXT, которая будет считывать информацию, подобную INDIRECT, но без необходимости открывать файлы! Вы можете прочитать больше здесь: http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/. Я скопировал инструкции из этого поста ниже.

Вы можете следовать процессу, указанному ниже, чтобы извлечь данные из определенных ячеек нескольких закрытых файлов Excel.

Все файлы в папке отчетов Survey должны быть закрыты

В листе 1 из Book1.xlsx введите C4, R9 и C16 в A1:C1

От ячейки D3 вниз введите имена всех файлов Excel (вместе с их расширением, например, xls, xlsx) в папке отчета Survey. Чтобы автоматически создать этот список имен файлов Excel, обратитесь к следующему сообщению.

Загрузите и установите надстройку Morefunc отсюда. Это дополнение позволит нам использовать функцию INDIRECT.EXT. Для MS Excel 2007 и более ранних версий можно просто загрузить, установить и использовать это дополнение. В Excel 2007 после установки этого надстройки он появится в разделе «Формулы»> «Больше функций». Для Excel 2010 процесс установки этого дополнения выглядит следующим образом:

а. Разархивируйте загруженную папку и дважды щелкните файл установки

б. Перейдите в следующую папку C:\Program Files\Morefunc и скопируйте три файла - Morefunc, Morefunc11 и Morefunc12

с. Перейдите в следующую папку C:\Program Files\Microsoft Office\Office14\Library и вставьте туда файлы

д. Открыть MS Excel 2010

е. Перейдите в Файлы> Параметры> Надстройки> Управление надстройками Excel> Перейти

е. Установите следующие три флажка - Morefunc (функции надстроек), Morefunc Tools и Morefunc12

г. Нажмите на ОК

час Закройте MS Excel 2010 и снова откройте

я. Morefunc теперь должно отображаться на вкладке "Формулы" на ленте

В ячейке A3 листа sheet1 Book1.xlsx введите следующую формулу и скопируйте до C3 и вниз

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

Вы заметите, что даже если все файлы Excel в папке отчетов Survey закрыты, функция INDIRECT.EXT будет отображать данные из соответствующих ячеек этих файлов Excel.

Поскольку данные извлекаются из закрытых файлов Excel с помощью надстройки, производительность рабочей книги будет снижена. Пожалуйста, попробуйте сначала на ограниченных файлах Excel.

Сейчас выбран русский

Автоматическое чтение данных из отдельных файлов Excel

3 ответа3

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel.

Автоматическое чтение данных из отдельных файлов Excel

3 ответа3

Всё ещё ищете ответ? Посмотрите другие вопросы с метками microsoft-excel.

Похожие