AW: Schlüsselwort in Exceldatei suchen
07.05.2018 19:05:42
Sepp
Hallo Manfred,
der Code für den Such-Button, in den Kommentaren sind die Stellen an denen du ggf. anpassen musst gekennzeichnet!
Dialog UserForm1
Option Explicit
' Name des CommandButton ggf. ANPASSEN!
Private Sub CommandButton1_Click()
Dim objWB As Object, objRange As Object, bolAlreadyOpen As Boolean
Const cstrFile As String = "D:\Forum\1.xls" 'Dateiname - ANPASSEN!
Const cstrTab As String = "Tabelle1" 'Tabellenname - ANPASSEN!
On Error GoTo ErrorHandler
With Application
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
' TextBox1 = Textbox mit Suchbegriff - Name ggf. ANPASSEN!
If Len(Trim$(TextBox1)) Then
' TextBox2 & TextBox3 = Ausgabetextboxen - Namen ggf. ANPASSEN!
TextBox2 = "": TextBox3 = ""
For Each objWB In Application.Workbooks
If objWB.FullName = cstrFile Then bolAlreadyOpen = True: Exit For
Next
If objWB Is Nothing Then Set objWB = Workbooks.Open(cstrFile)
With objWB
Set objRange = .Sheets(cstrTab).Columns(3).Find(What:=TextBox1, LookAt:=xlWhole, _
LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
If Not objRange Is Nothing Then
TextBox2 = objRange.Offset(0, -2)
TextBox3 = objRange.Offset(0, -1)
Else
MsgBox "Suchbegriff nicht gefunden!"
End If
If Not bolAlreadyOpen Then .Close False
End With
Else
MsgBox "Suchbegriff fehlt!"
End If
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "Fehler in UserForm1" & vbLf & vbLf & "Prozedur:" & vbTab & "CommandButton1_Click" & vbLf & _
"Nummer:" & vbTab & Err.Number & vbLf & "Meldung:" & vbTab & Err.Description & vbLf & _
IIf(Erl, "Zeile:" & vbTab & Erl, ""), vbExclamation, "Fehler!"
Err.Clear
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
Set objWB = Nothing
Set objRange = Nothing
End Sub
VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 16 - mit VBAHTML 12.6.0
Typ | Name | Eigenschaften |
CommandButton | CommandButton1 | Caption: | CommandButton1 | Height: | 24 | Left: | 141 | TabIndex: | 3 | Top: | 42 | Width: | 72 |
|
TextBox | TextBox1 | Height: | 18 | Left: | 15 | TabIndex: | 0 | Text: | | Top: | 18 | Width: | 72 |
|
TextBox | TextBox2 | Height: | 18 | Left: | 12 | Text: | | Top: | 54 | Width: | 72 |
|
TextBox | TextBox3 | Height: | 18 | Left: | 18 | TabIndex: | 2 | Text: | | Top: | 90 | Width: | 72 |
|