Подготовка среды VBA для работы с файлами

Для работы с файлами в VBA (а также для ряда других операций) используется библиотека Microsoft Scripting Runtime. Прежде чем выполнять любую программу, использующую операции с файлами, необходимо подключить эту библиотеку. Для этого необходимо в среде VBA выбрать команду меню Tools - References и установить флажок Microsoft Scripting Runtime.

Основной объект файловой системы VBA, позволяющий выполнять операции с файлами – объект filesystemobject. В начале любой программы, работающей с файлами, его необходимо создать:

Set fso = CreateObject("scripting.filesystemobject")

Ввод данных из файла

Пример 8.1 –Имеется текстовый файл (например, подготовленный в программе Блокнот), содержащий некоторые числа. В каждой строке файла содержится одно число. Требуется разработать программу для ввода всех чисел из файла в столбец C рабочего листа Excel (начиная с ячейки C1). Кроме того, требуется предусмотреть вывод сообщения об ошибке (и прерывание программы) в случае, если в файле будут введены нечисловые данные. Файл должен выбираться пользователем из стандартного окна открытия файла; если пользователь отказывается от загрузки файла (нажатием клавиши ECS), то программа должна прекращать работу.

Sub primer8_1()

Set fso = CreateObject("scripting.filesystemobject")

ChDrive("D")

ChDir("D:\User")

otkr_file = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If otkr_file = False Then Exit Sub

Set dan = fso.OpenTextFile(otkr_file, ForReading)

i=0

Do While Not dan.AtEndOfStream

stroka = dan.ReadLine

stroka = Trim(stroka)

If Not (IsNumeric(stroka)) Then

MsgBox ("Ошибка в файле")

Exit Sub

End If

x = CSng(stroka)

i = i + 1

Cells(i, 3).Value = x

Loop

dan.Close

End Sub

Здесь оператор Set fso = CreateObject("scripting.filesystemobject") создает объект с именем fso, который будет использоваться для операций с файлами (все имена в этом операторе – зарезервированные).

Оператор ChDrive("D") устанавливает в качестве текущего диск D; оператор ChDir("D:\User") задает текущий путь.

Примечание – Оператор ChDir делает текущим указанный каталог на указанном диске, но не изменяет текущий диск. Поэтому перед оператором ChDir в данном примере указан оператор ChDrive, задающий текущий диск.

Оператор otkr_file = Application.GetOpenFilename("Text Files (*.txt), *.txt") открывает стандартное окно загрузки файла. В нем отображается перечень файлов, содержащихся в текущем каталоге и соответствующих заданному шаблону (в данном случае – файлов с расширением .TXT). Пользователь выбирает один из файлов. Имя выбранного файла присваивается заданной переменной, в данном случае – переменной otkr_file; эта переменная будет строковой (т.е. имеет тип String). Если пользователь отказывается от загрузки файла (например, нажатием клавиши ESC), то переменная otkr_file становится логической (тип Boolean) и принимает значение False.

Примечание – Шаблон файлов требуется указывать именно так, как показано в данном примере, т.е. указывать сначала описание файлов (Text Files (*.txt)), затем – указание расширения (*.txt). Например, если бы потребовалось, чтобы в окне загрузки перечислялись не TXT, а XLS-файлы, то оператор открытия окна загрузки файла имел бы следующий вид: otkr_file = Application.GetOpenFilename ("Excel Files (*.xls), *.xls"). Если бы требовалось перечислить в окне загрузки все файлы, то оператор был бы указан в следующей форме: otkr_file = Application.GetOpenFilename().

Оператор If otkr_file = False Then Exit Sub проверяет, не отказался ли пользователь от загрузки файла. В случае отказа от загрузки работа программы завершается (оператор Exit Sub).

В операторе Set dan = fso.OpenTextFile(otkr_file, ForReading) файл с заданным именем (хранящимся в переменной otkr_file) открывается для чтения (ForReading), т.е. из него можно будет только читать данные. С этим файлом связывается объект dan, который будет использоваться для последующих операций с этим файлом.

В операторе i=0 задается начальное значение переменной i; она будет затем использоваться в качестве номера строки на рабочем листе Excel, и при необходимости она будет увеличиваться.

