Практическая работа 44. Использование объектов Excel и Access: результаты SQL-запроса передать средствами языка программирования для дальнейшей математической обработки в Excel

Date: 2015-10-07; view: 411.

1. Создать элементарный фрагмент базы данных, см. Подключить 3 библиотеки (для обеспечения работы с DAO, Access и Excel): Microsoft Excel 10.0 Object Library, Microsoft Access 10.0 Object Library, Microsoft DAO 3,51Object Library

2. создать на форме () поле списка (Name = lst) свойству Microsoft присвоить значение, обеспечивающее возможность одновременной маркировки нескольких строк, 3 командных кнопки - cmd1 (Caption = Access), cmd2, Caption = Excel), cmdEnd, CommonDialog (Name = cdlg), cм. 6.2, (для обеспечения диалога с пользователем при передаче данных Access Exсel), см. код процедуры Private Sub cmd2_Click(). Элемент управления CommonDialog не виден на т.к. там изображена форма после старта проекта,

3. ввести код, данный ниже, стартовать проект, сделать щелчок на кнопке Access - имена полей таблицы базы данных выводятся в поле списка lst,

4. в поле lst (на - Fields) маркировать поля, которые нужно передать в приложение Excel для дальнейшей математической обработки; далее сделать щелчок на кнопке Excel,

5. подтвердить желание сохранить файл: сделать щелчок на кнопке ОК в окне MsgBox, ввести имя файла - выводится сообщение о том, что данные Access сохранены на листе Exсel,

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

' Средствами DAO, см. процедуру cmd1_Click(), выполня-

' ется работа с таблицей nin2 базы данных nin1.mdb и

' имена полей таблицы, (см. цикл For Next), заносятся в

' поле списка lst. Далее пользователь должен выбрать

' поля таблицы для переноса в приложение Excel

Private Sub cmd1_Click()

Dim ws As Workspace, db As Database

Dim tdef As TableDef, i As Integer

lst.Clear

Set ws = DBEngine.Workspaces(0)

Set db = ws.OpenDatabase("A:\nin1.mdb")

Set tdef = db.TableDefs("nin2")

For i = 0 To tdef.Fields.Count - 1

lst.AddItem tdef.Fields(i).Name

Next i

db.Close: ws.Close

End Sub

' программируется передача таблицы из Access в Excel.

' Cначала объявляются новые объекты Excel, затем - DAO

Private Sub cmd2_Click()

' объект Excel.Application содержит коллекцию Workbooks

Dim xla As New Excel.Application

' объект Workbook содержит коллекцию Worksheet

Dim xlb As New Excel.Workbook

' объект Worksheet содержит ячейки

Dim xls As New Excel.Worksheet

' объект Range - непрерывный набор ячеек

Dim xlr As Excel.Range

Dim ws As Workspace, db As Database, rs As Recordset

Dim i As Integer, j As Integer, k As Integer, rc As Integer

Dim r As Integer, c As Integer

' описание массива полей таблицы базы данных, выделенных

' пользователем в поле lst после старта проекта и

' щелчка на кнопке Access

Dim masFields() As String

' определить выделенные пользователем в поле списка

' lst поля (таблицы базы данных) для переноса в Excel и

' определить размерность массива masFields()

' для их (выделенных полей) хранения (ReDim -

' изменить размерность массива)

For i = 0 To lst.ListCount - 1

If lst.Selected(i) = True Then j = j + 1

Next i

ReDim masFields(j - 1)

' занести данные в массив masFields(j) из поля

' списка (свойство ListCount содержит число

' элементов списка)

j = 0

For i = 0 To lst.ListCount - 1

If lst.Selected(i) = True Then masFields(j) = lst.List(i): j = j + 1

Next i

' создать рабочее пространство, открыть базу данных

Set ws = DBEngine.Workspaces(0)

Set db = ws.OpenDatabase("A:\nin1")

' создать объекты Excel (экземпляр книги и листа) и

' активизировать лист Excel

Set xlb = xla.Workbooks.Add

Set xls = xlb.Worksheets.Add

