Формирование отчетов баз данных Access в формате Excel
Отчет Access имеет известные достоинства, такие как простота формирования его структуры и
мощный аппарат компоновки исходных данных. Но есть и неудобства: пользователь для
изменения этого отчета должен вносить изменения в исходные данные базы, или, того хуже, обращаться к Разработчику для внесения поправок (напр. нужен раздел "Кому, от кого").
Есть еще недостатки, описывать их, думаю, не стоит: понятно.
Предлагается решение:
- Создание локальной исходной таблицы — источника (t3_Sborka).
- Копирование исходных данных отчета в t3_Sborka с мин. преобразованием типов данных
(здесь в t3_Sborka данные сохраняются в поле формата STRING).
… и далее по F1: - Формирование на основе t3_Sborka перекрестного запроса NewQueryDef.
- Вывод в формат Excel NewQueryDef.
Уничтожение — NewQueryDef и t3_Sborka.
Я по этой теме уже публиковался на
http://www.cyberforum.ru/ms-access/thread2158558.html
но речь там идет об обратном процессе — о сохранении уже готовых таблиц Excel в БД.
Подробнее на примере БД со схемой БД учета трудоемкости изготовления изделия по заданному технологическому маршруту, где значение записи поля Итог в таблице Маршруты есть трудоемкость изготовления конкретной детали в одном из цехов предприятия. Здесь предлагается рассматривать изделие как простую совокупность входящих деталей и на практике дело намного сложнее, но это уже другая тема.
где такой код Basic :
Dim qdfNew As QueryDef
Dim D, N
Otvet = MsgBox("Обработать данные", vbOKOnly, "Отчеты Excel")
Call Заполнитьt3_Sborka
' Создайте временный QueryDef.
Set qdfNew = CurrentDb.CreateQueryDef("ДеталиОперации", "TRANSFORM Max(t3_Sborka.NCH) AS [Max-NCH] SELECT " _
& "t3_Sborka.NumStroka FROM t3_Sborka GROUP BY t3_Sborka.NumStroka ORDER BY " _
& "t3_Sborka.NumStroka, t3_Sborka.NumStolbec PIVOT t3_Sborka.NumStolbec;")
Call closeProject("Excel") 'закрыть процесс Excel
' Откройте набор записей и распечатайте отчет.
DoCmd.OutputTo acOutputQuery, "ДеталиОперации", "ExcelWorkbook(*.xlsx)", "Таблица.xlsx", True, "", , acExportQualityScreen
' Удалите новый QueryDef, потому что это демонстрация.
CurrentDb.QueryDefs.Delete "ДеталиОперации"
' Удалите источник, потому что это демонстрация.
CurrentDb.TableDefs.Delete "t3_Sborka"
Exit_Кн_Отчеты_Click:
Exit Sub
End Sub
Sub Заполнитьt3_Sborka()
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset
Dim NStroka As Long, NStolb As Long
'создать t3_Sborka
CurrentDb.Execute ("CREATE TABLE t3_Sborka (t3Cod COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY, NumStroka LONG, " _
& "NumStolbec LONG, NCH STRING);")
'три выборки:
Set rs1 = CurrentDb.OpenRecordset("SELECT Детали.КодДетали, Детали.ДецимДетали, Сборки.КолДеталей FROM Детали INNER JOIN Сборки ON " _
& "Детали.КодДетали = Сборки.КодДетали WHERE Сборки.КодИзделия=" & Me.КодИзделия & " ORDER BY Сборки.КодСборки;")
Set rs2 = CurrentDb.OpenRecordset("Цеха")
Set rs3 = CurrentDb.OpenRecordset("t3_Sborka")
<cut/>
'заполнить сетку с данными
NStroka = 0
With rs3
Do Until rs1.EOF
NStroka = NStroka + 1
NStolb = 2
Do Until rs2.EOF
NStolb = NStolb + 1
.AddNew
!NumStroka = NStroka
!NumStolbec = NStolb
!NCH = DLookup("Итог", "Маршруты", "КодДетали=" & rs1!КодДетали & " And КодЦеха=" & rs2!КодЦеха)
.Update
rs2.MoveNext
Loop
rs2.MoveFirst
rs1.MoveNext
Loop
End With
rs2.Close
'заголовки строк
rs1.MoveFirst
NStroka = 0
With rs3
Do Until rs1.EOF
NStroka = NStroka + 1
.AddNew
!NumStroka = NStroka
!NumStolbec = 1
!NCH = rs1!ДецимДетали
.Update
.AddNew
!NumStroka = NStroka
!NumStolbec = 2
!NCH = rs1!КолДеталей
.Update
rs1.MoveNext
Loop
.Close
End With
rs1.Close
Exit_Заполнитьt3_Sborka:
Exit Sub
End Sub
Sub closeProject(proc As String) 'закрыть процесс Excel
Dim Process As Object, i
For Each Process In GetObject("winmgmts:").ExecQuery("Select * from Win32_Process")
i = i + 1
If Process.Caption Like "*" & proc & "*" Then
Process.Terminate
End If
Next
End Sub
Выдает итоговый отчет

Конечно, в коде заполнения источника t3_Sborka можно предусмотреть и заголовки, и поля итогов. Простор широкий, а получаемый результат, думаю, окупит затраченные усилия.