Excel Extract Autofit
13.07.2018 10:30:17
Christian
aus meiner Datenbank möchte ich einen Excel Extrakt ziehen, dafür habe ich folgende Codestruktur aufgebaut. Bei dieser Zeile beschwert er sich:
Fehler beim Kompilieren: Sub oder Function nicht definiert. Habt ihr eine Lösung für mich :)?
Columns("A:AL").EntireColumn.AutoFit
Private Sub Befehl98_Click()
Dim rs As DAO.Recordset ' !!Verweis auf Microsoft DAO x.x setzen !!
Dim xlApp As Object, xlWB As Object, xlSheet As Object ' Excel Objekte
Set rs = Me!Unterformular_Excel.Form.RecordsetClone ' Daten des UForms
Set xlApp = CreateObject("Excel.Application") ' Excel Instanz
xlApp.Visible = True ' Sichtbar schalten
Set xlWB = xlApp.Workbooks.Add ' Neues Workbook anlegen
Set xlSheet = xlWB.Sheets(1) ' Erstes Tabellenblatt setzen
xlSheet.Cells(2, 1).CopyFromRecordset rs ' Accessdaten in (ab) B2 schreiben
Columns("A:AL").EntireColumn.AutoFit
xlSheet.Cells(1, 1) = "Request ID"
xlSheet.Cells(1, 2) = "Area"
xlSheet.Cells(1, 3) = "Prio"
xlSheet.Cells(1, 4) = Format("Received Date", "dd.mm.yyyy")
xlSheet.Cells(1, 5) = Format("Received Time", "HH:MM")
xlSheet.Cells(1, 6) = "MSN"
xlSheet.Cells(1, 7) = "Work Order"
xlSheet.Cells(1, 8) = "IPOSCD"
xlSheet.Cells(1, 9) = "IPOITEM"
xlSheet.Cells(1, 10) = "SOP"
xlSheet.Cells(1, 11) = "KIT"
xlSheet.Cells(1, 12) = "DR & FC"
xlSheet.Cells(1, 13) = Format("Creation Date", "dd.mm.yyyy")
xlSheet.Cells(1, 14) = Format("Creation Time", "HH:MM")
xlSheet.Cells(1, 15) = "Delivery Note Number"
xlSheet.Cells(1, 16) = "AWB"
xlSheet.Cells(1, 17) = "Box"
xlSheet.Cells(1, 18) = "Request"
xlSheet.Cells(1, 19) = "Category Inbound"
xlSheet.Cells(1, 20) = "Category Production"
xlSheet.Cells(1, 21) = "Category Outbound"
xlSheet.Cells(1, 22) = Format("Validation Date", "dd.mm.yyyy")
xlSheet.Cells(1, 23) = Format("Validation Time", "HH:MM")
xlSheet.Cells(1, 24) = "Confirm CT"
xlSheet.Cells(1, 25) = Format("Starttime DBS", "HH:MM")
xlSheet.Cells(1, 26) = Format("Startdate DBS", "dd.mm.yyyy")
xlSheet.Cells(1, 27) = "Status of Request"
xlSheet.Cells(1, 28) = "Comment DBS"
xlSheet.Cells(1, 29) = "Actionholder"
xlSheet.Cells(1, 30) = "Confirm DBS"
xlSheet.Cells(1, 31) = Format("Endtime DBS", "HH:MM")
xlSheet.Cells(1, 32) = Format("Enddate DBS", "dd.mm.yyyy")
xlSheet.Cells(1, 33) = "Request Closed"
xlSheet.Cells(1, 34) = "Validation Email"
xlSheet.Cells(1, 35) = "Validation Editor"
xlSheet.Cells(1, 36) = Format("Closed Date", "dd.mm.yyyy")
xlSheet.Cells(1, 37) = Format("Closed Time", "HH:MM")
xlSheet.Cells(1, 38) = "Department"
Set db = Nothing
Set rs = Nothing
End Sub
Christian