Excel vba работа с файлами

Работа с внешним Excel файлом из VBA

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

Открытие файла Excel

В первой строке запускаем новый Excel, затем делаем его невидимым, в 3-й строке открываем файл fname. В последней строке получаем первый лист открытого excel-кого файла.

Альтернативный вариант открытия файла

При открытии файла можно использовать доп.параметры (приведу некоторые):

UpdateLinks — обновлять или нет внешние ссылки при открытии файла;
ReadOnly — открытие в режиме только для чтения;
Format — используемый при открытии разделитель (1 — символ tab, 2 — запятые, 3 — пробелы, 4 — точка с запятой, 5 — без разделителя, 6 — пользовательский разделитель, заданный в Delimiter);
Delimiter — пользовательский разделитель (в случае, если Format = 6);
Origin — тип операционной системы (xlMacintosh, xlWindows или xlMSDOS);
Local — использование в Excel языка такого же, как в открываемом файле.

Теперь можно выполнять какие-то действия с открытым файлом, просто обращаясь через wb и ws.

Записать книгу и закрыть

Для записи текущей книги (где находится макрос), можно использовать:

Чтобы сохранить или перезаписать книгу Excel без вопросов, можно применить такой вариант:

У метода SaveAs есть несколько параметров сохранения, с ними можно ознакомиться на сайте Microsoft.

Если нужно, можно закрыть книгу Excel без сохранения изменений таким образом:

Файловые функции VBA

CurDir() — функция, которая возвращает путь к каталогу(для указанного диска), в котором по умолчанию будут сохраняться файлы:

Dim sCurDir As String sCurDir = CurDir(«D»)

Input() — открывает текстовый файл на чтение. Т.е. таким методом можно открыть файл и вытянуть из него данные. Например, чтобы считать информацию из файла C:Text1.txt и вывести ее в окно Immediate можно применить такой код:

Dim MyChar Open «C:Text1.txt» For Input As #1 ‘Открываем файл функцией Open() на чтение(Input) Do While Not EOF(1) ‘пока файл не кончился ‘ Получаем по одному символу и добавляем его к предыдущим MyChar = MyChar & Input(1, #1) Loop Close #1 ‘ Закрываем файл ‘Выводим его содержание в окно Immediate ‘(отобразить Immediate: Ctrl+G в окне редактора VBA) Debug.Print MyChar ‘или в MsgBox MsgBox MyChar, vbInformation, «www.excel-vba.ru»

Sub SelectionToTxt() Dim s As String, rc As Range Dim ff ‘запоминаем все значения из выделенной строки в строку For Each rc In Selection If s = «» Then ‘если пока ничего не записали — присваиваем только значение ячейки s = rc.Value Else ‘если уже записано — добавляем через TAB s = s & vbTab & rc.Value End If Next ff = FreeFile ‘Открываем текстовый файл ‘если файла нет — он будет создан Open «C:Text1.txt» For Output As #ff ‘записываем значение строки в файл Print #ff, s Close #ff ‘ Закрываем файл End Sub

Важно помнить, что при открытии файла таким методом(Output) все предыдущие данные из файла стираются и в файле будет записано только то, что мы записали в текущем сеансе. Если данные необходимо добавить к имеющимся — используется метод Append
Append() — метод открывает файл для записи, но в отличии от Output записывает данные в конец файла, а не перезаписывает текущие данные. Например, код добавления выделенных ячеек как одной строки в имеющийся файл будет выглядеть так:

3.9.10 Функции для работы с файловой системой

Файловые функции VBA, Input(), FileLen(), EOF(), LOF(), Loc()

В VBA предусмотрен набор встроенных функций для выполнения различных операций с файлами, каталогами, дисками и прочими объектами файловой системы. Информация об этих функциях приведена ниже. Но не забывайте, что помимо этих возможностей (общих для всех приложений, в которых используется VBA) у нас есть также, во-первых, возможности, специфические для данного приложения (например, открытие и сохранение документа Word средствами объектной модели Word). Во-вторых, на любом компьютере под управлением Windows есть объектная библиотека под названием Microsoft Scripting Runtime — очень простая и очень удобная для выполнения различных операций с файлами, каталогами и дисками. Можно добавить в проект VBA на нее ссылку и использовать все имеющиеся в ней возможности. Если, к примеру, мне нужно пройти по всем файлам в данном каталоге и что-нибудь с ними сделать (например, загрузить в Excel все файлы отчетов, которые пришли из филиалов), я использую именно эту библиотеку. Справку по ней можно найти на сайте Microsoft (www.microsoft.com/scripting).