Оператор Do While Not dan.AtEndOfStream – начало цикла, который будет повторяться, пока не будет достигнут конец файла, связанного с объектом dan. В данном случае это файл, выбранный пользователем и открытый ранее для чтения (см. выше).

В операторе stroka = dan.ReadLine вводится одна строка из заданного файла; эта строка присваивается переменной strokа (здесь ReadLine – операция ввода). В операторе stroka = Trim(stroka) функция Trim удаляет из переменной strokа начальные и концевые пробелы, т.е. пробелы в начале и в конце строки (если они есть).

В операторе If Not (IsNumeric(stroka)) Then … проверяется, является ли переменная stroka числом. Для этого используется функция IsNumeric, принимающая значение True, если ее аргумент – число, и False – если это не число (например, если в переменной strokaсодержится буква или пробел). Если оказывается, что stroka – не число, то выводится сообщение об ошибке MsgBox ("Ошибка в файле") и программа завершается (Exit Sub).

Если оказывается, что переменная stroka содержит число, то в операторе x= CSng(stroka) эта переменная преобразуется в переменную типа Single (функция CSng). Затем номер текущей строки на рабочем листе Excel увеличивается на единицу (i = i + 1), и переменная x выводится в очередную ячейку столбца C, т.е. третьего столбца (Cells(i, 3).Value = x).

По окончании цикла, т.е. после достижения конца файла (dan.AtEndOfStream), файл закрывается (оператор dan.Close).

Вывод данных в файл

Пример 8.2 –На рабочем листе Excel в столбце A (начиная с ячейки A1) хранятся номера контрактов, в столбце B – количество поставляемого товара по соответствующему контракту, в столбце C – цена за одно изделие. Требуется вывести в текстовый файл номера и стоимости контрактов, стоимость которых превышает 10 000 ден.ед. В текстовом файле номер и стоимость контракта должны представлять собой одну строку; номер и стоимость должны быть разделены пробелом. На рабочем листе Excel имеется также текстовое поле Imya_faila, где пользователь указывает имя файла для вывода данных. Если файл с указанным именем уже существует, то данные должны добавляться в него.

Sub primer8_2()

Set fso = CreateObject("scripting.filesystemobject")

rez_file = Imya_faila.Value

Set vyvod = fso.OpenTextFile(rez_file, ForAppending, True)

Set d = Cells(1, 1).CurrentRegion

m = d.Rows.Count

For i = 1 To m

stoimost = d.Cells(i, 2).Value * d.Cells(i, 3).Value

If stoimost > 10000 Then

nomer = d.Cells(i, 1).Value

stroka = CStr (nomer) + " " + CStr(dohod)

vyvod.WriteLine (stroka)

End If

Next i

vyvod.Close

End Sub

Здесь, как и в предыдущем примере, оператор Set fso = CreateObject("scripting.filesystemobject") создает объект с именем fso, который будет использоваться для операций с файлами (все имена в этом операторе – зарезервированные).

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

В операторе Set vyvod = fso.OpenTextFile(rez_file, ForAppending, True) файл с заданным именем (хранящимся в переменной rez_file) открывается для добавления данных (ForAppending). Значение True указывает, что в случае, если указанный файл не существует, он будет создан (и также открыт для добавления данных). С открываемым файлом связывается объект vyvod.

Затем определяется заполненная данными область рабочего листа, начиная с ячейки Cells(1,1)(т.е. с A1); этой области будет соответствовать объект d. Определяется количество строк (m = d.Rows.Count).

Для каждой строки вычисляется стоимость указанного в ней контракта (переменная stoimost). Если она превышает 10 000, то из номера контракта и его стоимости составляется строка: stroka = CStr (nomer) + " " + CStr(stoimost). Здесь CStr – функция преобразования из числовых данных в строковые, знак + обозначает операцию сцепления строк. В операторе vyvod.WriteLine(stroka) полученная строка выводится в файл, соответствующий объекту vyvod. Здесь WriteLine – операция вывода строки в файл.

По окончании цикла, т.е. после вывода в файл всех необходимых строк, файл закрывается (оператор vyvod.Close).

Не нашли, что искали? Воспользуйтесь поиском по сайту: