Hello, I have problems with the script.
The file path is not found. The file is located on SharePoint and the script should read the Excel file in a folder located on SharePoint.
Code
Sub GetData ()
Dim oMe As Worksheet, iLine As Long, oFile As Object
Dim oFS As Object, wbQuelle As Workbook
Set oMe = ThisWorkbook.ActiveSheet
Const sDateiPfad As String = "https:\\xxxx.sharepoint.com\sites\001073\xxxxxx\xxxxxx\Anmeldungen\" 'Path is changed because of data protection
iZeile = 19
Application.ScreenUpdating = False
Set oFS = CreateObject ("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder (sDateiPfad). Files
If InStrRev (oDatei.name, "xlsx") Then
Set wbQuelle = Workbooks.Open (sDateiPfad & oDatei.name)
With wbQuelle.ActiveSheet
oMe.Cells (iZeile, 2) = .Range ("B5")
oMe.Cells (iZeile, 3) = .Range ("B13")
oMe.Cells (iZeile, 4) = .Range ("B14")
oMe.Cells (iZeile, 5) = .Range ("B15")
oMe.Cells (iZeile, 6) = .Range ("B16")
oMe.Cells (iZeile, 7) = .Range ("B17")
oMe.Cells (iZeile, = .Range ("B22")
oMe.Cells (iZeile, 9) = .Range ("B28")
oMe.Cells (iZeile, 10) = .Range ("B29")
oMe.Cells (iZeile, 11) = .Range ("B36")
oMe.Cells (iZeile, 12) = .Range ("B24")
oMe.Cells (iZeile, 13) = .Range ("G30")
oMe.Cells (iZeile, 14) = .Range ("H53")
oMe.Cells (iZeile, 15) = .Range ("B30")
oMe.Cells (iZeile, 16) = .Range ("B31")
oMe.Cells (iZeile, 17) = .Range ("G26")
oMe.Hyperlinks.Add Anchor:=oMe.Cells (iZeile, 29), Address:=sDateiPfad_
& wbQuelle.name, TextToDisplay:=wbQuelle.name
wbQuelle.Close False
iZeile = iZeile + 1
End With
End If
Next
Set oMe = Nothing: Set wbQuelle = Nothing
End Sub
Display More