2 семестр / vba_2002

Экспортирование диапазона в текстовый файл

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

Обратите внимание на то, что процедура использует два оператора Write #. Первый завершается точкой с запятой, поэтому последовательность возврат каретки/перевод строки в файл не добавляется. Но для последней ячейки в строке второй оператор Write # не имеет точки с запятой, что приводит к завершению строки и добавлению следующей ячейки уже в новой строке.

Переменная Data используется для хранения содержимого каждой ячейки. Если ячейка имеет числовой формат, то переменная принимает значение, что обеспечивает отсутствие кавычек при сохранении данных. Если ячейка пуста, то ее свойство Value возвращаегзначение 0. Таким образом, проверяются пустые ячейки (для этого используется функция IsEmpty) и вместо 0 подставляется пустая строка.

Sub ExportRangeO

Dim Filename As String

Dim NumRows As Long, NumCols As Integer Dim r As Long, с As Integer

Dim Data

Dim ExpRng As Range Set ExpRng = Selection

NumCols = ExpRng.Columns.Count HumRows = ExpRng.Rows.Count Filename * "c:\windows\textfile.txt" Open Filename For Output As #1

For r = 1 To NumRows

For с = 1 To HumCols

Data = ExpRng.Cells(r, c)-Value

If IsNumeric(Data) Then Data = Val(Data)

If IsEmpty(ExpRng.Cells(r, c) > Then Data = "" If с <> NumCols Then

Write #1, Data;

Else

Write #1, Data End If

Next с Next г

Close #1

End Sub

Этот пример доступен на Web-узле издательства.

На рис. 27.3 показано содержимое результирующего файла.

Рис.27.3.Текстовыйфайл,которыйсоздан спомощьюкода VBA

ЧастьVII.Другиетемы

66:)

Импортирование текстового файла в диапазон

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