А это — встроенные функции для работой с файловой системой, предусмотренные в VBA:

• CurDir() — функция, которая возвращает путь к текущему каталогу, в котором будут сохраняться файлы нашего приложения по умолчанию.
• Dir() — позволяет искать файл или каталог по указанному пути на диске.
• EOF() — при операции записи в файл на диске эту функция вернет True, если вы находитесь в конце файла. Используется при записи в файл своего собственного формата. При сохранении документов Word, книг Excel и т.п. лучше использовать стандартные методы объектов этих документов: Save и SaveAs().
• Error() — позволяет вернуть описание ошибки по ее номеру. Генерировать ошибку нужно при помощи метода RaiseError() специального объекта Err (см. главу 6, в которой рассказывается про перехват ошибок и отладку).
• FileAttr() — позволяет определить, как именно был открыт вами файл в файловой системе: на чтение, запись, добавление, в двоичном или текстовом режиме и т.п.
• FileDateTime() — позволяет получить информацию о последнем времени обращения к указанному вами файлу. Если к файлу после создания ни разу не обращались, то это будет время создания файла.
• FileLen() — позволяет определить длину указанного вами файла в байтах.
• FreeFile() — позволяет определить следующую свободную цифру, которую можно использовать как номер файла при его открытии.
• GetAttr() — возможность обратиться к файлу к файловой системе и получить информацию об его атрибутах (скрытый, доступен только для чтения, архивный и т.п.)
• Input() — позволяет считать информацию из открытого файла. Например, считать информацию из файла C:text1.txt и вывести ее в окно Immediate можно так:

Open » c:text1.txt» For Input As #1 ‘Открываем файл функцией Open() на чтение

Do While Not EOF(1) ‘пока файл не кончился

‘ Получаем по одному символу и добавляем его к предыдущим

