Path not found

Excel-Hopper · Aug 4th 2020

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

**AliGW** · Aug 4th 2020

You have not said what the problems are - we are not clairvoyant.

I have added code tags for you - please do this yourself in future. Thanks.

Excel-Hopper · Aug 4th 2020

The mistake was, path not found.

The mistake is coming;

For Each oDatei In oFS.GetFolder (sDateiPfad).Files

Excel-Hopper · Aug 4th 2020

what code tags do you mean?

**AliGW** · Aug 4th 2020

If you click to edit your opening post, you will see what I mean.

See the content section here: Forum Rules

**royUK** · Aug 4th 2020

What is the value of sDateiPfad when the error occurs.

To understand about Code Tags and other requirements of the Forum, simply spend a few minutes reading the Forum Rules

Excel-Hopper · Aug 4th 2020

Runtime error 76

**royUK** · Aug 4th 2020

That's not a value that's an error. If it is the value then you have a problem in the code that produes that value. You need to Debug your code by stepping through it.

Excel-Hopper · Aug 4th 2020

that's what I did. with this code, it indicates the error.

Code

For Each oDatei In oFS.GetFolder (sDateiPfad).Files

**royUK** · Aug 4th 2020

I can't test your code.

You don't seem to be declaring any variables either.

Excel-Hopper · Aug 4th 2020

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, 8) = .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

Excel-Hopper · Aug 5th 2020

Hello, can anyone tell me what the problem is that the path cannot be found?

When I execute the script, an error message is always displayed at the same place (runtime error 76).

Code

For Each oDatei In oFS.GetFolder (sDateiPfad).Files

The whole script for execution is here.

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, 8) = .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

Het script haalt gegevens op uit een extern gesloten map en voegt deze rij voor rij toe aan een nieuw Excel-bestand.

Path not found

Participate now!

Share

Users Viewing This Thread