Автор: Пользователь скрыл имя, 10 Июня 2014 в 22:50, дипломная работа
В данном дипломном проекте была решена задача по автоматизации учета основных средств на предприятии ООО "Алеф". В качестве основы для автоматизации был выбран и сконфигурирован под нужды предприятия комплекс УСН 1С Бухгалтерия. Второй частью создания АРМ бухгалтера была разработка отдельного программного блока для формирования унифицированных форм учета основных средств предприятия. Программный блок был разработан в СУБД MS Access, что позволяет автономно быстро и легко формировать унифицированные формы документов. Основными плюсами разработанного модуля является его относительная автономность, низкая экономическая затратность, легкость с точки зрения технологической установки, а также широкие возможности по дальнейшему усовершенствованию. Проведена предварительная работа по подбору технических средств реализации блока, проанализирована и доказана его экономическая окупаемость. Продумана дальнейшая оптимизация бухгалтерского документооборота и учета на предприятии "Алеф" - добавление полной его интеграции с экспортными данными из программного комплекса УСН 1С Бухгалтерии 7.7.
РЕФЕРАТ
ВВЕДЕНИЕ
1. АНАЛИТИЧЕСКАЯ ЧАСТЬ
1.1 ОРГАНИЗАЦИОННО-ЭКОНОМИЧЕСКАЯ ХАРАКТЕРИСТИКА ПРЕДМЕТНОЙ ОБЛАСТИ. КРАТКОЕ ОПИСАНИЕ КОМПАНИИ ООО "АЛЕФ", АНАЛИЗ АДМИНИСТРАТИВНОЙ И ХОЗЯЙСТВЕННОЙ ДЕЯТЕЛЬНОСТИ ПРЕДПРИЯТИЯ
1.1.1 ОРГАНИЗАЦИОННАЯ СТРУКТУРА ОБЪЕКТА УПРАВЛЕНИЯ
1.1.2 ФУНКЦИОНАЛЬНАЯ ХАРАКТЕРИСТИКА ОБЪЕКТА УПРАВЛЕНИЯ
1.1.3 ЭКОНОМИЧЕСКАЯ ХАРАКТЕРИСТИКА ПРЕДМЕТНОЙ ОБЛАСТИ
1.2 ЭКОНОМИЧЕСКАЯ СУЩНОСТЬ КОМПЛЕКСА ЭКОНОМИЧЕСКИХ ИНФОРМАЦИОННЫХ ЗАДАЧ. РЕАЛИЗАЦИЯ ЭКОНОМИЧЕСКОЙ МОДЕЛИ ПРЕДПРИЯТИЯ С ПОМОЩЬЮ УЧЕТНОЙ СИСТЕМЫ 1С УCН
1.2.1 ОБЩИЕ СВЕДЕНИЯ О ЗАДАЧАХ. ОПИСАНИЕ ИМЕЮЩЕЙСЯ УЧЕТНОЙ СХЕМЫ ПРЕДПРИЯТИЯ В РАМКАХ УСН 1С
1.2.2 ОБОСНОВАНИЕ ВЫБОРА ЗАДАЧ, ВХОДЯЩИХ В КОМПЛЕКС. ПРЕИМУЩЕСТВА ОСНОВНОЙ УЧЕТНОЙ СИСТЕМЫ 1С УСН, А ТАКЖЕ НЕДОСТАЮЩИЕ ЗВЕНЬЯ АВТОМАТИЗИРОВАННОГО УЧЕТА
1.2.3 СПОСОБЫ РЕШЕНИЯ ЗАДАЧИ. ОБЩАЯ МОДЕЛЬ ПОДБОРА РЕШЕНИЯ
1.3 ОБОСНОВАНИЕ ПРОЕКТНЫХ РЕШЕНИЙ ПО АВТОМАТИЗИРОВАННОМУ РЕШЕНИЮ ЭКОНОМИКО-ИНФОРМАЦИОННЫХ ЗАДАЧ. ОСНОВНЫЕ МОМЕНТЫ АВТОМАТИЗАЦИИ УЧЕТА ДЕЯТЕЛЬНОСТИ ПРЕДПРИЯТИЯ. РЕШЕНИЕ НАШИХ ЗАДАЧ С ТОЧКИ ЗРЕНИЯ АВТОМАТИЗАЦИИ УЧЕТА НА ПРЕДПРИЯТИИ
1.3.1 ОБОСНОВАНИЕ ПРОЕКТНЫХ РЕШЕНИЙ ПО ИНФОРМАЦИОННОМУ ОБЕСПЕЧЕНИЮ КОМПЛЕКСА ЗАДАЧ. ПОДБОР ИНФОРМАЦИОННОГО ОБЕСПЕЧЕНИЯ ДЛЯ БЛОКА РАБОТЫ С УНИФИЦИРОВАННЫМИ ФОРМАМИ
1.3.2 ОБОСНОВАНИЕ ПРОЕКТНЫХ РЕШЕНИЙ ПО ТЕХНОЛОГИИ СБОРА, ПЕРЕДАЧИ, ОБРАБОТКИ И ВЫДАЧИ ИНФОРМАЦИИ. РЕАЛИЗАЦИЯ НЕДОСТАЮЩЕГО В УСН БЛОКА РАБОТЫ С УНИФИЦИРОВАННЫМИ ФОРМАМИ
1.3.3 ОБОСНОВАНИЕ ПРОЕКТНЫХ РЕШЕНИЙ ПО ПРОГРАММНОМУ ОБЕСПЕЧЕНИЮ КОМПЛЕКСА ЗАДАЧ
1.3.4 ОБОСНОВАНИЕ НЕОБХОДИМОСТИ ИСПОЛЬЗОВАНИЯ ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ И СОЗДАНИЯ АРМ ДЛЯ РЕШЕНИЯ ДАННОГО КОМПЛЕКСА ЗАДАЧ
2. ПРОЕКТНАЯ ЧАСТЬ
2.1 ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ КОМПЛЕКСА ЗАДАЧ
2.1.1 ИНФОЛОГИЧЕСКАЯ (ИНФОРМАЦИОННАЯ) МОДЕЛЬ (СХЕМА ДАННЫХ) И ЕЕ ОПИСАНИЕ
2.1.2 ИСПОЛЬЗУЕМЫЕ КЛАССИФИКАТОРЫ И СИСТЕМЫ КОДИРОВАНИЯ
2.1.3 ХАРАКТЕРИСТИКА ВХОДНОЙ ИНФОРМАЦИИ
2.1.4 НОРМАТИВНО-СПРАВОЧНАЯ ИНФОРМАЦИЯ
2.1.5 ХАРАКТЕРИСТИКА РЕЗУЛЬТАТНОЙ ИНФОРМАЦИИ
2.2 ВНУТРИМАШИННАЯ РЕАЛИЗАЦИЯ КОМПЛЕКСА ЗАДАЧ
2.2.1 ФОРМАЛИЗАЦИЯ РАСЧЕТОВ (АЛГОРИТМЫ РАСЧЕТА И РЕШЕНИЯ ЗАДАЧ)
2.2.2 СТРУКТУРНАЯ СХЕМА ИСПОЛЬЗОВАНИЯ КОМПЛЕКСА ПРОГРАММ (ДЕРЕВО ДИАЛОГА). ВЗАИМОДЕЙСТВИЕ УСН С БЛОКОМ ФОРМИРОВАНИЯ УНИФИЦИРОВАННЫХ ФОРМ
2.2.3 ОПИСАНИЕ ИНТЕРФЕЙСА ПРИЛОЖЕНИЯ.
2.3.1 ОРГАНИЗАЦИЯ ТЕХНОЛОГИИ СБОРА, ПЕРЕДАЧИ, ОБРАБОТКИ И ВЫДАЧИ ИНФОРМАЦИИ
2.3.2 СХЕМА ТЕХНОЛОГИЧЕСКОГО ПРОЦЕССА СБОРА, ПЕРЕДАЧИ, ОБРАБОТКИ И ВЫДАЧИ ИНФОРМАЦИИ
2.4 ПРОГРАММНОЕ ОБЕСПЕЧЕНИЕ КОМПЛЕКСА ЗАДАЧ
2.4.1 ОБЩИЕ ПОЛОЖЕНИЯ
2.4.2 СТРУКТУРНАЯ СХЕМА ПАКЕТА (ДЕРЕВО ВЫЗОВА ПРОЦЕДУР И ПРОГРАММ). ОПИСАНИЕ ПРОГРАММНЫХ МОДУЛЕЙ
2.5 СХЕМА ВЗАИМОСВЯЗИ ПРОГРАММНЫХ МОДУЛЕЙ И ИНФОРМАЦИОННЫХ ФАЙЛОВ
2.6 ВЫБОР И ОБОСНОВАНИЕ ТЕХНИЧЕСКИХ СРЕДСТВ
2.7 ЭКОНОМИЧЕСКОЕ ОБОСНОВАНИЕ ПРОЕКТА
ЗАКЛЮЧЕНИЕ
СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ
Private Const rPrim2_2 As Integer = 27
Sub PrintFormOS2 (ByVal nomer As Long)
Dim db As Database, Rec As DAO. Recordset, RecList As DAO. Recordset
Dim oApp As Object
Dim StrFormName As String
Dim StrFile As String, s_folder As String, StrPath As String
Dim StrGlBuch As String
Dim StrFirmName As String, StrFirmOKPO As String
Dim StrPodrazdName1 As String, StrPodrazdOKPO1 As String
Dim StrPodrazdName2 As String, StrPodrazdOKPO2 As String
Dim StrDate As Date, StrDate_s As Date, StrDate_p As Date
Dim StrNomer As String
Dim StrSotrName1 As String, StrSotrNomer1 As String, StrSotrDolzh1 As String
Dim StrSotrName2 As String, StrSotrNomer2 As String, StrSotrDolzh2 As String
Dim StrItog As Double, s_Sum As Double
Dim StrMonth1 As String, StrMonth2 As String
Dim p As Integer, p2 As Integer, i As Long, NRecord As Long
Dim StrPrim As String
On Error GoTo LblErr
If nomer = 0 Then Exit Sub
s_folder = CurrentProject. Path
If Right$ (s_folder,
1) <> "\" Then s_folder = s_folder + "\"
s_folder = s_folder + "blanks\"
If Len (Dir$ (s_folder, vbDirectory)) = 0 Then
MsgBox "Путь к папке с бланками " & s_folder & " не обнаружен!", vbCritical + vbOKOnly
Exit Sub
End If
Set db = CurrentDb
Set Rec = db. OpenRecordset ("select * from Формы where НомерФорма = " & NomerForm, dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrFormName = Rec. Fields ("Наименование"). Value
StrFile = Rec. Fields ("Файл"). Value
Else
Set Rec = Nothing
MsgBox "Нет информации о форме №" & NomerForm & "!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
StrPath = s_folder + StrFile
If Len (Dir$ (StrPath)) = 0 Then
MsgBox "Файл бланка формы '" & StrFormName & "' " & StrPath & " не обнаружен!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = db. OpenRecordset ("SELECT Параметры. *, Сотрудники. Сотрудник FROM Сотрудники INNER JOIN Параметры ON Сотрудники. НомерСотр = Параметры. ГлБухгалтер", dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrFirmName = Nz (Rec. Fields ("НаименованиеФирмы"). Value, "")
StrFirmOKPO = Nz (Rec. Fields ("ОКПО"). Value, "")
StrGlBuch = Nz (Rec. Fields ("Сотрудник"). Value, "")
Else
MsgBox "Общие параметры фирмы не занесены!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from запрос_ВнутренниеНакл where НомерНакл = " & nomer, dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrPodrazdName1 = Nz (Rec. Fields ("p1"). Value, "")
StrPodrazdOKPO1 = Nz (Rec. Fields ("p1_okpo"). Value, "")
StrPodrazdName2 = Nz (Rec. Fields ("p2"). Value, "")
StrPodrazdOKPO2 = Nz (Rec. Fields ("p2_okpo"). Value, "")
StrDate = Nz (Rec. Fields ("ДатаНакл"). Value, Date)
StrDate_s = Nz (Rec. Fields ("ДатаНаклСдал"). Value, Date)
StrDate_p = Nz (Rec. Fields ("ДатаНаклПринял"). Value, Date)
StrNomer = Nz (Rec. Fields ("НомерНаклВнутр"). Value, nomer)
StrSotrName1 = Nz (Rec. Fields ("s1"). Value, "")
StrSotrNomer1 = Nz (Rec. Fields ("s1_nomer"). Value, "")
StrSotrDolzh1 = Nz (Rec. Fields ("s1_dolzh"). Value, "")
StrSotrName2 = Nz (Rec. Fields ("s2"). Value, "")
StrSotrNomer2 = Nz (Rec. Fields ("s2_nomer"). Value, "")
StrSotrDolzh2 = Nz (Rec. Fields ("s2_dolzh"). Value, "")
StrPrim = Nz (Rec. Fields ("Примечание"). Value, "")
Else
MsgBox "Накладная №" & nomer & " не найдена!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDate_s), dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrMonth1 = Nz (Rec. Fields ("НазвМес"). Value, "")
Else
StrMonth1 = "нет названия"
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDate_p), dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrMonth2 = Nz (Rec. Fields ("НазвМес"). Value, "")
Else
StrMonth2 = "нет названия"
End If
Set Rec = Nothing
Set oApp = CreateObject ("Excel. Application")
oApp. Workbooks. Open FileName: =StrPath, ReadOnly: =True
Application. SysCmd acSysCmdInitMeter, "Вывод информации о товарах", 100
oApp. ActiveWorkbook. Sheets (1). Select
StrItog = 0
Set RecList = db. OpenRecordset ("select * from запрос_ВнутренниеНаклТовары where НомерНакл = " & nomer, dbOpenSnapshot)
NRecord = RecList. RecordCount
If NRecord > 0 Then
RecList. MoveLast
NRecord = RecList. RecordCount
RecList. MoveFirst
i = 0
oApp. ActiveWorkbook. Sheets (1). Select
p = rSh1_1 - 1: p2 = rSh1_2
While Not RecList. EOF
i = i + 1
p = p + 1
Application. SysCmd acSysCmdUpdateMeter, i / NRecord * 100
If p > p2 Then
oApp. ActiveWorkbook. Sheets (2). Select
p = rSh2_1: p2 = rSh2_2
End If
s_Sum = Nz (RecList. Fields ("Сумма"). Value, 0)
oApp. Cells (p, cNomer). Value = i
oApp. Cells (p, cTovar). Value = Nz (RecList. Fields ("Товар"). Value, "")
oApp. Cells (p, cYear). Value = Nz (RecList. Fields ("ГодВыпуска"). Value, Year (Date))
oApp. Cells (p, cInv). Value = Nz (RecList. Fields ("ИнвКод"). Value, Year (Date))
oApp. Cells (p, cKol). Value = Nz (RecList. Fields ("Количество"). Value, 0)
oApp. Cells (p, cCena). Value = Format$ (Nz (RecList. Fields ("ЦенаРозн"). Value, 0), "0.00")
oApp. Cells (p, cSum). Value = Format$ (s_Sum, "0.00")
StrItog = StrItog + s_Sum
RecList. MoveNext
Wend
Else
MsgBox "Для накладной №" & nomer & " нет перечня товаров!", vbCritical + vbOKOnly
Exit Sub
End If
Set RecList = Nothing
oApp. Cells (rFirmName, cFirmName). Value = StrFirmName
oApp. Cells (rFirmOKPO, cFirmOKPO). Value = StrFirmOKPO
oApp. Cells (rPodrazdName1, cPodrazdName1). Value = StrPodrazdName1
oApp. Cells (rPodrazdOKPO1, cPodrazdOKPO1). Value = StrPodrazdOKPO1
oApp. Cells (rPodrazdName2, cPodrazdName2). Value = StrPodrazdName2
oApp. Cells (rPodrazdOKPO2, cPodrazdOKPO2). Value = StrPodrazdOKPO2
oApp. Cells (rNomerNakl, cNomerNakl). Value = StrNomer
oApp. Cells (rDateNakl, cDateNakl). Value = Format$ (StrDate, "dd. mm. yyyy")
oApp. ActiveWorkbook. Sheets (2). Select
oApp. Cells (rSumItog, cSumItog). Value = " " & Format$ (StrItog, "0.00")
oApp. Cells (rSotrDolzh1, cSotrDolzh1). Value = StrSotrDolzh1
oApp. Cells (rSotrName1, cSotrName1). Value = StrSotrName1
oApp. Cells (rSotrNomer1, cSotrNomer1). Value = StrSotrNomer1
oApp. Cells (rDatDay1, cDatDay1). Value = Format$ (StrDate_s, "dd")
oApp. Cells (rDatMonth1, cDatMonth1). Value = StrMonth1
oApp. Cells (rDatYear1, cDatYear1). Value = Right$ (Format$ (StrDate_s, "yyyy"),
1)
oApp. Cells (rSotrDolzh2, cSotrDolzh2). Value = StrSotrDolzh2
oApp. Cells (rSotrName2, cSotrName2). Value = StrSotrName2
oApp. Cells (rSotrNomer2, cSotrNomer2). Value = StrSotrNomer2
oApp. Cells (rDatDay2, cDatDay2). Value = Format$ (StrDate_p, "dd")
oApp. Cells (rDatMonth2, cDatMonth2). Value = StrMonth2
oApp. Cells (rDatYear2, cDatYear2). Value = Right$ (Format$ (StrDate_p, "yyyy"),
1)
oApp. Cells (rPrim1, cPrim). Value = Left$ (StrPrim, nSymbPrim)
StrPrim = Mid$ (StrPrim, nSymbPrim + 1)
i = rPrim2_1
While Len (StrPrim) > 0
oApp. Cells (i, cPrim2). Value = Left$ (StrPrim, nSymbPrim2)
StrPrim = Mid$ (StrPrim, nSymbPrim2 + 1)
i = i + 1
If i > rPrim2_2 Then GoTo lb_ex
Wend
lb_ex:
oApp. Cells (rGlBuch, cGlBuch). Value = StrGlBuch
ex:
Application. SysCmd acSysCmdRemoveMeter
If Not (oApp Is Nothing) Then oApp. Visible = True
Set Rec = Nothing
Set RecList = Nothing
Set oApp = Nothing
Set db = Nothing
Exit Sub
LblErr:
MsgBox Err. Description, vbCritical + vbOKOnly
GoTo ex
End Sub
Код модуля OS3
Option Compare Database
Option Explicit
Private Const NomerForm As Long = 5
Private Const cFirmName As Byte = 7
Private Const rFirmName As Integer = 7
Private Const cFirmOKPO As Byte = 88
Private Const rFirmOKPO As Integer = 7
Private Const cNomer As Byte = 36
Private Const rNomer As Integer = 15
Private Const cDat As Byte = 48
Private Const rDat As Integer = 15
Private Const cIsp As Integer = 13
Private Const rIsp As Integer = 11
Private Const cIspOKPO As Integer = 88
Private Const rIspOKPO As Integer = 11
Private Const cdNomer As Integer = 88
Private Const rdNomer As Integer = 12
Private Const cdDate As Integer = 88
Private Const rdDate As Integer = 13
Private Const cPDate1 As Integer = 88
Private Const rPDate1 As Integer = 14
Private Const cPDate2 As Integer = 88
Private Const rPDate2 As Integer = 15
Private Const cFDate1 As Integer = 88
Private Const rFDate1 As Integer = 16
Private Const cFDate2 As Integer = 88
Private Const rFDate2 As Integer = 17
Private Const cRukDolzh As Byte = 61
Private Const rRukDolzh As Integer = 20
Private Const cRukName As Byte = 85
Private Const rRukName As Integer = 20
Private Const cDatRukDay As Byte = 54
Private Const rDatRukDay As Integer = 22
Private Const cDatRukMon As Byte = 58
Private Const rDatRukMon As Integer = 22
Private Const cDatRukYear As Byte = 71
Private Const rDatRukYear As Integer = 22
Private Const cTovar As Byte = 6
Private Const rTovar As Integer = 29
Private Const cInv As Byte = 30
Private Const rInv As Integer = 29
Private Const cPasp As Byte = 45
Private Const rPasp As Integer = 29
Private Const cZav As Byte = 60
Private Const rZav As Integer = 29
Private Const cOstStoim As Byte = 75
Private Const rOstStoim As Integer = 29
Private Const cFaktSrok As Byte = 90
Private Const rFaktSrok As Integer = 29
Private Const cTovar2 As Byte = 6
Private Const rTovar2 As Integer = 39
Private Const cOper As Byte = 20
Private Const rOper As Integer = 39
Private Const cDemStoim As Byte = 30
Private Const rDemStoim As Integer = 39
Private Const cPlRab As Byte = 40
Private Const rPlRab As Integer = 39
Private Const cPlRab2 As Byte = 50
Private Const rPlRab2 As Integer = 39
Private Const cFtRab As Byte = 60
Private Const rFtRab As Integer = 39
Private Const cFtRab2 As Byte = 70
Private Const rFtRab2 As Integer = 39
Private Const cTransp As Byte = 80
Private Const rTransp As Integer = 39
Private Const ciDemStoim As Byte = 30
Private Const riDemStoim As Integer = 41
Private Const ciPlRab As Byte = 40
Private Const riPlRab As Integer = 41
Private Const ciPlRab2 As Byte = 50
Private Const riPlRab2 As Integer = 41
Private Const ciFtRab As Byte = 60
Private Const riFtRab As Integer = 41
Private Const ciFtRab2 As Byte = 70
Private Const riFtRab2 As Integer = 41
Private Const ciTransp As Byte = 80
Private Const riTransp As Integer = 41
Private Const cVip1 As Byte = 34
Private Const rVip1 As Integer = 3
Private Const cVip2 As Byte = 34
Private Const rVip2 As Integer = 4
Private Const cVipInf As Byte = 44
Private Const rVipInf As Integer = 3
Private Const cPredsDolzh As Byte = 17
Private Const rPredsDolzh As Integer = 13
Private Const cChl1Dolzh As Byte = 17
Private Const rChl1Dolzh As Integer = 15
Private Const cChl2Dolzh As Byte = 17
Private Const rChl2Dolzh As Integer = 17
Private Const cPredsName As Byte = 51
Private Const rPredsName As Integer = 13
Private Const cChl1Name As Byte = 51
Private Const rChl1Name As Integer = 15
Private Const cChl2Name As Byte = 51
Private Const rChl2Name As Integer = 17
Private Const cPrinDolzh As Byte = 17
Private Const rPrinDolzh As Integer = 30
Private Const cPrinName As Byte = 51
Private Const rPrinName As Integer = 30
Private Const cDatPrinDay As Byte = 79
Private Const rDatPrinDay As Integer = 30
Private Const cDatPrinMon As Byte = 83
Private Const rDatPrinMon As Integer = 30
Private Const cDatPrinYear As Byte = 96
Private Const rDatPrinYear As Integer = 30
Private Const cSdalDolzh As Byte = 17
Private Const rSdalDolzh As Integer = 22
Private Const cSdalName As Byte = 51
Private Const rSdalName As Integer = 22
Private Const cDatSdalDay As Byte = 79
Private Const rDatSdalDay As Integer = 22
Private Const cDatSdalMon As Byte = 83
Private Const rDatSdalMon As Integer = 22
Private Const cDatSdalYear As Byte = 96
Private Const rDatSdalYear As Integer = 22
Private Const сGlBuch As Byte = 30
Private Const rGlBuch As Integer = 38
Sub PrintFormOS3 (ByVal nomer As Long)
Dim db As Database, Rec As DAO. Recordset, RecList As DAO. Recordset
Dim oApp As Object
Dim StrFormName As String
Dim StrFile As String, s_folder As String, StrPath As String
Dim StrGlBuch As String
Dim StrFirmName As String, StrFirmOKPO As String, StrFirmAddr As String, StrFirmReq As String
Dim NomerVnutr As String, StrDate As Date
Dim StrTovar As String, StrInv As String
Dim StrPasp As String, StrZav As String
Dim StrIsp As String, StrIspOKPO As String
Dim StrOper As String, StrdNomer As String, StrdDate As Date
Dim StrPDate1 As Date, StrPDate2 As Date
Dim StrFDate1 As Date, StrFDate2 As Date
Dim StrRukName As String, StrRukDolzh As String
Dim StrDatePodp As Date
Dim StrOstStoim As Double, StrFaktSrok As Long
Dim StrDemStoim As Double, StrPlRab As Double, StrPlRab2 As Double
Dim StrFtRab As Double, StrFtRab2 As Double, StrTransp As Double
Dim vbVip As Boolean, StrNoVip As String
Dim StrPredsName As String, StrPredsDolzh As String
Dim StrChl1Name As String, StrChl1Dolzh As String
Dim StrChl2Name As String, StrChl2Dolzh As String
Dim StrPrinName As String, StrPrinDolzh As String
Dim StrSdalName As String, StrSdalDolzh As String
Dim StrPrinDate As Date, StrSdalDate As Date
Dim StrMonthPodp As String, StrMonthPrin As String, StrMonthSdal As String
On Error GoTo LblErr
If nomer = 0 Then Exit Sub
s_folder = CurrentProject. Path
If Right$ (s_folder,
1) <> "\" Then s_folder = s_folder + "\"
s_folder = s_folder + "blanks\"
If Len (Dir$ (s_folder, vbDirectory)) = 0 Then
MsgBox "Путь к папке с бланками " & s_folder & " не обнаружен!", vbCritical + vbOKOnly
Exit Sub
End If
Set db = CurrentDb
Set Rec = db. OpenRecordset ("select * from Формы where НомерФорма = " & NomerForm, dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrFormName = Rec. Fields ("Наименование"). Value
StrFile = Rec. Fields ("Файл"). Value
Else
Set Rec = Nothing
MsgBox "Нет информации о форме №" & NomerForm & "!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
StrPath = s_folder + StrFile
If Len (Dir$ (StrPath)) = 0 Then
MsgBox "Файл бланка формы '" & StrFormName & "' " & StrPath & " не обнаружен!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = db. OpenRecordset ("SELECT Параметры. *, Сотрудники. Сотрудник FROM Сотрудники INNER JOIN Параметры ON Сотрудники. НомерСотр = Параметры. ГлБухгалтер", dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrFirmName = Nz (Rec. Fields ("НаименованиеФирмы"). Value, "")
StrFirmOKPO = Nz (Rec. Fields ("ОКПО"). Value, "")
StrGlBuch = Nz (Rec. Fields ("Сотрудник"). Value, "")
StrFirmAddr = Nz (Rec. Fields ("ЮрАдрес"). Value, "")
StrFirmReq = Nz (Rec. Fields ("БанкРеквизиты"). Value, "")
Else
MsgBox "Общие параметры фирмы не занесены!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from запрос_АктыРемонта where НомерАктаРемонта = " & nomer, dbOpenSnapshot)
If Rec. RecordCount > 0 Then
NomerVnutr = Nz (Rec. Fields ("НомерВнутр"). Value, nomer)
StrDate = Nz (Rec. Fields ("ДатаАкта"). Value, Date)
StrTovar = Nz (Rec. Fields ("Товар"). Value, "")
StrInv = Nz (Rec. Fields ("ИнвКод"). Value, "")
StrPasp = Nz (Rec. Fields ("НомерПоПаспорту"). Value, "")
StrZav = Nz (Rec. Fields ("НомерЗавод"). Value, "")
StrIsp = Nz (Rec. Fields ("Исполнитель"). Value, "")
StrIspOKPO = Nz (Rec. Fields ("isp_okpo"). Value, "")
StrOper = Nz (Rec. Fields ("ВидРаботы"). Value, "")
StrdNomer = Nz (Rec. Fields ("НомерДоговора"). Value, "")
StrdDate = Nz (Rec. Fields ("ДатаДоговора"). Value, Date)
StrPDate1 = Nz (Rec. Fields ("ПериодРемПлан1"). Value, Date)
StrPDate2 = Nz (Rec. Fields ("ПериодРемПлан2"). Value, Date)
StrFDate1 = Nz (Rec. Fields ("ПериодРемФакт1"). Value, Date)
StrFDate2 = Nz (Rec. Fields ("ПериодРемФакт2"). Value, Date)
StrRukName = Nz (Rec. Fields ("ruk_name"). Value, "")
StrRukDolzh = Nz (Rec. Fields ("ruk_dolzhn"). Value, "")
StrDatePodp = Nz (Rec. Fields ("ДатаПодписи"). Value, Date)
StrOstStoim = Nz (Rec. Fields ("ОстСтоииость"). Value, 0)
StrFaktSrok = Nz (Rec. Fields ("ФактСрокЭкспл"). Value, 0)
StrOper = Nz (Rec. Fields ("ВидРаботы"). Value, "")
StrDemStoim = Nz (Rec. Fields ("СтоимДемонт"). Value, 0)
StrPlRab = Nz (Rec. Fields ("СтоимРаботПлан"). Value, 0)
StrPlRab2 = Nz (Rec. Fields ("СтоимРаботПлан2"). Value, 0)
StrFtRab = Nz (Rec. Fields ("СтоимРаботФакт"). Value, 0)
StrFtRab2 = Nz (Rec. Fields ("СтоимРаботФакт2"). Value, 0)
StrTransp = Nz (Rec. Fields ("СтоимТрансп"). Value, 0)
vbVip = Nz (Rec. Fields ("Полностью"). Value, True)
StrNoVip = Nz (Rec. Fields ("ЧтоНеПолн"). Value, "")
StrPredsName = Nz (Rec. Fields ("preds_name"). Value, "")
StrPredsDolzh = Nz (Rec. Fields ("preds_dolzhn"). Value, "")
StrChl1Name = Nz (Rec. Fields ("chlen1_name"). Value, "")
StrChl1Dolzh = Nz (Rec. Fields ("chlen1_dolzhn"). Value, "")
StrChl2Name = Nz (Rec. Fields ("chlen2_name"). Value, "")
StrChl2Dolzh = Nz (Rec. Fields ("chlen2_dolzhn"). Value, "")
StrPrinName = Nz (Rec. Fields ("prin_name"). Value, "")
StrPrinDolzh = Nz (Rec. Fields ("prin_dolzhn"). Value, "")
StrPrinDate = Nz (Rec. Fields ("ДатаПриемки"). Value, Date)
StrSdalName = Nz (Rec. Fields ("sdal_name"). Value, "")
StrSdalDolzh = Nz (Rec. Fields ("sdal_dolzhn"). Value, "")
StrSdalDate = Nz (Rec. Fields ("ДатаСдачи"). Value, Date)
StrGlBuch = Nz (Rec. Fields ("glbuch_name"). Value, "")
Else
MsgBox "Акт сдачи-приемки отремонт. ОС №" & nomer & " не найден!", vbCritical + vbOKOnly
Exit Sub
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrDatePodp), dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrMonthPodp = Nz (Rec. Fields ("НазвМес"). Value, "")
Else
StrMonthPodp = "нет названия"
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrPrinDate), dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrMonthPrin = Nz (Rec. Fields ("НазвМес"). Value, "")
Else
StrMonthPrin = "нет названия"
End If
Set Rec = Nothing
Set Rec = db. OpenRecordset ("select * from ВспомДата where НомерМес = " & Month (StrSdalDate), dbOpenSnapshot)
If Rec. RecordCount > 0 Then
StrMonthSdal = Nz (Rec. Fields ("НазвМес"). Value, "")
Else
StrMonthSdal = "нет названия"
End If
Set Rec = Nothing
Set oApp = CreateObject ("Excel. Application")
oApp. Workbooks. Open FileName: =StrPath, ReadOnly: =True
oApp. ActiveWorkbook. Sheets (1). Select
oApp. Cells (rFirmName, cFirmName). Value = StrFirmName
oApp. Cells (rFirmOKPO, cFirmOKPO). Value = StrFirmOKPO
oApp. Cells (rNomer, cNomer). Value = NomerVnutr
oApp. Cells (rDat, cDat). Value = Format$ (StrDate, "dd. mm. yyyy")
oApp. Cells (rIsp, cIsp). Value = StrIsp
oApp. Cells (rIspOKPO, cIspOKPO). Value = StrIspOKPO
oApp. Cells (rdNomer, cdNomer). Value = StrdNomer
oApp. Cells (rdDate, cdDate). Value = Format$ (StrdDate, "dd. mm. yyyy")
oApp. Cells (rPDate1, cPDate1). Value = Format$ (StrPDate1, "dd. mm. yyyy")
oApp. Cells (rPDate2, cPDate2). Value = Format$ (StrPDate2, "dd. mm. yyyy")