gibt es eine Möglichkeit ein Laufwerk nach einem Dateinamen durchsuchen zulassen und es wird zu den jeweilgen Ergebnissen ein Hyperlink erstellt?
Also er soll nach Einkaufsliste suchen und alle auflisten egal ob xls oder pdf.
Gruß
Marcus
Option Explicit
Dim wksStart As Worksheet, wksInhalt As Worksheet, vntFiles(), lngFiles As Long
Dim strSuch As String
Sub prcFolders()
Dim FSO As Object, oFolder As Object
Dim strFolder As String
Application.ScreenUpdating = False
Set wksStart = ThisWorkbook.Sheets("Start")
On Error Resume Next
Set wksInhalt = ThisWorkbook.Sheets("Inhalt")
On Error GoTo 0
If wksInhalt Is Nothing Then
Set wksInhalt = Worksheets.Add
wksInhalt.Name = "Inhalt"
End If
Set FSO = CreateObject("Scripting.FileSystemObject")
strFolder = wksStart.Cells(1, 2)
If strFolder = "" Then
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "c:\" 'anpassen
If .Show = -1 Then
strFolder = .SelectedItems(1)
End If
End With
End If
If strFolder = "" Then Exit Sub
strSuch = Application.InputBox("Dateiname?", "Suchbegriff")
If strSuch = "Falsch" Then Exit Sub
strSuch = LCase("*" & strSuch & "*")
Set oFolder = FSO.getfolder(strFolder)
lngFiles = 1
With wksInhalt
.Range("A:C").ClearContents
.Cells(1, 1) = "Pfad"
.Cells(1, 2) = "Dateiname"
.Range(.Cells(1, 1), .Cells(1, 3)).Font.Bold = True
End With
prcFiles oFolder
prcSubFolders oFolder
With wksInhalt
.Range(.Cells(2, 1), .Cells(lngFiles, 2)).FormulaLocal = WorksheetFunction.Transpose(vntFiles)
.Activate
End With
End Sub
Sub prcSubFolders(oFolder)
Dim oSubFolder As Object
For Each oSubFolder In oFolder.subfolders
prcFiles oSubFolder
prcSubFolders oSubFolder
Next
End Sub
Sub prcFiles(oFolder)
Dim oFile As Object
For Each oFile In oFolder.Files
If LCase(oFile.Name) Like strSuch Then
ReDim Preserve vntFiles(1 To 2, 1 To lngFiles)
vntFiles(1, lngFiles) = oFolder.Path
vntFiles(2, lngFiles) = "=hyperlink(""" & oFile.Path & """;""" & oFile.Name & """)"
lngFiles = lngFiles + 1
End If
Next
End Sub
Public Sub SearchFiles()
Dim wksInhalt As Worksheet
Dim objFileSearch As clsFileSearch
Dim ialngIndex As Long, lngFileCount As Long, lngRow As Long
Dim strFolder As String, strSearch As String
strSearch = InputBox("Dateiname?", "Suchbegriff")
If StrPtr(strSearch) = 0 Then Exit Sub
strFolder = ThisWorkbook.Worksheets("Start").Cells(1, 2).Text
If strFolder = "" Then
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:\" 'anpassen
If .Show Then
strFolder = .SelectedItems(1)
Else
Exit Sub
End If
End With
End If
Application.ScreenUpdating = False
lngRow = 2
For Each wksInhalt In ThisWorkbook.Worksheets
If wksInhalt.Name = "Inhalt" Then Exit For
Next
If wksInhalt Is Nothing Then
Set wksInhalt = Worksheets.Add
wksInhalt.Name = "Inhalt"
End If
With wksInhalt
Call .Activate
Call .Columns("A:C").Clear
With .Range("A1:B1")
.Value = Array("Pfad", "Dateiname")
.Font.Bold = True
End With
End With
Set objFileSearch = New clsFileSearch
With objFileSearch
.CaseSenstiv = False
.Extension = "*.*"
.FolderPath = strFolder
.SearchLike = "*" & strSearch & "*"
.SubFolders = True
lngFileCount = .Execute(Sort_by_Path, Sort_Order_Ascending)
For ialngIndex = 1 To lngFileCount
wksInhalt.Cells(lngRow, 1).Value = .Files(ialngIndex).Path
Call wksInhalt.Hyperlinks.Add(Anchor:=wksInhalt.Cells(lngRow, 2), _
Address:=.Files(ialngIndex).Path, TextToDisplay:=.Files(ialngIndex).Filename)
lngRow = lngRow + 1
Next
End With
Call wksInhalt.Columns.AutoFit
Application.ScreenUpdating = True
Set objFileSearch = Nothing
Set wksInhalt = Nothing
End Sub
Gruß