Hi ihr,
ich such gerade verzweifelt nach meinem Codefehler, aber ich find ihn einfach nicht. Auf jeden Fall wird eine Fehlermeldung rausgegeben, weil ich angeblich ein Loop ohne Do habe.
Hier der dazugehörige Code:
ich such gerade verzweifelt nach meinem Codefehler, aber ich find ihn einfach nicht. Auf jeden Fall wird eine Fehlermeldung rausgegeben, weil ich angeblich ein Loop ohne Do habe.
Hier der dazugehörige Code:
Code:
Option Compare Database Option Explicit Function Daten_exportieren() Const ExcelDatNam = "Z:\Reportingtool\test.xls" ' vorhandene Datei Dim xlApp As Object, xlWb As Object, xlSheet As Object Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset, rs4 As DAO.Recordset, rs5 As DAO.Recordset Dim db As Database Dim a As Integer, b As Integer, c As Integer Dim strx As String, strcost_center As String, strcost_center_test As String, strcost_element As String, str_db_anfrage As String Dim year As Integer, month As Integer, country As Integer Set db = CurrentDb() Set rs5 = db.OpenRecordset("SELECT * FROM tbl_cost_center ORDER BY CC_Group") Set rs2 = db.OpenRecordset("tbl_cost_center_group") Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True ' zum Testen Set xlWb = xlApp.Workbooks.Open(ExcelDatNam) rs5.MoveFirst a = 1 b = 2 month = [Forms]![frm_Excel_Export].[E_Month] year = [Forms]![frm_Excel_Export].[E_Jahr] country = [Forms]![frm_Excel_Export].[E_Land] 'Tabellenblatt CC+CE anlegen Set xlSheet = xlWb.Sheets.Add xlSheet.Name = "CC+CE" 'CC anlegen Do While Not rs5.EOF If rs5!CC_Group <> a Then xlSheet.Cells(1, b) = rs2!CCG_Description rs2.MoveNext a = a + 1 b = b + 1 End If xlSheet.Cells(1, b) = rs5!Cost_Center & " - " & rs5!CC_Description rs5.MoveNext b = b + 1 Loop 'CE anlegen Set rs = db.OpenRecordset("tbl_Kostengruppen") rs.MoveFirst b = 2 Do While Not rs.EOF xlSheet.Cells(b, 1) = rs!Description rs.MoveNext b = b + 1 Loop xlSheet.Cells((b - 1), 1) = "" a = 2 rs.MoveFirst Do While xlSheet.Cells(a, 1) <> "" b = 2 Set rs4 = db.OpenRecordset("SELECT * FROM tbl_Kostengruppen INNER JOIN tbl_Cost_Element__Position ON tbl_Kostengruppen.Position = tbl_Cost_Element__Position.Position WHERE (((tbl_Kostengruppen.Description)='" & rs!Description & "'))") rs4.MoveFirst With Sheets("CC+CE") Do While .Cells(1, b) <> "" strcost_center_test = xlSheet.Cells(1, b) strcost_center_test = Left(strcost_center_test, 2) If strcost_center_test = "2G" Then strcost_center = xlSheet.Cells(1, b) strcost_center = Left(strcost_center, 10) Else strcost_center = xlSheet.Cells(1, b) strcost_center = Left(strcost_center, 6) End If str_db_anfrage = "=" Do While Not rs4.EOF strcost_element = rs4!Cost_Element Set rs3 = db.OpenRecordset("SELECT * FROM tbl_Controlling, tbl_Cost_Center WHERE tbl_Controlling.VCost_Center = tbl_Cost_Center.Cost_Center AND Country = " & country & " AND Year = " & year & " AND Month = " & month & " AND Szenario = 1 AND tbl_Cost_Center.CC_Description = '" & strcost_center & "' AND Cost_Element = '" & strcost_element & "'") c = rs3.RecordCount If c = 0 Then Exit Do Else rs3.MoveFirst str_db_anfrage = str_db_anfrage & rs3!Value & "+" End If rs4.MoveNext Loop b = b + 1 Loop rs.MoveNext a = a + 1 Loop End Function