aus geschlossenen Dateien per Schleife
08.02.2008 00:22:21
Lorenz
Wie kann man das ewige " z= z+1 in eine Schleife packen?
Mit "For 6 To 89" & "Next z" ist es mir leider nicht gelungen (Da schreibt´s die Werte 84mal nur in Zelle ("B2"))
HIER der CODE:
Sub HoleWerte()
Dim fn As String, z As Long
Const pfad As String = "D:\Lohnliste\"
Range("B6:B89").ClearContents
fn = Dir(pfad & "\LL_11_2007.xls")
Do While fn ""
z = 6
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V6")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V7")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V8")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V9")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V10")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V11")
z = z + 1
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "V12")
..... z geht bis 89
fn = Dir()
Loop
End Sub
Function GetValue(path, file, sheet, ref)
Dim arg As String
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
Hat vielleicht jemand nen Tipp?
Danke Lorenz
Anzeige