Ich habe ein Makro, was mir PDfs aus mehreren Ordnern einlesen soll und auf einer bestimmten _ Position in meine Tabelle eintragen soll. Jenes Makro funktioniert:
Private Sub workbook_open()
Dim zeile As Variant
Dim sFile As String, sPattern As String, sPath As String
Dim iRow As Integer
Range("B7:E56,B59:E88,B91:E120,B123:E152,B155:E184").Select
Selection.ClearContents
Range("B7").Select
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1100 CPM Allgemein"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 6, "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1100 CPM Allgemein"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 6 - ((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7:C56") _
_
_
, "")), "B").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1100 CPM Allgemein"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow + 6 - (((Range("C7:C56").Rows.Count) - _
WorksheetFunction.CountIf(Range("C7:C56"), "")) * 2), 5), _
Address:=sPath & sFile, TextToDisplay:="Klick"
Cells(iRow + 6 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7:C56" _
_
_
), "")) * 2), "E").Value = "Klick"
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1200 Anbindung an Strategie"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 37 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3), "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1200 Anbindung an Strategie"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 37 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - ((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
C88"), "")), "B").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1200 Anbindung an Strategie"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow + 37 - (((Range("C7:C56").Rows.Count) - _
WorksheetFunction.CountIf(Range("C7:C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - _
WorksheetFunction.CountIf(Range("C59:C88"), "")) * 2), 5), _
Address:=sPath & sFile, TextToDisplay:="Klick"
Cells(iRow + 37 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "")) * 2), "E").Value = "Klick"
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1300 Konzernsteuergrößen, Kennzahlen und Ziele"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 48 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3), "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1300 Konzernsteuergrößen, Kennzahlen und Ziele"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 48 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - ((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "")), "B").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1300 Konzernsteuergrößen, Kennzahlen und Ziele"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow + 48 - (((Range("C7:C56").Rows.Count) - _
WorksheetFunction.CountIf(Range("C7:C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - _
WorksheetFunction.CountIf(Range("C59:C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - _
WorksheetFunction.CountIf(Range("C91:C120"), "")) * 2), 5), _
Address:=sPath & sFile, TextToDisplay:="Klick"
Cells(iRow + 48 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "")) * 2), "E").Value = "Klick"
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1400 Stellhebel und Werttreiber"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 50 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3), "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1400 Stellhebel und Werttreiber"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 50 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3) - ((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "")), "B").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1400 Stellhebel und Werttreiber"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow + 50 - (((Range("C7:C56").Rows.Count) - _
WorksheetFunction.CountIf(Range("C7:C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - _
WorksheetFunction.CountIf(Range("C59:C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - _
WorksheetFunction.CountIf(Range("C91:C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "")) * 2), 5), _
Address:=sPath & sFile, TextToDisplay:="Klick"
Cells(iRow + 50 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "")) * 2), "E").Value = "Klick"
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1500 Incentivierung"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 79 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "") - 1) * 3), "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1500 Incentivierung"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow + 79 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "") - 1) * 3) - ((Range("C155:C184").Rows.Count) - WorksheetFunction.CountIf(Range("C155:C184"), "")), "B").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\1000 Corporate Performance _
Management\1500 Incentivierung"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow + 79 - (((Range("C7:C56").Rows.Count) - _
WorksheetFunction.CountIf(Range("C7:C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - _
WorksheetFunction.CountIf(Range("C59:C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - _
WorksheetFunction.CountIf(Range("C91:C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "") - 1) * 3) - (((Range("C155:C184").Rows.Count) - WorksheetFunction.CountIf(Range("C155:C184"), "")) * 2), 5), _
Address:=sPath & sFile, TextToDisplay:="Klick"
Cells(iRow + 79 - (((Range("C7:C56").Rows.Count) - WorksheetFunction.CountIf(Range("C7: _
C56"), "") - 7) * 3) - (((Range("C59:C88").Rows.Count) - WorksheetFunction.CountIf(Range("C59: _
_
C88"), "") - 7) * 3) - (((Range("C91:C120").Rows.Count) - WorksheetFunction.CountIf(Range("C91: _
C120"), "") - 10) * 3) - (((Range("C123:C152").Rows.Count) - WorksheetFunction.CountIf(Range("C123:C152"), "") - 1) * 3) - (((Range("C155:C184").Rows.Count) - WorksheetFunction.CountIf(Range("C155:C184"), "")) * 2), "E").Value = "Klick"
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
Next
Nun möchte ich aber, dass er für Tabellenblatt 2 genau das Gleiche macht, nur mit anderen _Verzeichnissen, daher habe ich ergänzt:
Sheets("2000").Select
Columns(1).ClearContents
sPath = "U:\Stuttgart\RoedlConsulting\09_Literatur\Literatur\2000 Shared Service Center\2100 _
_
_
SSC Allgemein"
If Right(sPath, 1) "\" Then sPath = sPath & "\"
sPattern = "*.*"
sFile = Dir(sPath & sPattern)
Do Until sFile = ""
iRow = iRow + 1
Cells(iRow - 81, "C").Value = sFile
sFile = Dir()
Loop
For zeile = 1 To Cells.SpecialCells(xlLastCell).Row
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Right(Range("C" & zeile), Len(Range("C" & zeile)) - 5)
End If
If InStr(1, Range("C" & zeile), ".") > 0 Then
Range("C" & zeile) = Left(Range("C" & zeile), Len(Range("C" & zeile)) - 4)
End If
Next
Sheets("1000").Select
End Sub
Mein Problem ist allerdings das iRow. Ich habe keine Lust, den Cells(iRow...) noch länger werden zu lassen, sondern möchte den Code aus dem Tabellenblatt 1 bzw. "1000" quasi unverändert nutzen... die Verzeichnisse kann ich ja anpassen, mehr möchte ich aber nicht machen.
Hat da jemand eine Idee?