Sub	ImportRange()
	Dim	ImpRng As Range
	Dim Filename As		String
	Dim r As Long,		с As Integer
	Dim txt As String, Char As String * 1
	Dim Data
	Dim i As integer
	Set	ImpRng = ActiveCell
	On	Error Resume Next
	Filename = "c:Windows\textfile.txt"
	Open Filename For1 Input As #1
	If Err <> 0 Then
		MsgBox "Невозможно найти: " & Filename, vbCritical, "ОШИБКА"
		Exit Sub
	End	If
	r = 0
	с = О
	txt = ""
	Application.ScreenUpdating - False
	Do Until EOF|1J
		Line Input #1,		Data
		For i = 1 To Len(Data)
		Char = Mid(Data, i, 1)
		If Char = "," Then 'запятая
		ActiveCell.Offset(r, c) = txt
		с = с + 1
		txt =		""
		Elself i - Len(Data) Then 'конец строки
		If Char <> Chr<34) Then txt = txt & Char
		ActiveCell.Offset(r, c> ~ txt
		txt =		""
		Elself Char <> Chr{34) Then
		txt = txt & Char
		End If
		Next i
		с = 0
		r = r + 1		t
	Loop
	Close #1
	Application.ScreenUpdating = True
End	Sub
		Процедура, показанная выше, имеет недостаток: она не поддерживает значе-
		ния, которые содержат символы кавычек или запятой. Кроме того, импортиро-
		ванныеданныебудутокруженысимволаминомера,например:#2001-05-12*.
		Данный примердоступенна Web-уэлеиздательства.

670 Глава 27. Управление файлами с помощью ИМ

Протоколирование операций в Excel

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

Следующая процедура, которая находится в модуле кода объекта ЭтаКнига, выполняется каждый раз при открытии файла.

Private Sub Workbook_Open()

Open Application.Path & "\excelusage.txt" _ For Append As #1

Print #1, "Started " & Now Close #1

End Sub

Процедура добавляет новую строку в файл, который называется e x c e l u s a g e . t x t . Новая строка содержит текущую дату и время, и может выглядеть следующим образом:

Started 03/09/00 9:27:43 РМ

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Open Application.Path & "\excelusage.txt" _ For Append As #1

Print #1, "Stopped " & Now

Close #1

End Sub

Фильтрациятекстовогофайла

Пример данного раздела демонстрирует одновременную работу с двумя текстовыми файлами. Процедура F i l t e r F i l e , которая приводится ниже, читает текстовый файл (inf i l e . t x t ) и копирует строки, содержащие определенный текст, во второй текстовый файл ( o u t p u t . t x t ) .

Sub FilterFile(}

Open "infile.txt" For Input As #1

Open "output.txt" For Output As #2 TextToFind = "January"

Do Until EOF(l)

Line Input #1, data

If InStr(1, data, TextToFind) Then Print #2, data

End If

Loop

Close End Sub

Этот пример доступен на Web-узле издательства.

Часть VII. Другие темы

671

Импортирование более 256 столбцов данных

Часто возникает необходимость в импортировании данных, которые превышают ограничение Excel в 256 столбцов. Если попытаться открыть такой файл с помощью команды Файл^Открыть, то Excel просто проигнорирует данные, которые находятся за пределами 256-го столбца (при этом предупреждениена экране не отображается)!

Следующая процедура является вариантом процедуры ImportRange, которая приводилась ранее в этой главе. Такая процедура выполняет чтение текстового файла и импортирует данные в новую рабочую книгу. Если данные содержат более 256-ти столбцов, то в рабочую книгу добавляются дополнительные листы.

Sub ImportLongLines()

1Импорт тер:стового файла объемом более 256 столбцов

Dim ImpRange As Range

Dim r As Long, с As Integer Dim CurrLine As Long

Dim Data As String, Char As String, Txt As String Dim i As Integer

Dim CurrSheet As Worksheet

1	Создание рабочей книги	с одним листом
	Workbooks.Add xlWorksheet
	Open ThisWorkbook.Path & "\longfile.txt" For Input As #1
	r = 0
	с = О
	Set ImpRange * ActiveWorkbook.Sheets(1).Range("Al")
	Application.ScreenUpda ting =		False
	Чтение первой строки,	вставка	листа {если требуется)
	CurrLine = CurrLine + 1
	Line Input #1, Data
	For i = 1 To Len(Data)
	Char - Mid(Data, i, 1)
1	Столбцы закончились?
	If с <> 0 And с Mod 2 5 6 - 0 Then
	Set CurrSheet = ActiveWorkbook.Sheets.Add(after:= _
	ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
	Set ImpRange =	CurrSheet,Range{"Al")

с= О

End If

1Конец поля?

If Char - "," Then ImpRange.Offset(r, c) = Txt

с= с + 1

Txt = ""

Else

1Пропуск символов кавычек If Char <> Chr(34) Then _

Txt = Txt SL Mid (Data, i, 1)

'Конец строки?

If i = Len(Data) Then

ImpRange.Offset(r, c) = Txt с = с + 1

672	Глава 27. Управление файлами с помощью VBA

		Тхt = •"
		End If
	End If
	Next i
	Чтение	остальных данных
	с = О
	CurrLine - 1
	Set ImpRange = ActiveWorkbook.Sheets(1).Range("Al")
	r = r + 1
	Do Until EOF(l)
	Set ImpRange = ActiveWorkbook.Sheets(1).Range("Al")
	CurrLine = CurrLine + 1
	Line Input #1, Data
	Application.StatusEar = "Обработка строки " & CurrLine
	For i = 1 To Len(Data)
		Char = Mid(Data, i, 1)
1	•	Столбцы закончились?
		If с о 0 And с Mod 256 =	0 Then
		с = О
		Set ImpRange = ImpRange.Parent.Next.Range("Al")
		End If
1		Конец поля
		If Char = ", " Then
		ImpRange.Offset(r, c)	= Txt
		с = с + 1
		Txt = ""
		Else

'Пропуск символов кавычек If Char <> Chr(34) Then

Txt = Txt & Mid(Data, i, 1)

1	Конец строки?
	If i = Len(Data) Then
		ImpRange.Offset(r, c) = Txt
	с = с + 1
		Txt = "•
	End	If
	End If
Next	i
с = О
Set	ImpRange	= ActiveWorkbook.Sheets(1),Range("Al"}
r = r + 1
Loop

1 Очистка Close #1

Application.ScreenUpdating = True Application.StatusBar = False

End Sub

Эта процедура состоит из двух частей. Первая часть читает первую строку данных и до-
бавляет новые рабочие листы, если это необходимо. Вторая часть читает оставшиеся строки
текстового файла. Код предполагает, что первая строка имеет тот же формат, что и оставшая-
ся часть данных, и что первая строка характеризуется максимальным числом столбцов.
ЧастьVII.Другиетемы	673

Этот пример доступен на Web-узле издательства. Более того, вместе с примером предоставляется текстовый файл, содержащий 100 строк и 600 столбцов данных.

Экспорт диапазона в формат HTML

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

Ниже приведен исходный код процедуры ExportToHTML.

Sub ExportToHTML()
	Dim ws As Worksheet
	Dim Filename As		Variant
	Dim TDOpenTag	As	String, TDCloseTag		As String
	Dim CellContents		As	String
	Dim Rng As Range
	Dim r As Long,	с	As	Integer
1	Использование	выделенного диапазона			ячеек

Set Rng = Application.Intersect{ActiveSheet.UsedRange, Selection}

'Получение имени файла

Filename = Application.GetSaveAsFilename( _ InitialFileName:="myrange.htm", _

fileFilter:="HTML Files(*.htm), *.htm") If Filename = False Then Exit Sub

'Открытие текстового файла

Open Filename For Output As #1

1	Запись дескриптора <TABLE>
	Print #1, "<TABLE BORDER=1 CELLPADDING=3>"
'	Циклический просмотр ячеек
	For г = 1 To Rng.Rows.Count
	Print #1, "<TR>"
	For с - 1 To Rng.Columns.Count
	TDOpenTag = "<TD ALIGN=RIGHT>"
	TDC1oseTag = "</TD>"
	If Rng.Cells(r,	c).Font.Bold Then
	TDOpenTag = TDOpenTag & "<B>"
	TDCloseTag = "</B>" & TDCloseTag
	End If
	If Rng.Cellsfr,	c ) . F o n t . I t a l i c Then
	TDOpenTag = TDOpenTag & "<l>"
	TDCloseTag = "</I>" & TDCloseTag
	End If
	CellContents -	Rng.Cells(r, c).Text
	Print #1, TDOpenTag & CellContents & TDCloseTag
	Next с
	Print #1, "</TR>"
	Next r
674		Глава 27. Управление файлами с помощью VBA

1	Закрытие	таблицы
	Print #1,	"</TABLE>"
1	Закрытие	файла
	Close #1
1	Создание	сообщения пользователю

MsgBox Rng.Count & " ячеек экспортировано в файл " & Filename End Sub

Этот пример доступен на Web-узле издательства.

Процедура начинает свою работу с определения экспортируемого диапазона. Данный диапазон определяется как пересечение выделенного диапазона и используемой области рабочего листа. Это позволяет удостовериться, что не будет обрабатываться весь столбец или строка. Пользователь получает диалоговое окно с просьбой указать имя файла, Далее открывается указанный текстовый файл. Основная работа выполняется в двух циклах F o r - N e x t . Код генерирует соответствующий код HTML (записывает необходимые дескрипторы) и заносит в файл экспортируемые данные. Наконец, файл закрывается, и пользователю выводится окно сообщения с итоговой информацией.

На рис. 27.4 показан диапазон рабочего листа, а на рис. 27.5 представлено, как этот диапазон выглядит в броузере после экспорта данных в формат HTML.

Что мешает использовать команду Excel Файл'ФСохранить как Web страницу? Процедура, приведенная в данном примере, имеет одно заметное преимущество: она не создает "раздутый" код HTML. Например, процедура ExportToHTML использовалась для экспорта диапазона из 70 ячеек. Результирующий файл имеет размер 2,7 Кбанта. Если выполнить команду

Часть VII.Другиетемы

675

Excel ФайлоСохранить как Web страницу, то результирующий файл будет иметь размер 15,8 Кбайта (в шесть раз больше). С другой стороны, процедура ExportToHTML не сохраняет все параметры форматирования ячеек. Она поддерживает только форматирование с помощью полужирного и курсивного начертания. Кроме того, процедура ExportToHTML имеет еще один серьезный недостаток: она не поддерживает объединенные ячейки.

Процедуру ExportToHTML можно использовать в качестве основы для последующего изменения текстовых данных.

Резюме

В этой главе рассматривались способы управления файлами, хранящимися на жестком диске: стандартные команды VBA, использование объекта F i l e S e a r c h и применение объекта F i l e S y s t e m O b j e c t . Вы ознакомились с примерами использования кода VBA для чтения и записи текстовых файлов.

Следующая глава демонстрирует применение приложений VBA для генерации других приложений VBA.

676	Глава 27. Управление файлами с помощью VBA

Управление компонентами Visual Basic

В данной главе рассматривается тема, которая может оказаться довольно важной для многих читателей, — создание кода VBA для управления компонентами проектов. Интегрированная среда разработки VBA содержит объектную модель, которая предоставляет ключевые элементы проекта VBA, включая сам редактор. Можно создавать код VBA, который добавляет или удаляет модули, создает дополнитель-

ный код VBA или даже диалоговые окна UserForm.

Введение в IDE

IDE — это интерфейс OLE Automation для редактора Visual Basic Editor. Сразу после создания ссылки на библиотеку Visual Basic Extensibility Library (с помощью команды ToolsOReferences (Сервис^Ссылки)) разработчику предоставляется доступ ко всем объектам, свойствам и методам VBE, если также позволено объявлять объекты из классов членов IDE.

В диалоговом окне References можно выбрать опцию Microsoft Visual Basic for Application Extensibility. Это предоставит доступ к объекту VBIDE. Создание ссылки на объект VBIDE позволит объявлять объекты, которые входят в состав VBIDE, а также открывает доступ к константам, определенным внутри IDE. Отметим, что доступ к объектам ШЕ можно получать и не создавая ссылку, но при этом у вас не будет возможности использовать константы ШЕ, а также объявлять объекты, которые ссылаются на компоненты IDE.

Дополнительная информация об автоматизации OLE приведена в главе 20.

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

Создавать и удалять модули VBA. Вставлять код VBA.

Создавать пользовательские диалогоиые окна.

Добавлять элементы управлении в диалоговые окна UserForm.

Важные замечания для пользователей Excel 2002

Если для разработки приложений используется Excel 2002, то необходимо помнить, что в Excel 2002 многое изменилось. Для того чтобы уменьшить вероятность заражения макровирусами, Microsoft усложнила процедуры использования VBA для модификации компонентов проекта VBA. Если попытаться запустить одну из процедур, приводимых в этой главе, то на экране появится сообщение об ошибке, показанное ниже.

Отображение этого сообщения зависит от параметров, установленных в диалоговом окне Excel Безопасность (доступ к которому можно получить с помощью команды Сервис^Макрос=*Безопасность). В этом окне опция Доверять доступ к Visual Basic Project по умолчанию не выставлена. Даже если пользователь разрешит запускать макросы, которые содержатся в рабочей книге, но этот параметр будет отключен, макрос не сможет внести изменения в проект VBA. Обратите внимание, что данный параметр применяется ко всем рабочим книгам и не может изменяться только для одной рабочей книги.

Прямой доступ к этому параметру получить невозможно. Единственный способ определить наличие в среде этого параметра — попытаться получить доступ к объекту VBProject, после чего проверить» существование ошибки, Следующий код иллюстрирует этот способ.

On	Error		Resume Next
Set	x	=	ActiveWorkbook.VBProject
If Err		<>	0 Then

MsgBox "Настройки безопасности не позволяют выполнить макрос."

Exit Sub

End If

He все примеры, приведенные в этой главе, предназначены для использования конечными пользователями. Большинство из них призваны упростить задачу разработчиков по созданию новых проектов. В таких проектах может возникнуть необходимость в отключении параметра Доверять доступ к Visual Basic Project.

Объектная модель IDE

Программирование ГОЕ требует четкого понимания объектной модели. Объектом верхнего уровня в иерархии объектов выступает VBE (Visual Basic Environment — Среда разработки Visual Basic). Как и в случае с объектной моделью Excel, VBE содержит другие объекты. Упрощенная версия объектной иерархии IDE выглядит следующим образом.

VBE

VBProject

VBComponent

678	Глава 28. Управление компонентами Visual Basic