Выгрузка в файл excel в vba
Метод ExportAsFixedFormat сохраняет рабочую книгу Excel или выбранную группу листов этой книги в один PDF-файл. Чтобы экспортировать каждый лист в отдельный файл, необходимо применить метод ExportAsFixedFormat к каждому сохраняемому листу.
Синтаксис
Expression . ExportAsFixedFormat ( Type , FileName , Quality , IncludeDocProperties , IgnorePrintAreas , From , To , OpenAfterPublish , FixedFormatExtClassPtr )
Expression – это выражение, представляющее объект Workbook, Worksheet или Range.
Параметры
Единственный обязательный параметр – Type, остальные можно не указывать – в этом случае будут применены значения по умолчанию.
Параметр | Описание |
---|---|
Type | Задает формат файла для экспорта книги или листа: xlTypePDF(0) – сохранение в файл PDF; xlTypeXPS(1) – сохранение в файл XPS*. |
FileName | Задает имя файла. При указании полного пути, файл будет сохранен в указанную папку, при указании только имени – в папку по умолчанию (в Excel 2016 – «Документы»). Если имя не задано (по умолчанию), файл будет сохранен с именем экспортируемой книги. |
Quality | Задает качество сохраняемых электронных таблиц: xlQualityMinimum(1) – минимальное качество; xlQualityStandard(0) – стандартное качество (по умолчанию). |
IncludeDocProperties | Включение свойств документа Excel в PDF: True(1) – включить; False(0) – не включать; мне не удалось обнаружить разницу и значение по умолчанию. |
IgnorePrintAreas | Указывает VBA, следует ли игнорировать области печати, заданные на листах файла Excel: True(1) – игнорировать области печати; False(0) – не игнорировать области печати (по умолчанию). |
From** | Задает номер листа книги Excel, с которого начинается экспорт. По умолчанию сохранение в PDF начинается с первого листа книги. |
To** | Задает номер листа книги Excel, на котором заканчивается экспорт. По умолчанию сохранение в PDF заканчивается на последнем листе книги. |
OpenAfterPublish | Указывает VBA на необходимость открыть созданный файл PDF средством просмотра: True(1) – открыть файл PDF для просмотра; False(0) – не открывать файл PDF для просмотра (по умолчанию). |
FixedFormatExtClassPtr | Указатель на класс FixedFormatExt (игнорируем этот параметр). |
* XPS – это редко использующийся фиксированный формат файлов, разработанный Microsoft, который похож на PDF, но основан на языке XML.
** Применимо только к книге (Workbook.ExportAsFixedFormat), при экспорте листа (Worksheet.ExportAsFixedFormat) указание параметров From и/или To приведет к ошибке.
В этой статье рассматриваются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы можно было выбрать оптимальное решение.
Дополнительная информация
Чаще всего для передачи данных в Excel используется автоматизация. Автоматизация обеспечивает максимальную гибкость при указании расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью службы автоматизации можно использовать несколько подходов для передачи данных:
- Передача ячейки данных по ячейкам
- Передача данных в массиве в диапазон ячеек
- Передача данных из набора записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
- Создание таблицы QueryTable на Excel, содержащей результат запроса к источнику данных ODBC или OLEDB
- Передача данных в буфер обмена, а затем вставка содержимого буфера обмена в Excel листа
Существуют также методы, которые можно использовать для передачи данных в Excel, которые не обязательно требуют автоматизации. Если вы работаете на стороне сервера приложений, это может быть хорошим подходом для того, чтобы отойдите от клиентов к массовой обработке данных. Для передачи данных без автоматизации можно использовать следующие методы:
- Передача данных в текстовый файл с разделителями-табуляции или запятыми, который Excel позже можно проанализировать в ячейки на листе.
- Передача данных на лист с помощью ADO
- Передача данных в Excel с помощью динамических Exchange данных (DDE)
В следующих разделах приведены более подробные сведения о каждом из этих решений.
Примечание При использовании Microsoft Office Excel 2007 при сохранении книг можно использовать новый формат Excel книги 2007 (*.xlsx). Для этого найдите следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных Northwind не включена в Office 2007 по умолчанию. Однако базу данных Northwind можно скачать из Microsoft Office Online.
Использование службы автоматизации для передачи данных по ячейкам
С помощью службы автоматизации можно передавать данные на лист по одной ячейке за раз:
Передача ячеек данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете размещать данные в любом месте книги и условно форматировать ячейки во время выполнения. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книге. Каждый объект Range, полученный во время выполнения, приводит к запросу интерфейса, чтобы передача данных таким образом была медленной. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение в 64 КБ на запросы интерфейса. Если вы достигнете или превысите это ограничение в 64 КБ для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникнуть ошибки, указывающие на нехватку памяти.
Еще раз перенос ячеек данных допускается только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.
Дополнительные примеры кода для автоматизации Excel см. в статье об автоматизации Microsoft Excel из Visual Basic.
Использование автоматизации для передачи массива данных в диапазон на листе
Массив данных можно передать в диапазон из нескольких ячеек одновременно:
При передаче данных с помощью массива, а не ячейки по ячейкам вы можете реализовать огромное повышение производительности с большим объемом данных. Рассмотрим следующую строку из приведенного выше кода, который передает данные в 300 ячеек листа:
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейкам требуются запросы 300 интерфейсов к объектам Range. По возможности вы можете воспользоваться преимуществами массовой передачи данных и сокращения количества запросов интерфейса.
Использование автоматизации для передачи набора записей ADO в диапазон листа
Excel 2000 г. появился метод CopyFromRecordset, который позволяет передавать набор записей ADO (или DAO) в диапазон на листе. В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и передать содержимое таблицы Orders в образце базы данных Northwind с помощью метода CopyFromRecordset.
Примечание При использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но его можно использовать только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статье о передаче данных из набора записей ADO в Excel с помощью автоматизации.
Использование автоматизации для создания таблицы QueryTable на листе
Объект QueryTable представляет таблицу, созданную на основе данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать таблицу QueryTable, просто указав строку подключения для OLEDB или источника данных ODBC вместе с SQL строкой. Excel несет ответственность за создание набора записей и его вставку на лист в указанном расположении. Использование таблиц QueryTables дает несколько преимуществ по сравнению с методом CopyFromRecordset:
- Excel обрабатывает создание набора записей и его размещение на листе.
- Запрос можно сохранить с помощью таблицы QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
- При добавлении новой таблицы QueryTable на лист можно указать, что данные, уже существующие в ячейках листа, будут сдвинуты в соответствии с новыми данными (дополнительные сведения см. в свойстве RefreshStyle).
В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 для создания новой таблицы QueryTable на листе Excel с помощью данных из образца базы данных Northwind:
Использование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек на листе, можно скопировать строку, в которой столбцы разделены символами табуляции, а строки разделены символами каретки. В следующем коде показано, Visual Basic использовать объект буфера обмена для передачи данных в Excel:
Создание текстового файла с разделителями, который Excel анализировать по строкам и столбцам
Excel могут открывать файлы с разделителями-табуляции или запятыми и правильно анализировать данные в ячейках. Эту функцию можно использовать, если вы хотите перенести большой объем данных на лист, используя небольшой объем (при наличии) службы автоматизации. Это может быть хорошим подходом для клиент-серверного приложения, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте с помощью службы автоматизации, где это необходимо.
В следующем коде показано, как создать текстовый файл с разделителями-запятыми из набора записей ADO:
Обратите внимание, что при использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если текстовый файл имеет расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделен запятыми. Аналогичным образом, если файл имеет расширение .TXT, Excel автоматически анализировать файл с помощью разделителей табуляции.
В предыдущем примере кода Excel с помощью инструкции Оболочки, а имя файла использовался в качестве аргумента командной строки. В предыдущем примере автоматизация не использовалась. Однако при необходимости можно использовать минимальный объем автоматизации, чтобы открыть текстовый файл и сохранить его в Excel формате книги:
Передача данных на лист с помощью ADO
С помощью поставщика Microsoft Jet OLE DB можно добавлять записи в таблицу в существующей Excel книге. Таблица в Excel является просто диапазоном с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки — записи. Ниже показано, как создать книгу с пустой таблицей с именем MyTable.
Excel 97, Excel 2000 и Excel 2003
Запустите новую книгу в Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
В меню "Вставка" выберите "Имена", а затем выберите "Определить". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать следующий код:
Excel 2007
В Excel 2007 запустите новую книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
На ленте откройте вкладку "Формулы " и выберите команду "Определить имя". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматируются с выравниванием по правому краю. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Обратите внимание, что при добавлении записи в ячейку или ячейки листа она перезаписывает все данные, которые ранее были в этих ячейках. Другими словами, строки на листе не "помещаются вниз" при добавлении новых записей. Это следует учитывать при проектировании макета данных на листах.
Метод обновления данных на листе Excel с помощью ADO или DAO не работает в среде Visual Basic for Application в Access после установки Office 2003 с пакетом обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в статье "Как запрашивать и обновлять данные Excel с помощью ADO из ASP".
Использование DDE для передачи данных в Excel
DDE — это альтернатива автоматизации в качестве средства взаимодействия с Excel и передачи данных. Однако с появлением автоматизации и COM DDE больше не является предпочтительным методом для взаимодействия с другими приложениями и должен использоваться только в том случае, если вам не доступно другое решение.
Чтобы передать данные в Excel с помощью DDE, можно использовать метод LinkPoke для передачи данных в определенный диапазон ячеек или метод LinkExecute для отправки команд, которые Excel будут выполняться.
В следующем примере кода показано, как установить диалог DDE с Excel, чтобы можно было перенести данные в ячейки на листе и выполнить команды. В этом примере для успешной установки диалога DDE в LinkTopic Excel|MyBook.xls книга с именем MyBook.xls должна быть открыта в работающем экземпляре Excel.
При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновлено в следующем примере кода. В этом примере Text1 представляет элемент управления Text Box в Visual Basic форме:
При использовании LinkPoke с Excel указывается диапазон в нотации столбца строк (R1C1) для LinkItem. Если данные разделяются на несколько ячеек, можно использовать строку, в которой столбцы разделяются вкладками, а строки разделяются символами возврата каретки.
При использовании LinkExecute для Excel выполнения команды необходимо Excel в синтаксисе языка макросов Excel (XLM). Документация по XLM не входит в Excel 97 и более поздних версий.
DDE не рекомендуется для взаимодействия с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет больше доступа к новым функциям, Excel может предложить.
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
- Номер_файла – обязательный параметр, представляющий из себя номер, присвоенный файлу при открытии с помощью оператора Open.
- Переменные – обязательный параметр, представляющий из себя список переменных, разделенных запятой, которым присваиваются значения, считанные из файла.
- Номер_файла – обязательный параметр, представляющий из себя номер, присвоенный файлу при открытии с помощью оператора Open.
- Переменная – обязательный параметр, представляющий из себя имя переменной, объявленной как String или Variant, которой присваивается строка, считанная из файла.
- Номер_файла – обязательный параметр, представляющий из себя номер, присвоенный файлу при открытии с помощью оператора Open.
- Данные – необязательный параметр, представляющий из себя одно или несколько числовых или строковых выражений, разделенных запятой, которые нужно записать в файл.
Функция EOF
Функция EOF возвращает логическое значение True, когда достигнут конец файла, открытого для последовательного (Input) или произвольного (Random) доступа.
Синтаксис функции EOF:
Номер_файла – это номер, присвоенный файлу при открытии с помощью оператора Open.
Функция EOF используется для предупреждения ошибок, вызываемых попытками выполнить чтение после конца файла. Она возвращает значение False, пока не будет достигнут конец файла.
Примеры чтения и записи в файл
Пример 1
Открытие (или создание, если он не существует) текстового файла для чтения и записи и запись в него одной строки, состоящей из двух текстовых и одного числового значений. Файл с именем myFile1.txt будет создан в той же папке, где расположен файл Excel с кодом VBA.
Пример 2
Открытие (или создание, если он не существует) файла без расширения для чтения и записи и запись в него трех строк: двух текстовых и одной в числовом формате. Файл с именем myFile2 будет создан в той же папке, где расположен файл Excel с кодом VBA.
Так как у файла нет расширения, Windows выведет диалоговое окно для выбора открывающей его программы. Выберите любой текстовый редактор или интернет-браузер.
Пример 3
Считываем строку, разделенную на отдельные элементы, из файла myFile1.txt и записываем в три переменные, по типу данных соответствующие элементам.
Пример 4
Считываем поочередно три строки из файла myFile2 и записываем в три элемента массива, объявленного как Variant, так как в этот файл ранее были записаны две строки с текстом и одна с числом.
Сохранение одномерного массива в текстовый файл с добавлением разделителя между его элементами.
Объявляем переменную универсального типа, создаем из нее массив, заполнив ее данными с помощью функции Array:
Теперь создаем текстовый файл с помощью метода CreateTextFile. Создаем, а не открываем, для того, чтобы, если такой файл с данными уже существует, от был перезаписан новым пустым файлом.
Если переменную, предназначенную для создания нового объекта TextStream объявить явно: fl As TextStream , то станут доступны подсказки для автозаполнения свойств и методов этого объекта при написании кода.
Сохраняем информацию из массива в текстовый файл с помощью цикла For… Next:
В качестве разделителя элементов массива используется символ «;». Если точка с запятой встречается в содержимом элементов массива, следует использовать другой разделитель.
Закрываем объект TextStream и открываем созданный текстовый файл для просмотра:
Полный код процедуры VBA Excel для сохранения одномерного массива в текстовый файл:
Сохранение двумерного массива
Сохранение двумерного массива в текстовый файл с использованием в качестве разделителей точки с запятой (для элементов в одной строке) и переноса строки.
Раз у нас VBA Excel, то и заполнять двумерный массив будем данными с рабочего листа:
В результате будет создан массив с размерностью (1 to 8, 1 to 4) . Первое измерение массива соответствует строкам диапазона, в второе – столбцам.
Создаем текстовый файл (или перезаписываем существующий) и открываем связанный с ним объект TextStream для записи данных из массива:
Записываем данные из двумерного массива в текстовый файл:
Закрываем объект TextStream и открываем созданный текстовый файл для просмотра:
Полный код процедуры VBA Excel для сохранения двумерного массива в текстовый файл:
Заполнение массива из текстового файла
Заполнение одномерного массива
Обратное заполнение с помощью кода VBA Excel одномерного массива данными из созданного текстового файла "C:\test\testfile1.txt" :
Заполнение двумерного массива
Обратное заполнение с помощью кода VBA Excel двумерного массива из созданного текстового файла "C:\test\testfile2.txt" .
Копируем информацию из текстового файла в переменную myString1:
Что мы теперь имеем? Все данные из файла "C:\test\testfile2.txt" скопированы в переменную myString1 с исходной структурой: со знаком переноса строки в качестве разделителя строк и точкой с запятой в качестве разделителя столбцов. Этой информацией мы и воспользуемся для заполнения массива myArray() .
Читайте также: