AW: Werte aus geschlossener Datei
05.07.2005 18:12:39
Matthias
Hallo Peter,
mein Vorschlag:
Option Explicit
'VBA Function to Get a Value From a Closed File
'VBA does not include a method to retrieve a value from a closed file.
'You can, however, take advantage of Excel's ability to work with linked files.
'This tip contains a VBA function that retrieves a value from a closed workbook.
'It does by calling an XLM macro.
'The GetValue Function
'The GetValue function, listed below takes four arguments:
'path: The drive and path to the closed file (e.g., "d:\files")
'file: The workbook name (e.g., "99budget.xls")
'sheet: The worksheet name (e.g., "Sheet1")
'ref: The cell reference (e.g., "C4")
Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Function HoleDateiname(ByVal path As String, teil As String)
Dim s As String, found As Boolean
found = False
If Right(path, 1) <> "\" Then path = path + "\" ' Backslash ergänzen
s = Dir(path & "*.xls")
Do While s <> ""
If InStr(s, teil) > 0 Then found = True: Exit Do
s = Dir()
Loop
HoleDateiname = IIf(found, s, "")
End Function
Sub test()
Const teilS = "1234", pfad = "D:\xl\"
Dim fn As String, i As Integer, Wert
fn = HoleDateiname(pfad, teilS)
If fn = "" Then MsgBox "Eine Datei mit " & teilS & " wurde nicht im Verzeichnis " _
& pfad & " gefunden!", vbCritical, "Fehler": Exit Sub
For i = 1 To 12
Wert = GetValue(pfad, fn, "Tabelle1", "B" & i)
Sheets("Tabelle1").Cells(i, 3).Value = Wert
Next
End Sub
Die GetValue()-Funktion habe ich aus dem Forum.
In der Schleife der Prozedur "Test" musst du evtl. noch die Blattnamen anpassen.
Gruß Matthias