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

Размещено на http://www.allbest.ru/

2

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ МАШИНОСТРОИТЕЛЬНЫЙ УНИВЕРСИТЕТ (МАМИ) / УНИВЕРСИТЕТ МАШИНОСТРОЕНИЯ

Кафедра: «Автоматика и процессы управления»

Факультет: «Автоматизация и информационные технологии»

Дисциплина: «Информатика»

Расчетно-графическая работа

Тема: «Использование средств автоматизации Excel и VBA для разработки приложений обработки данных СУБД»

Студент:

Царева К.С.

Преподаватель:

Поповкин А.В.

Москва 2014

Задание к расчётно-графической работе

Тема: Исследование возможностей разработки приложений в среде Visual Basic for Application для обработки данных реляционных СУБД, исследование возможностей создания пользовательского интерфейса на основе форм, рассмотрение методов расширения функциональности приложений за счет сторонних компонент.

Задачи:

* Создать приложение с несколькими формами в Visual Basic for Application;

* Создать базу данных в Ms Access, спроектировав ее структуру и запросы;

* Заполнить базу данных и провести отладку запросов;

* Разработать алгоритмы обработки данных в приложении;

* Отладить приложение, разработать и проверить контрольный пример;

* Подготовить отчет.

Оборудование и принадлежности:

Персональный компьютер с установленной операционной системой Microsoft Windows, система управления электронными таблицами Ms Excel с установленной средой Visual Basic for Applications, СУБД Ms Jet и среда Ms Access.

1. Основные теоретические сведения

ADO (ActiveX Data Objects - объекты данных, построенные как объекты ActiveX) - это часть архитектуры универсального доступа к данным от Microsoft. ADO является частью более крупномасштабной технологии под названием MDAC (Microsoft Data Access Components) . Термин MDAC является общим обозначением для всех разработанных компанией Microsoft технологий, связанных с базами данных. К этому набору относятся ADO, OLE DB, ODBC и др.

Технология ADO базируется на возможностях СОМ, а именно интерфейсов OLE DB. OLE DB представляет собой интерфейс системного уровня, обеспечивающий доступ к различным источникам данных, изолируя приложение от вида источника. ADO представляет собой высокоуровневый программный интерфейс для доступа к OLE DB-интерфейсам. ADO содержит набор объектов, используемых для соединения с источником данных, для чтения, добавления, удаления и модификации данных.

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

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

Так как технология ADO основана на стандартных интерфейсах СОМ, которые являются системным механизмом Windows, это сокращает общий объем работающего программного кода и позволяет распространять приложения без вспомогательных программ и библиотек.

При использовании технологии ADO в VBA нам необходимо знать следующее:

Методы:

Create - создает объект подключения. Автоматически вызывается при инициализации.

Connect - открывает соединение. Автоматически вызывается при запросе.

Destroy - уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.

Disconnect - закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.

Query - выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.

Свойства:

Connection - объект соединения

Recordset - результат выполнения запроса

DataSoure - источник данных. Полное имя книги Excel.

Header - учитывать заголовки (да/нет). По умолчанию нет. В этом случае имена полей назначаются автоматически F1 ... Fn. Если да, первая строка диапазона считается заголовком поля.

Ход выполнения работы

1. Создала две таблицы и заполнила их в Access



2. Спроектировала структуру и запросы

3. Создала кнопку, которой присвоила макрос на открытие главной формы

4. Создала формы для стартового окна и запросов в среде VBA



5. Используя синтаксис VBA, разработала программный код на выполнение задач РГР и создала обработчики событий для кнопок формы

2. Программный код

Dim t1 As Boolean

Dim t2 As Boolean

Private Sub CommandButton1_Click() 'Загрузить таблицу 1'

Me.ListBox1.Clear

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "Select * From Tab_1"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn

i = 1

y = rs.Fields.Count - 1

Me.ListBox1.ColumnCount = y + 1

While Not (rs.EOF)

For j = 0 To y

Me.ListBox1.AddItem ""

Me.ListBox1.List(0, 0) = "Код предприятия"

Me.ListBox1.List(0, 1) = "Название"

Me.ListBox1.List(0, 2) = "Адрес"

Me.ListBox1.AddItem ""

Me.ListBox1.List(i, j) = rs.Fields(j)

Next j

i = i + 1

t1 = True

rs.MoveNext

Wend

rs.Close

cn.Close

End Sub

Private Sub CommandButton17_Click()

UserForm5.Show

End Sub

_____________________________________________________________

