AW: Wert aus geschlossenner Mappe auslesen
18.10.2007 14:20:00
Stefan
Hallo,
ich hatte mal dieses Makro bekommen und es funktioniert wunderbar. Hoffe das passt:
Option Explicit
Sub HoleWerte()
Dim fn As String, z As Long
Const pfad As String = "D:\Firmendaten\Klangmodul.com\Rechnungen\2007\"
Range("A:B").ClearContents
fn = Dir(pfad & "\*.xls")
Do While fn ""
z = z + 1
Cells(z, 1) = fn
Cells(z, 2) = GetValue(pfad, fn, "Tabelle1", "b16")
Cells(z, 3) = GetValue(pfad, fn, "Tabelle1", "b17")
Cells(z, 4) = GetValue(pfad, fn, "Tabelle1", "b18")
Cells(z, 5) = GetValue(pfad, fn, "Tabelle1", "b19")
Cells(z, 6) = GetValue(pfad, fn, "Tabelle1", "b20")
Cells(z, 7) = GetValue(pfad, fn, "Tabelle1", "b21")
Cells(z, 8) = GetValue(pfad, fn, "Tabelle1", "b22")
Cells(z, 9) = GetValue(pfad, fn, "Tabelle1", "b23")
Cells(z, 10) = GetValue(pfad, fn, "Tabelle1", "b24")
Cells(z, 11) = GetValue(pfad, fn, "Tabelle1", "b18")
Cells(z, 12) = GetValue(pfad, fn, "Tabelle1", "b19")
Cells(z, 13) = GetValue(pfad, fn, "Tabelle1", "b20")
Cells(z, 14) = GetValue(pfad, fn, "Tabelle1", "b21")
Cells(z, 15) = GetValue(pfad, fn, "Tabelle1", "b22")
Cells(z, 16) = GetValue(pfad, fn, "Tabelle1", "b23")
Cells(z, 17) = GetValue(pfad, fn, "Tabelle1", "k16")
Cells(z, 18) = GetValue(pfad, fn, "Tabelle1", "k17")
Cells(z, 19) = GetValue(pfad, fn, "Tabelle1", "k18")
Cells(z, 20) = GetValue(pfad, fn, "Tabelle1", "k19")
Cells(z, 21) = GetValue(pfad, fn, "Tabelle1", "k20")
Cells(z, 22) = GetValue(pfad, fn, "Tabelle1", "k21")
Cells(z, 23) = GetValue(pfad, fn, "Tabelle1", "k22")
Cells(z, 24) = GetValue(pfad, fn, "Tabelle1", "k23")
Cells(z, 25) = GetValue(pfad, fn, "Tabelle1", "k24")
Cells(z, 26) = GetValue(pfad, fn, "Tabelle1", "l38")
Cells(z, 27) = GetValue(pfad, fn, "Tabelle1", "l36")
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