Hallo!
Nun mal zu den anderen Fragen. :-) Die Fehlermeldung beim Ansprechen der Tabellenblätter stammen daher, dass Excel nicht weiß, welches Blatt du ansprechen willst. Da müsstest du vor dem Range(...).... immer noch das Tabellenblatt angeben. Excel bezieht sich dann auf das geöffnete Workbook. Ich habe mal deinen Code dahingehend abgewandelt. Um es noch übersichtlicher zu machen, in welcher Datei man grad ist, habe ich auch noch das Workbook davor gesetzt. Das könnte man eigentlich weglassen, man muss halt nur aufpassen, welche Datei grad aktiv ist.
Es gibt noch 2 Variablen mehr - Spalte und Ausgang.
Spalte sucht dir die nächste freie Spalte im Blatt1 von Order_Spread_Export.xls raus. Ich gehe dabei davon aus, dass dabei in Zeile 2 immer was steht. ( in Spalte A ja Zeiten und danach das eingetragene vom Makro)
Ausgang ist deine Datei mit dem Code, also Order_Spread_Export.xls. Brauche ich nur zum Dateiansprechen.
Wenn du in deinem Ordner mit den 65 Dateien übrigens nur deine 65 Dateien hast und keine anderen kannst du es so lassen. Ansonsten solltest du beim ersten zuweisen an StrFile angeben, dass du nur Excel haben willst. Könnte sonst zu Fehlemeldungen führen.
Doch noch eine Frage. In den zu öffnenden Dateien, ist da eigentich im Blatt 2 immer nur B2 und C2 befüllt oder die komplette Spalte B und C bis Zeile 6601? Wenn nicht, ergibt sich da sonst eine Fehlermeldung, weil eine Division durch Null durchgeführt wird. Die Funktion die kopiert wird passt ja die Bezüge an - ansonsten würde ja immer der selbe Wert drinstehen (dann bräuchte man die Formel nicht und berechnet einmal den Wert und fügt den dann ein).
Also hier mal der Code, ggf. mal noch die Dateinamen und Pfade anpassen (falls falsch geschrieben):
Sub Spread_CalcExp()
'Open Files
Dim Source As String
Dim StrFile As String
Const csPath As String = "C:\Users\Maximilian\Documents\Studium\Bachelor Arbeit\Data\Aggr_Orders_Match\"
Dim i As Integer
Dim j As Integer
Dim objRange As Range
Dim Spalte As Integer
Dim Ausgang As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'do not forget last backslash in source directory.
Source = "C:\Users\Maximilian\Documents\Studium\Bachelor Arbeit\Data\"
StrFile = Dir(Source)
'die Datei aus der das Makro startet und die Werte eingetragen werden
Ausgang = "Order_Spread_Export.xls"
Do While Len(StrFile) > 0
'sucht die aktuelle Spalte in der Ausgangsdatei
Spalte = Workbooks(Ausgang).Worksheets(1).Cells(2, Columns.Count).End(xlToLeft).Column
'öfnet die erste Datei
Workbooks.Open Filename:=Source & StrFile
' Prozedur Spread Start
Sheets.Add After:=ActiveSheet
'einfügen der Formel, ggf. könnte man das auch mit for und einem Code durch alle 6601 durchlaufen, könnte aber länge dauern
'Range für A2 bs A6601 festlegen
Set objRange = Workbooks(StrFile).Worksheets(3).Range("A2:A6601") 'Cells(11, 6).Resize(zeilen - 11, 1) 'Bereichlänge anpassen....
'Formel einfügen
objRange.FormulaLocal = "=(Tabelle2!B2-Tabelle2!C2)/MITTELWERT(Tabelle2!B2;Tabelle2!C2)"
Set objRange = Nothing
Workbooks(StrFile).Worksheets(3).Range("A1").Value = "Relative Spread" 'Einfügen einer Überschrift
' die Formatierungen
Workbooks(StrFile).Worksheets(3).Range("A:A").Style = "Percent"
Workbooks(StrFile).Worksheets(3).Range("A:A").NumberFormat = "0.0000%"
' Prozedur Spread Ende
'Ab hier soll die Range("A:A") in das Workbook("Order_Spread_Export") in Sheet1 in die nächste leere Spalte kopiert werden.
' Danach soll sich dass Variable Sheet schließen und ein neues Sheet bearbeitet werden
Workbooks(StrFile).Worksheets(3).Range("A:A").Copy
'Odner Order_Spread_Export.xls aktivieren und dann die Werte einfügen
Workbooks(Ausgang).Activate
'jetzt einfügen, letzter Wert war in Spalte deshalb Spalte +1
Workbooks(Ausgang).Worksheets(1).Columns(Spalte + 1).PasteSpecial (xlPasteValues)
Workbooks(StrFile).Close savechanges:=False
' nächste Datei holen
StrFile = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Das sollte klappen. Dann nen schönen 1. Advent
Gruß Matthias
|