Private Sub CommandButton2_Click() 'Загрузить таблицу 2'

Me.ListBox2.Clear

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "Select * From Tab_2"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

i = 1

y = rs.Fields.Count - 1

Me.ListBox2.ColumnCount = y + 1

While Not (rs.EOF)

For j = 0 To y

Me.ListBox2.AddItem ""

Me.ListBox2.List(0, 0) = "Код"

Me.ListBox2.List(0, 1) = "Вид продукции"

Me.ListBox2.List(0, 2) = "Квартал1"

Me.ListBox2.List(0, 3) = "Квартал2"

Me.ListBox2.List(0, 4) = "Квартал3"

Me.ListBox2.List(0, 5) = "Квартал4"

Me.ListBox2.List(1, 6) = "Средняя цена"

Me.ListBox2.AddItem ""

Me.ListBox2.List(i, j) = rs.Fields(j)

Next j

i = i + 1

t2 = True

rs.MoveNext

Wend

rs.Close

cn.Close

End Sub

_____________________________________________________________

Private Sub CommandButton13_Click() 'Удалить из таблицы1'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

If t1 = False Then

MsgBox "Сначало загрузите таблицу1", vbExclamation: Exit Sub

Else

For l = 0 To ListBox1.ListCount - 1

If Me.ListBox1.Selected(l) Then

x = Me.ListBox1.List(l)

End If

Next l

If x = "" Then

MsgBox "Выделите строку со значениями", vbExclamation: Exit Sub

Else

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "Delete Tab_1.Kod, Tab_1.Nazv, Tab_1.adr FROM Tab_1 WHERE ((Tab_1.Kod)=" & x & ");"

MsgBox "Данные успешно удалены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

End If

End If

End Sub

__________________________________________________

Private Sub CommandButton14_Click() 'Удалить из таблицы2'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

Dim q As String

If t2 = False Then

MsgBox "Сначала загрузите таблицу2", vbExclamation: Exit Sub

Else

For l = 0 To ListBox2.ListCount - 1

If Me.ListBox2.Selected(l) Then

x = Me.ListBox2.List(, 0)

q = Me.ListBox2.List(, 1)

End If

Next l

If x = "" Then

MsgBox "Выделите строку со значениями", vbExclamation: Exit Sub

Else

q = Chr(34) & q & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "DELETE Tab_2.kod, Tab_2.vid, Tab_2.kv1, Tab_2.kv2, Tab_2.kv3, Tab_2.kv4, Tab_2.sred FROM Tab_2 WHERE (((Tab_2.Kod)=" & x & ") AND ((Tab_2.vid)=" & q & "));"

MsgBox "Данные успешно удалены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

End If

End If

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'Редактор таблицы 1'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As String

Dim Na As String

Dim ad As String

Dim ko As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

For l = 0 To ListBox1.ListCount - 1

If Me.ListBox1.Selected(l) Then

x = Me.ListBox1.List(l)

End If

Next l

If x = "" Then

MsgBox "Выделите строку со значениями", vbExclamation: Exit Sub

Else

ko = InputBox("Введите код предприятия")

Na = InputBox("Введите название")

ad = InputBox("Введите адрес")

If ko = Empty Or Na = Empty Or ad = Empty Then GoTo Oshibka

Na = Chr(34) & Na & Chr(34)

ad = Chr(34) & ad & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "UPDATE Tab_1 SET Tab_1.Kod =" & ko & ", Tab_1.Nazv =" & Na & ", Tab_1.adr =" & ad & " WHERE (((Tab_1.Kod)=" & x & "));"

MsgBox "Данные успешно изменены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

GoTo Konec

Oshibka:

MsgBox ("Введены не все значения.")

Konec:

End If

End Sub

_______________________________________________________

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'Редактор таблицы 2'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As String

Dim y As String

Dim ko As String

Dim vi As String

Dim k1 As String

Dim k2 As String

Dim k3 As String

Dim k4 As String

Dim sre As String

Dim i As Integer

Dim j As Integer

Dim q As String

For l = 0 To ListBox2.ListCount - 1

If Me.ListBox2.Selected(l) Then

x = Me.ListBox2.List(, 0)

q = Me.ListBox2.List(, 1)

End If

Next l

If x = "" Then

MsgBox "Выделите строку со значениями", vbExclamation: Exit Sub

Else

q = Chr(34) & q & Chr(34)

ko = InputBox("Код?")

vi = InputBox("Вид?")

k1 = InputBox("Квартал1?")

k2 = InputBox("Квартал2?")

k3 = InputBox("Квартал3?")

k4 = InputBox("Квартал4?")

sre = InputBox("Средняя цена?")

If Not IsNumeric(ko) Or Not IsNumeric(k1) Or Not IsNumeric(k2) Or Not IsNumeric(k3) Or Not IsNumeric(k4) Or Not IsNumeric(sre) Then GoTo Oshibka

vi = Chr(34) & vi & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "UPDATE Tab_2 SET Tab_2.Kod =" & ko & ", Tab_2.vid =" & vi & ", Tab_2.kv1 =" & k1 & ", Tab_2.kv2 =" & k2 & ", Tab_2.kv3 =" & k3 & ", Tab_2.kv4 =" & k4 & ", Tab_2.sred =" & sre & " WHERE (((Tab_2.Kod)=" & x & ") AND ((Tab_2.vid)=" & q & "));"

MsgBox "Данные успешно изменены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

GoTo Konec

Oshibka:

MsgBox ("Введены не все значения или не соответсвует тип данных")

Konec:

End If

End Sub

__________________________________________________________

Private Sub CommandButton16_Click() 'Добавить в таблицу1'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As String

Dim Na As String

Dim ad As String

Dim ko As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

ko = InputBox("Введите код предприятия")

Na = InputBox("Введите название")

ad = InputBox("Введите адрес")

If ko = Empty Or Na = Empty Or ad = Empty Then GoTo Oshibka

Na = Chr(34) & Na & Chr(34)

ad = Chr(34) & ad & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "INSERT INTO Tab_1 VALUES (" & ko & "," & Na & ", " & ad & ");"

MsgBox "Данные успешно изменены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

GoTo Konec

Oshibka:

MsgBox ("Введены не все значения или не соответсвует тип данных")

Konec:

End Sub

________________________________________________________

Private Sub CommandButton15_Click() 'Добавить в таблицу2'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim l As Integer

Dim x As Integer

Dim y As String

Dim ko As String

Dim vi As String

Dim k1 As String

Dim k2 As String

Dim k3 As String

Dim k4 As String

Dim sre As String

Dim i As Integer

Dim j As Integer

Dim q As String

ko = InputBox("Код?")

vi = InputBox("Вид?")

k1 = InputBox("Квартал1?")

k2 = InputBox("Квартал2?")

k3 = InputBox("Квартал3?")

k4 = InputBox("Квартал4?")

sre = InputBox("Средняя цена?")

If Not IsNumeric(ko) Or Not IsNumeric(k1) Or Not IsNumeric(k2) Or Not IsNumeric(k3) Or Not IsNumeric(k4) Or Not IsNumeric(sre) Then GoTo Oshibka

vi = Chr(34) & vi & Chr

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "INSERT INTO Tab_2 VALUES (" & ko & ", " & vi & ", " & k1 & ", " & k2 & "," & k3 & "," & k4 & "," & sre & ");"

MsgBox "Данные успешно изменены"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

cn.Close

GoTo Konec

Oshibka:

MsgBox ("Введены не все значения или не соответсвует тип данных")

Konec:

End Sub

Private Sub CommandButton3_Click() 'Вывести данные о продукции выпуск которой непрерывно увеличивался'

UserForm2.ListBox1.Clear

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "SELECT Tab_2.kod, Tab_2.vid FROM Tab_2 WHERE (((kv1)<[kv2]) AND ((kv2)<[kv3]) AND ((kv3)<[kv4]));"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

i = 1

y = rs.Fields.Count - 1

UserForm2.ListBox1.ColumnCount = y + 1

While Not (rs.EOF)

For j = 0 To y

UserForm2.ListBox1.AddItem ""

UserForm2.ListBox1.List(0, 0) = "Код"

UserForm2.ListBox1.List(0, 1) = "Вид продукции"

UserForm2.ListBox1.AddItem ""

UserForm2.ListBox1.List(i, j) = rs.Fields(j)

Next j

i = i + 1

rs.MoveNext

Wend

rs.Close

cn.Close

UserForm2.Show

End Sub

________________________________________________________

Private Sub CommandButton4_Click() 'Сведения о годовом выпуске и годовой стоимости выпуска для произвольно заданного предприятия и определенной продукции.'

UserForm3.ListBox1.Clear

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

Dim x As String

Dim z As String

x = Chr(34) & InputBox("Введите название фирмы") & Chr(34)