MyChar = MyChar & Input(1, #1)

Close #1 ‘ Закрываем файл

Debug . Print MyChar ‘Выводим его содержание в окно Immediate

• Вариант этой функции — InputB() позволяет указывать количество байт, которые надо скачать из файла.
• Loc() — от Location, то есть местонахождение — возвращает число, которое определяет текущее место вставки или чтения в открытом файле. Похоже работает функция Seek(), но она возвращает информацию о позиции, с которой будет выполняться следующая операция чтения или вставки.
• LOF() — от length of file — позволяет определить длину открытого файла в байтах.

Работа с файловой системой

В этой статье я расскажу об удобном способе работы с фаловой системой в VBA. Для начала создадим объект файловой системы:

Dim fso
Set fso = CreateObject(«Scripting.FileSystemObject»)

Этот объект включает в себя все необходимое для работы с файлами и папками Windows. Вот так можно получить список всех дисков в системе:

Set drives = fso.drives
For Each d In drives
s = s + d.DriveLetter + «, »
Next d
MsgBox (s)

Попробуем узнать всю информацию о диске C: этот код покажет нам свободное место на диске, его полную емкость, тип файловой системы, метку, серийный номер и многое другое.
Set DriveC = FSO.GetDrive(«C:»)
Str = «Диск C:» & vbCrLf & _
«AvailableSpace: » & DriveC.AvailableSpace & vbCrLf & _
«DriveLetter: » & DriveC.DriveLetter & vbCrLf & _
«DriveType: » & DriveC.DriveType & vbCrLf & _
«FileSystem: » & DriveC.FileSystem & vbCrLf & _
«FreeSpace: » & DriveC.FreeSpace & vbCrLf & _
«IsReady: » & DriveC.IsReady & vbCrLf & _
«Path: » & DriveC.Path & vbCrLf & _
«RootFolder (Path): » & DriveC.RootFolder.Path & vbCrLf & _
«SerialNumber: » & DriveC.SerialNumber & vbCrLf & _
«ShareName: » & DriveC.ShareName & vbCrLf & _
«TotalSize: » & DriveC.TotalSize & vbCrLf & _
«VolumeName: » & DriveC.VolumeName
MsgBox Str

С дисками разобрались, теперь перейдем к папкам. Объект папки создается так:

Set Folder = FSO.GetFolder(«C:Program Files»)

Set Folder = FSO.GetSpecialFolder(0)

Или вот так:

Set DriveC = FSO.GetDrive(«C:»)
Set Folder = DriveC.RootFolder

Получим список всех папок в C:Program Files:

Set Folder = fso.GetFolder(«C:Program Files»)
For Each SubFolder In Folder.SubFolders
s = s + SubFolder.Name + vbCrLf
Next
MsgBox (s)

А теперь посмотрим файлы на диске D:

Set Folder = fso.GetFolder(«d:»)
For Each f In Folder.Files
s = s + f.Name + vbCrLf
Next
MsgBox (s)

Более подробное описание свойств и методов FileSystemObject можно найти в документации.

РАБОТА С ФАЙЛАМИ

Типы файлов в VBA

В VBA допустима работа с тремя типами текстовых файлов. .

Файл последовательного доступа

Рассматривается как последовательность строк произвольной длины, разделенных специальными символами. Чтение и запись в файл производится построчно

Файл произвольного доступа

Состоит из записей фиксированной длины и размер записи указывается при его открытии. Это позволяет локализовать любую запись в файле по ее номеру

Является частным случаем файла произвольного доступа. Размер записи в бинарном файле считается равным 1 байту

Открытие и закрытие файла

Обсудим инструкции, связанные с открытием файла для операций ввода/вывода, а также с закрытием файла по завершению работы с ним.

Разрешает выполнение операций ввода/вывода при работе с файлом.

Open Путь For Режим [Access Доступ] [Блокировка] As [ # ] НомерФайла [Lеn=Длина]

Завершает операции ввода/вывода с файлом, открытым с помощью инструкции open. Эта инструкция ‘очищает буфер и указывает операционной системе обновить FAT (таблицу размещения файлов). Важно, чтобы каждый файл по завершении работы с ним был закрыт, иначе это может привести к частичной потере информации.

Close [СписокНомеровФайлов] Аргумент СписокНомеровФайлов может представлять один или несколько номеров файлов. При этом используется следующий синтаксис, где номерФайла представляет любой допустимый номер файла: [[#] номерФайла] [, [#] номерФайла] .

Закрывает все активные файлы, открытые с помощью инструкции open, и записывает содержимое всех буферов файлов

На диск, открытых с помощью инструкции Open

Функция возвращает доступный номер, который может использоваться в инструкции Open

Приведем пример инструкции, открывающей для записи бинарный файл первый из рабочей папки, и затем инструкции, закрывающей этот бинарный файл.

Open «Первый» For Binary Access Write As #1 Close #1

Ввод данных в файл последовательного доступа

Обсудим инструкции, используемые при вводе информации в файл последовательного доступа.

Записывает форматированные данные в файл последовательного доступа. Синтаксис:

Print #НомерФайла, [СписокВывода]

Записывает неформатированные данные в файл последовательного доступа. В отличие от инструкции Print, инструкция write вставляет запятые между элементами и заключает строки в кавычки по мере записи их в файл.

Write #НомерФайла, [СписокВывода]

Приведем две процедуры, использующие инструкции Print и write для создания файлов последовательного доступа. Сначала рассмотрим процедуру с инструкцией

Open «С:Новый» For Output As II

‘ Печатает текст в файл ‘

‘ Печатает пустую строку в файл

‘ Печатает в двух зонах печати

Результатом описанных выше инструкций будет файл со следующим содержимым:

Зона 1 Зона 2 3 пробела

Перейдем теперь к примеру процедуры, создающей файл последовательного

Достпа при помощи инструкции Write.

For Output As #1

Write #l, «инструкции»;

Результатом описанных выше инструкций будет файл со следующим содержимым:

«Пример»,»использования» «инструкции»,»Write» «Число»,1

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

ПримерислользованияWrite вторая инструкция write специально заканчивается знаком «;» Это обеспечивает вывод данных третьей инструкцией write в ту же строку файла, в которую выводила вторая инструкция.

Вывод данных из файла последовательного доступа

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

Считывает .данные из открытого файла последовательного доступа и присваивает их переменным. Данные, считываемые с помощью инструкции input I, обычно записываются в файл с помощью инструкции Write #.

Input #НомерФайла, СписокПеременных

Считывает строку из открытого файла последовательного доступа и присваивает ее переменной типа string. Данные, считываемые с помощью инструкции Line input I, как правило, записываются в файл с помощью инструкции Print #.

Line Input #НомерФайла, ИмяПеременной

Синтаксис инструкции Line Input1 содержит следующие элементы:

Возвращает значение типа string, содержащее символы из файла, открытого в режиме input или Binary. Функция input считывает данные, записываемые в файл с помощью инструкции Print # или Put.

Input (Число, [#] НомерФайла)

Функция возвращает значение True при достижении конца файла.

EOF (НомерФайла> При последовательном считывании информации из файла часто используется следующий цикл:

Do While Not EOF(l) Loop

или, для тех пользователей, кто предпочитает инструкцию While — Wend инструкции Do While — Loop, следующий эквивалентный цикл:

While Not EOF (I) Wend

Приведем пример использования инструкции input # для считывания данных из файла. В этом примере предполагается, что на диске существует файл группаЭкономистов, содержащий информацию о студентах. Файл был создан при помощи инструкции write # и состоит из двух столбцов, в первом из которых указывается фамилия, а во втором — оценка студента. Для удобства работы с информацией введен пользовательский тип Студенты. Процедура примериспользованияInput последовательно считывает фамилии и оценки из файла и выводит их в ячейки первого и второго столбца рабочего листа.

‘ Фамилия As String * 20

Оценка As String * 3

Dim Студент As Студенты

For Input As 12 i = 1

Do While Not EOF(2) With Студент

Input #2, .Фамилия, .Оценка

Cells(i, 1).Value = .Фамилия

Cells(i, 2).Value = .Оценка

Приведем пример использования инструкции Line input # для считывания данных из файла группаЭкономистов, имеющего ту же структуру, что и в предыдущем примере, но созданного с помощью инструкции Print #. Инструкция Line input # считывает всю строку из файла в строковую переменную. Поэтому в этом случае уже нет необходимости использовать введенный пользовательский тип, а достаточно ограничиться только обычной строковой переменной. Вся считываемая информация строка за строкой вводится в список диалогового .окна.

Private Sub UserForm_Initialize()

Dim Студент As String

.Clear Do While Not EOF(l)

Line Input 11, Студент

Работа с файлом произвольного доступа

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

Записывает содержимое переменной в файл произвольного доступа.

Put [#] НомерФайла, [НомерЗаписи] , ИмяПеременной

Читает данные из открытого файла произвольного доступа в переменную. Синтаксис:

Get [#] НомерФайла, [НомерЗаписи] , ИмяПеременной

Функция возвращает значение типа Long, определяющее текущее положение указателя чтения/записи внутри файла, открытого с помощью инструкции Open.

Функция возвращает значение типа Long, представляющее размер файла в байтах, открытого с помощью инструкции Оpen . Для определения размера закрытого файла следует использовать функции FileLen.

Возвращает значение типа Long, содержащее размер файла в байтах.

Как видно из приведенного в предыдущем разделе примера для считывания информации при помощи инструкции input # из файла группаЭкономистов, файл последовательного доступа иногда удобно задавать как последовательность записей (в данном случае, записей о студентах), причем каждая из записей формируется из группы полей (в данном случае из полей Фамилия и оценка). Отметим, что такая группировка по записям не является чем-то присущим файлам последовательного доступа. Это просто подход, позволяющий упростить процесс последовательного считывания записей. В файле последовательного доступа существует только одна внутренняя структура, образованная разделителями (запятыми или специальными символами, обозначающими переход на новую строку).

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

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

На практике бывает удобно создавать отдельный файл, в котором хранится информация о структуре файла произвольного доступа: структура записи, ее длина и заголовки полей.

Отметим, что при открытии файла произвольного доступа, в отличие от файла последовательного доступа, не надо специально указывать, открывается он для ввода или вывода информации. Ввод и вывод информации определяют команды Put и Get.

Приведем один, пример работы с файлом произвольного доступа группа Экономистов, который имеет ту же структуру, что и в предыдущем примере. Файл создается с помощью процедуры записьвоайл, которая последовательно считывает данные из первого и второго столбца рабочего листа и затем вводит их в файл. В этой процедуре число вводимых записей фиксировано и равно 5. Процедура Считываниеизфайла производит обратное действие — считывает данные из файла и вводит их в ячейки третьего и четвертого столбца рабочего листа. Интересной особенностью файла произвольного доступа является то, что при работе с ним можно определить число записей не пересчитывая их. Число записей равно отношению размера файла к длине одной записи. Длина записи устанавливается при создании файла произвольного доступа и определяется типом переменной, при помощи которой файл был создан, размер открытого файла возвращается функцией LOF, а еще не открытого — функцией FileLen. В рассматриваемом случае число записей в файле равно LOF(l) / Len(Студент).

Фамилия As String * 20 Оценка As String * 3

Dim Студент As Студенты

Dim i As Integer

For Random As #1 Len = Len(Студент)

.Фамилия = Cells(i, 1).Value .Оценка = Cells(i, 2).Value