1. Создайте новый файл Excel, сделайте видимым панель управления Элементы управления, щелкните в нем по элементу управления Кнопка и поместите кнопку на лист Excel. Для наших целей мы будем считать, что созданная кнопка занимает две верхние строки первого листа.
2. На панели инструментов Элементы управления щелкните по кнопке Свойства (при этом созданная кнопка должна быть выделена) и настройте для свойства Caption значение "Получить данные". Воспользуйтесь свойством Font, чтобы настроить подходящий шрифт для вашей кнопки.
3. Щелкните правой кнопкой мыши по созданной вами кнопке и в контекстном меню выберите Исходный текст. Откроется редактор Visual Basic с курсором ввода на месте события Сlick для вашей кнопки.
4. В окне редактора кода в меню Tools выберите References и установите флажок напротив строки Microsoft ActiveX Data Objects 2.1 Library.
5. Код для события Сlick вашей кнопки Получить данные может быть таким, как показано далее.
Private Sub CommandButtonl_Click()
'Вначале — чистим всю книгу от старых данных
Cells.Select Selection.Clear
'Создаем и настраиваем объект Connection
Dim en As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
& "Data Source=C:\Program Files\Microsoft Office\OFFICEll\SAMPLES\Бopeй.mdb"
en.Open
'Создаем и настраиваем объект Recordset
Dim rs As New ADODB.Recordset
rs.Open "SELECT [КодТовара], [Марка], [Цена], [НаСкладе]," _
& "[МинимальныйЗапас], [ПоставкиПрекращены] FROM Товары", cn
'На основе Recordset создаем объект QueryTable и
'вставляем его, начиная с 4-й строки
Dim QT1 As QueryTable
Set QT1 = QueryTables.Add(rs, Range("A4"))
QT1.Refresh
'Определяем количество записей в QueryTable
Dim nRowCount As Integer
Dim oRange As Range
Set oRange - QTl.ResultRange
nRowCount = oRange.Rows.Count
'Формируем столбец "Заказать товара, штук"
Range("G4").Value = "Заказать товара, штук"
Range("G4").Font.Bold = True
Range("G4").Columns.AutoFit
'Формируем столбец "Стоимость заказа"
Range("H4").Value = "Стоимость заказа"
Range("H4").Font.Bold = True
Range("H4").Columns.AutoFit
'Создаем диапазон, который включит в себя столбец G
' "вдоль" QueryTable
Set oRange = Range("G5", "G" & nRowCount + 3)
'Готовим переменные, которые нам потребуются в цикле
Dim oCell As Range
Dim sRowNumber As String
Dim cMoney As Currency
Dim cltogMoney As Currency
Dim cltogSklad As Currency
'Проходим циклом по всем ячейкам созданного диапазона
For Each oCell In oRange.Cells
'Получаем абсолютный номер строки в виде строковой переменной
sRowNumber = Replace(oCell.Address(True), "$G$", "")
'Проверяем определенные нами условия
If Range("E" & sRowNumber).Value > Range("D" & sRowNumber) And _
Range("F" & sRowNumber).Value = False Then
'Получаем значение для столбца G (заказ в. штуках) oCell.Value = (CInt(Range("E" & sRowNumber).Value) -_
CInt(Range("D" & sRowNumber).Value))
'Получаем значение для столбца Н (стоимость заказа)
cMoney = (CInt(Range-("E" & sRowNumber) .Value) — _
CInt(Range("D" & sRowNumber).Value)) *_
CCur(Range("C" & sRowNumber).Value)
'Записываем его в столбец Н
Range("Н" & sRowNumber).Value = cMoney
'Сразу плюсуем к итогу в рублях
cltogMoney = cltogMoney + cMoney
End If
'И в том же цикле сразу суммируем стоимость товаров на складе cltogSklad = cltogSklad + (Range("С" & sRowNumber).Value * _
Range("D" & sRowNumber).Value)
Next
'Формируем две строки с итогами
Range("В" & nRowCount+6).Value = "Общая стоимость товаров на складе:"
Range("В" & nRowCount+6).Font.Bold = True
Range("В" & nRowCount+7).Value = "Общая стоимость товаров к заказу:"
Range("В" & nRowCount + 7).Font.Bold = True
Range("D" & nRowCount + 6).Value = cltogSklad
Range("D" S nRowCount + 6).Font.Bold = True
Range("D" & nRowCount + 7).Value = cltogMoney
Range("D" & nRowCount + 7).Font.Bold = True
'Для красоты выделяем итоговое значение ...
Range("D" & nRowCount + 7).Select
'... и производим скроллирование
Range("D" & nRowCount + 7).Show
End Sub