z = Chr(34) & InputBox("Введите название продукции") & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "SELECT [kv1]+[kv2]+[kv3]+[kv4] AS [Годовой выпуск], Tab_2.sred, Tab_1.Nazv, Tab_2.vid FROM Tab_1 INNER JOIN Tab_2 ON Tab_1.Kod = Tab_2.kod WHERE (((Tab_1.Nazv)=" & x & ") AND ((Tab_2.vid)=" & z & "));"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

i = 1

y = rs.Fields.Count - 1

UserForm3.ListBox1.ColumnCount = y + 1

While Not (rs.EOF)

For j = 0 To y

UserForm3.ListBox1.AddItem ""

UserForm3.ListBox1.List(0, 0) = "Годовой выпуск"

UserForm3.ListBox1.List(0, 1) = "Годовая стоимость выпуска"

UserForm3.ListBox1.List(0, 2) = "Название предприятия"

UserForm3.ListBox1.List(0, 3) = "Вид продукции"

UserForm3.ListBox1.AddItem ""

UserForm3.ListBox1.List(i, j) = rs.Fields(j)

Next j

i = i + 1

rs.MoveNext

Wend

rs.Close

cn.Close

UserForm3.Show

End Sub

______________________________________________________

Private Sub CommandButton5_Click() 'Суммарная стоимость выпуска для каждого предприятия'

UserForm4.ListBox1.Clear

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim i As Integer

Dim j As Integer

Dim y As Integer

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "SELECT Tab_2.Kod, Round(Sum(Tab_2.sred)) AS [Суммарная стоимость], Tab_1.nazv FROM Tab_1 INNER JOIN Tab_2 ON Tab_1.Kod = Tab_2.kod GROUP BY Tab_2.Kod, Tab_1.nazv;"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

i = 1

y = rs.Fields.Count - 1

UserForm4.ListBox1.ColumnCount = y + 1

While Not (rs.EOF)

For j = 0 To y

UserForm4.ListBox1.AddItem ""

UserForm4.ListBox1.List(0, 0) = "Код предприятия"

UserForm4.ListBox1.List(0, 1) = "Суммарная стоимость"

UserForm4.ListBox1.List(0, 2) = "Название предприятия"

UserForm4.ListBox1.AddItem ""

UserForm4.ListBox1.List(i, j) = rs.Fields(j)

Next j

i = i + 1

rs.MoveNext

Wend

rs.Close

cn.Close

UserForm4.Show

End Sub

Private Sub CommandButton6_Click() 'Уменьшение на 5% выпуска квартала 1 для заданного предприятия'

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As String

Dim sql As String

Dim x As String

x = Chr(34) & InputBox("Введите название фирмы") & Chr(34)

cmd = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source =" & ActiveWorkbook.Path & "\BD.accdb;Persist Security Info=False"

sql = "UPDATE Tab_1 INNER JOIN Tab_2 ON Tab_1.Kod = Tab_2.kod SET Tab_2.kv1 = [kv1]-(([kv1]/100)*5) WHERE (((Tab_1.Nazv)=" & x & " ));"

cn.ConnectionString = cmd

cn.Open

rs.Open sql, cn, adOpenDynamics

MsgBox ("Данные успешно обновлены")

End Sub

____________________________________________________

Private Sub CommandButton12_Click() 'Выход'

UserForm1.Hide

End Sub

Private Sub UserForm_Click()

3. Блок-схемы алгоритмов



Вывод: В соответствии с индивидуальным заданием выполнено проектирование и создание базы данных в среде Access. Произведено начальное заполнение базы данных по указанному заданию. Сформирована структура рабочего листа вывода справочных данных. Выполнено проектирование средства автоматизации. Разработан пользовательские интуитивно-понятные формы в среде VBA. Произведена разработка приложения для соединения и редактирования базы данных, а так же программный код макросов в среде VBA. Работа выполнена в полном объеме.

Список литературы

1. Джон Уокенбах. Excel 2010 Power Programming with VBA. Издательство: Вильямс 2011 г. - 944 стр. ISBN 978-5-8459-1721-8;

2. Игорь Гайдышев Решение научных и инженерных задач средствами Excel, VBA и C/C++ Издательство: БХВ-Петербург 2004 г. - 512 стр. ISBN 5-94157-477-0;

3. Ганс-Йоахим Берндт. Измерение, управление и регулирование с помощью макросов VBA в Word и Excel. Издательство: МК-Пресс 2008 г. - 256 стр.

4. Игорь Гайдышев Решение научных и инженерных задач средствами Excel, VBA и C/С++ Издательство: БХВ-Петербург 2004 г. - 512 стр.

Размещено на Allbest.ru

...