VBA 2007 aus 2003
Pierre
ich hatte mir mal irgendwann mühselig im Excel 2003 ein Makro erarbeitet, der mir verschiedene ausgewählte Zellen aus ganz vielen Dateien, die in einem Ordner liegen, in eine Datei zusammenführt. Leider funktioniert das gleiche Makro nicht in 2007. Ich bekomme immer den Fehler 445 angezeigt. Kann mir bitte jemand helfen? Anbei das Makro:
Sub Einfügen()
Dim objWb As Workbook, objSh As Worksheet
Dim intCount As Integer, lngRow As Long
On Error GoTo ErrExit
Call EventsOff
lngRow = Application.Max(4, ThisWorkbook.Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row + 1)
With Application.FileSearch
.NewSearch
.LookIn = "C:\Dokumente und Einstellungen\Nedion\Desktop\Praktikum" 'Pfad für Ordner"
.SearchSubFolders = True
.Filename = "*.xls" 'es werden nur xls-intCount aus dem ordner verwendet
If .Execute() > 0 Then
For intCount = 1 To .FoundFiles.Count
If .FoundFiles(intCount) ThisWorkbook.FullName Then
Set objWb = Workbooks.Open(.FoundFiles(intCount)) 'öffnet die Datei
Set objSh = objWb.Sheets("Tabelle1")
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 1) = objSh.Range("E1").Value 'Kunde
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 2) = objSh.Range("E2").Value 'Bauteilbezeichnung
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 3) = objSh.Range("E3").Value 'Ident
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 4) = objSh.Range("B6").Value 'Liefermenge jährlich
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 5) = objSh.Range("B18").Value 'max. Coilgewicht
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 6) = objSh.Range("B20").Value 'i.O.-Platinen pro Coil
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 7) = objSh.Range("B23").Value 'Ladungsträgerart
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 8) = objSh.Range("B37").Value 'Platinen pro LT
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 9) = objSh.Range("B39").Value 'Bruttogewicht LT
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 10) = objSh.Range("E5").Value 'Arbeitsstation
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 11) = objSh.Range("E9").Value 'Platinen pro Hub
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 12) = objSh.Range("E10").Value 'Hub je Minute
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 13) = objSh.Range("E23").Value 'LT pro LKW
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 14) = objSh.Range("E24").Value 'Platinen pro LKW
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 15) = objSh.Range("B34").Value 'Anzahl LT
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 16) = objSh.Range("F28").Value 'SB
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 17) = objSh.Range("I28").Value 'Lagerfläche SB
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 18) = objSh.Range("I9").Value 'Rüstvorgänge
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 19) = objSh.Range("I10").Value 'Coils pr Los
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 20) = objSh.Range("I11").Value 'Stückzahl pro Los
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 21) = objSh.Range("I12").Value 'LT pro Los
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 22) = objSh.Range("I14").Value 'LT gesamt
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 23) = objSh.Range("I22").Value 'Hübe je Los
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 24) = objSh.Range("I25").Value 'Poduktionsdauer
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 25) = objSh.Range("I16").Value 'Lagerkosten Lose
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 26) = objSh.Range("I17").Value 'Lagerkosten SB
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 27) = objSh.Range("I18").Value 'Rüstkosten
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 28) = objSh.Range("I19").Value 'LT-Kosten
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 29) = objSh.Range("I20").Value 'Gesamtkosten
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 30) = objSh.Range("I34").Value 'g Fläche
ThisWorkbook.Sheets("Tabelle1").Cells(lngRow, 31) = objSh.Range("I37").Value 'ü Fläche
Set objSh = Nothing
objWb.Close False 'schließt die Datei
lngRow = lngRow + 1
End If
Next
End If
End With
ErrExit:
If Err.Number 0 Then
MsgBox "Fehler:" & vbTab & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Fehler"
End If
Call EventsOn
Set objSh = Nothing
Set objWb = Nothing
End Sub
Public Sub EventsOff()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub
Public Sub EventsOn()
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub