Тема: OLE error 800A9D00 при формировании отчёта
Добрый день!
Потребовалось добавить данные для выгрузки в уже существующий отчёт. Возникла ошибка OLE error 800A9D00.
При этом параметры безопасности Excel, Access настроены. Не пойму, возможно я где-то что-то не дописала.
Отчёт формируется благодаря:
макрос до:
Attribute VB_Name = "SetParam"
Sub SetPar(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30)
'
'
Dim Rpt As Object
Set Rpt = CreateObject("CSDNRPT.Report")
Rpt.Param Application, p1
Rpt.Param Application, p2
Rpt.Param Application, p3
Rpt.Param Application, p4
Rpt.Param Application, p5
Rpt.Param Application, p6
Rpt.Param Application, p7
Rpt.Param Application, p8
Rpt.Param Application, p9
Rpt.Param Application, p10
Rpt.Param Application, p11
Rpt.Param Application, p12
Rpt.Param Application, p13
Rpt.Param Application, p14
Rpt.Param Application, p15
Rpt.Param Application, p16
Rpt.Param Application, p17
Rpt.Param Application, p18
Rpt.Param Application, p19
Rpt.Param Application, p20
Rpt.Param Application, p21
Rpt.Param Application, p22
Rpt.Param Application, p23
Rpt.Param Application, p24
Rpt.Param Application, p25
Rpt.Param Application, p26
Rpt.Param Application, p27
Rpt.Param Application, p28
Rpt.Param Application, p29
Rpt.Param Application, p30
End Sub
макрос после, исходный:
Sub itog_sp()
Dim TextSQL As String
Set AccessCon = CreateObject("ADODB.Connection") '??????????? ? Access
Set SQLCon = CreateObject("ADODB.Connection") '??????????? ? SQL
Set AccessRS = CreateObject("ADODB.Recordset") '????? ?????? ??? Access
Set SQLRS = CreateObject("ADODB.Recordset") '????? ?????? ??? SQL
Set SQLCommand = CreateObject("ADODB.Command")
FullPathMDB = Sheets("ComplSheet").Cells(1, 2).Value '???? ?? mdb ?????
'???????????? ? Access ? ???????? ?????? ??????????? ? SQL ???????
AccessCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullPathMDB
TextSQL_Access = "select SQL_PUT from SQLPut"
AccessRS.Open TextSQL_Access, AccessCon
SQLServerStringCon = AccessRS.Fields(0).Value
AccessRS.Close
'??????? ??? ???????? ?????? ? TPVerName
TextSQL_Access = "CREATE TABLE Rass_chert([1] VARCHAR(255), [2] VARCHAR(255), [3] VARCHAR(255), " + _
"[4] VARCHAR(255), [5] VARCHAR(255), [6] VARCHAR(255), [7] VARCHAR(255), [8] VARCHAR(255), [9] VARCHAR(255), [10] VARCHAR(255), " + _
"[11] VARCHAR(255), [12] VARCHAR(255), [13] VARCHAR(255) , [14] VARCHAR(255), [15] VARCHAR(255), [16] VARCHAR(255), [17] VARCHAR(255), " + _
"[18] VARCHAR(255), [19] VARCHAR(255), [20] VARCHAR(255), [21] VARCHAR(255), [22] VARCHAR(255), [23] VARCHAR(255), [24] VARCHAR(255))"
AccessCon.Execute TextSQL_Access
SQLCon.Open SQLServerStringCon
'???????? ID ????????????
TextSQL_Access = "SELECT P4 FROM RptSheet"
AccessRS.Open TextSQL_Access, AccessCon
AccessRS.MoveFirst
If Not AccessRS.EOF Then
NmkID = AccessRS.Fields("P4").Value
'???????? ????????????? ?? SQL ? ????????? ? Access
TextSQL = "SELECT DISTINCT T.subLevel, pokup.nmk_par_value AS pokup, T.spec_format, T.spec_position, T.klass, T.nmk_note, " + _
"T.nmk_name, T.kolvo, T.kol2, T.spec_comment AS comment, spec_ver.ver_name AS spec_ver_name, " + _
"pv.par_value AS date_utv_spec, tp_ver.ver_name AS tp_ver_name, pv_tp.par_value AS date_utv_tp, " + _
"parent.nmk_note AS parent_note, parent.nmk_name AS parent_name, nmk2.nmk_name AS rascex, T.npp, " + _
"PRJVERSION.DEVICE_ID AS chert, PRJVERSION.FINISH_STATE_ID AS utverg, T.SPEC_ID, kodM.nmk_par_value AS kod_M, " + _
"DopTR.nmk_par_value AS DopTR, CASE WHEN (N.NMK_NOTUSED='T') THEN 'Да' ELSE 'Нет' END "
TextSQL = TextSQL + "FROM xxx_csoft_spec5M(" + CStr(NmkID) + ") AS T " + _
"LEFT JOIN NMK N ON N.NMK_ID=T.nmk_id " + _
"LEFT JOIN NMK parent ON parent.nmk_id = T.parent_nmk_id " + _
"LEFT JOIN NMK_PAR AS pokup ON pokup.nmk_id = T.nmk_id AND pokup.par_id = 1341 " + _
"LEFT JOIN NMK_PAR AS kodM ON kodM.nmk_id = T.nmk_id AND kodM.par_id = 1289 " + _
"LEFT JOIN NMK_PAR AS DopTR ON DopTR.nmk_id = T.nmk_id AND DopTR.par_id = 1572 " + _
"LEFT JOIN Version AS spec_ver ON (spec_ver.VER_TYPE = 'S') AND (spec_ver.VER_STATE = 1) AND (spec_ver.VER_ID = T.SP_VER_ID) " + _
"LEFT JOIN PART_VAL1 AS pv ON (pv.PARVAL_ID = T.SP_VER_ID) AND (pv.par_id = 1512) " + _
"LEFT JOIN Version AS tp_ver ON (tp_ver.VER_TYPE = 'T') AND (tp_ver.VER_STATE = 1) " + _
"AND (tp_ver.NMK_ID = T.nmk_id) AND (tp_ver.VER_ID = T.TP_VER_ID) " + _
"LEFT JOIN PART_VAL1 AS pv_tp ON (pv_tp.PARVAL_ID = T.TP_VER_ID) AND (pv_tp.par_id = 1512) " + _
"LEFT OUTER JOIN TECHNOLOGY as t2 ON (t2.TECH_ATTACH = 3) AND t2.VER_ID = tp_ver.VER_ID " + _
"LEFT OUTER JOIN nmk as nmk2 ON t2.tech_nmk_ID = nmk2.nmk_ID " + _
"LEFT JOIN PROJECTNMK ON PROJECTNMK.NMK_ID = T.nmk_id AND PROJECTNMK.NMK_ATTACH = 1" + _
"LEFT JOIN PROJECTS ON PROJECTS.prj_id = PROJECTNMK.prj_id " + _
"LEFT JOIN PRJVERSION ON PRJVERSION.prj_id = PROJECTS.prj_id AND PRJVERSION.prjver_act = 'T' " + _
"WHERE T.klass NOT IN ('ТЕХ_ДЕ(к)', 'ОБРАЗЦЫ(к)', 'СОСТ_Ч(к)') " + _
"ORDER BY T.npp"
SQLCommand.ActiveConnection = SQLCon
SQLCommand.CommandText = TextSQL
SQLCommand.CommandTimeout = 7000
Set DataSet2 = SQLCommand.Execute()
DataSet2.MoveFirst
Do While Not DataSet2.EOF
TextSQL_Access = "INSERT INTO Rass_chert(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24) VALUES ( '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(0)), "", DataSet2.Fields(0))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(1)), "", DataSet2.Fields(1))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(2)), "", DataSet2.Fields(2))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(3)), "", DataSet2.Fields(3))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(4)), "", DataSet2.Fields(4))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(5)), "", DataSet2.Fields(5))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(6)), "", DataSet2.Fields(6))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(7)), "", DataSet2.Fields(7))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(8)), "", DataSet2.Fields(8))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(9)), "", DataSet2.Fields(9))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(10)), "", DataSet2.Fields(10))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(11)), "", DataSet2.Fields(11))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(12)), "", DataSet2.Fields(12))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(13)), "", DataSet2.Fields(13))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(14)), "", DataSet2.Fields(14))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(15)), "", DataSet2.Fields(15))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(16)), "", DataSet2.Fields(16))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(17)), "", DataSet2.Fields(17))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(18)), "", DataSet2.Fields(18))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(19)), "", DataSet2.Fields(19))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(20)), "", DataSet2.Fields(20))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(21)), "", DataSet2.Fields(21))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(22)), "", DataSet2.Fields(22))), "'", "''") + "', '" + _
Replace(CStr(IIf(IsNull(DataSet2.Fields(23)), "", DataSet2.Fields(23))), "'", "''") + "')"
AccessCon.Execute TextSQL_Access
DataSet2.MoveNext
Loop
DataSet2.Close
'AccessRS.MoveNext
End If
Set SQLRS = Nothing
Set DataSet2 = Nothing
Set AccessRS = Nothing
Set SQLCon = Nothing
Set AccessCon = Nothing
Call Report
'Call GetSum
'ActiveWorkbook.ActiveSheet.Cells(4, 4) = TPVerName
'ActiveWorkbook.ActiveSheet.Cells(4, 7) = VerName2
' Rows("6:6").Select
' Range("E6").Activate
' Selection.AutoFilter
' Rows("5:6").Select
' Range("N5").Activate
' ActiveWindow.FreezePanes = True
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
End With
Range("A1:AD2").Select
Selection.AutoFilter
Range("A2:AB3").Select
ActiveWindow.FreezePanes = True
'Первый параметр - номер строки, с которой начинать построение дерева (номируется с 1)
'Второй параметр - номер столбца с уровнем входимости (номируется с 1, А=1)
Call constructTree(2, 1)
Range("A1").Select
End Sub