AW: Daten aus geschlossener Datei holen
20.11.2003 10:42:26
LotharR
Moin Atikar,
mit den geschlossenen Dateien auf Server hab ich auch ein Problem. Ich stelle mal folgenen Workaround zur Diskussion:
Public adresse
Private Sub ListBox1_Click()
Range(adresse).Select
ActiveCell.FormulaR1C1 = ListBox1.Value
ListBox1.Visible = False
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim qPath As String
Dim qFile As String
Dim aFile As String
aFile = ThisWorkbook.Name
qPath = ThisWorkbook.Path & "\" ' HIER GGF. DEN PFAD ANPASSEN
qFile = "Mappe2.xls" 'HIER GGF. DEN FILENAMEN ANPASSEN
If Not WkbExists(qFile) Then
Workbooks.Open Filename:=qPath & qFile
Windows(aFile).Activate
End If
If Intersect(Target, Range("c5:c5")) Is Nothing Then Exit Sub
adresse = Target.Address
Range(Target.Address).Select
If ActiveCell.FormulaR1C1 = "" Then ListBox1.Visible = False: Exit Sub
TextBox1 = ActiveCell.Value
Dim index As Integer
ListBox1.Visible = True
ListBox1.Clear
With Workbooks(qFile).Sheets("Lieferanten")
For index = 1 To Workbooks(qFile).Sheets("Lieferanten").Range("A65536").End(xlUp).Row
If LCase(Left(.Cells(index, 1), Len(TextBox1))) = LCase(TextBox1) Then
ListBox1.AddItem .Cells(index, 2)
End If
Next
End With
End Sub
Function WkbExists(qFile As String) As Boolean
Dim wkb As Object
On Error Resume Next
Set wkb = Workbooks(qFile)
If Err = 0 And Not wkb Is Nothing Then
WkbExists = True
End If
On Error GoTo 0
End Function