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:
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
|