xls.Activate

' в цикле For : Next перебрать выбранные пользователем и

' занесeнные ранее в массив поля. Для каждого поля

' формируется SQL-запрос. Далее просматривается

' весь набор записей для определения количества

' соответствующих записей

For i = 0 To UBound(masFields)

Set rs = db.OpenRecordset("SELECT nin2." & masFields(i) & " _

FROM nin2")

rs.MoveLast: rs.MoveFirst

' выбрать номера столбцов таблицы Excel, где будут

' размещены выбранные данные. В первую строку поместить

' названия полей. Ячейки первой строки делаются

' объектом xlr и выделяются

c = i + 1

xls.Cells(1, c) = masFields(i)

Set xlr = xls.Cells(1, c)

xlr.Select

xlr.Font.Bold = True

' ограничить набор данных некоторым числом (например, 10)

If rs.RecordCount > 10 Then rc = 10 Else rc = rs.RecordCount

' в тот же столбец таблицы (аргумент с) перенести

' все записи. Далее (цикл For i : Next i) - возврат к

' началу цикла для перебора всех полей

For r = 2 To rc + 1

xls.Cells(r, c) = rs(masFields(i))

rs.MoveNext

Next r

Next i

' сообщение пользователю о передаче данных в Excel;

' вопрос о необходимости сохранения их в файле, ввод в

' диалоговом режиме имени файла; сообщение о

' сохранении/несохранении файла; закрыть объект -

' приложение Excel. Синтаксис функции MsgBox дан

' после кода

If MsgBox("I have transported the Access data " & "to an Excel." &_

vbCrLf & vbCrLf & "Do you want to save ?", vbQuestion & _

vbYesNo) = vbYes Then

cdlg.FileName = ""

cdlg.ShowSave

xls.SaveAs (cdlg.FileName)

MsgBox "Data has been saved to file:" & vbCrLf & vbCrLf &_

cdlg.FileName, vbInformation, "Data has been saved"

Else

MsgBox "Data has not been saved!", vbCritical, "Data has not been saved!"

End If

xlb.Saved = True

xla.Quit

End Sub

Рис. 6.9.Передача данных Access - Excel, вид формы после старта проекта и результат на листе Excel

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

Рис. 6.10.Функция MsgBox. Диалоговое окно

Возвращаемое функцией MsgBox значение присваивается переменной целого типа.

Упрощeнный синтаксис функции: MsgBox(Prompt[, Buttons][, Title]), здесь:

· Prompt - сообщение пользователя в выводимом диалоговом окне, на : A Happy New Millenniun !!!,

· Buttons - константа, определяющая наличие в диалоговом окне кнопок/пикторгаммм, см.

· Title - текст заголовка диалогового окна, на : MsgBox-Study.

Пример: MsgBox("A Happy New Millennium !!!", vbOKOnly + vbExclamation, "MsgBox()-Study") После ответного действия пользователя возвращается код, по которому можно определить кнопку, нажатую пользователем,

При работе с функцией MsgBox после ввода имени функции, пробела/открывающейся скобки выводится всплывающая подсказка, содержащая синтаксис функции. После набора сообщения пользователя - Prompt и ввода символа "запятая" выводится окно со списоком констант; можно выбрать требуемую из списка (для этого нужно использовать сочетание клавиш CTRL и Enter) или ввести константу вручную. Из всплывающего списка после ввода символа + можно выбрать следующую константу. Возвращаемое функциейзначение может использоваться для управления ходом выполения программы.

Пример: переменной ккКК присваивается возвращаемое функцией значение, это значение используется далее:

kkKK = MsgBox("Do you work ?", vbYesNoCancel + vbExclamation, "MsgBox()-Study")

If kkKK = vbYes Then

Form1.Print "He works !!"

ElseIf kkKK = vbNo Then

Form1.Print "He does not work !!"

Else: Form1.Print "He won't work !!"

End If

Если возвращаемое функцией значение не используется, то используется другой синтаксис, скобки опускаются; пример: MsgBox "Kindest regards and best wishes !", 0, "regards"