Posts by Excel-Hopper

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.

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 4th 2020

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

Code

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

Excel-Hopper · Aug 4th 2020

Runtime error 76

Excel-Hopper · Aug 4th 2020

what code tags do you mean?

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

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

Excel-Hopper · Feb 10th 2020

Thank you also to Rory for your Imput

Excel-Hopper · Feb 10th 2020

Now it runs like it must be, I am very happy. After the new findings I had to increase the left column by 1. Everything is perfect. Thank you very much.

Excel-Hopper · Feb 10th 2020

When I start the script, it will probably start writing on line 19, but so far it starts writing on line A19, but should start writing in B19

Excel-Hopper · Feb 10th 2020

That's perfect. How can I still control that the import should start on line 19 (already defined) but on column B in line 19?

Excel-Hopper · Feb 10th 2020

How would I have to write it?

i'm not a savvy vba expert, unfortunately.

Excel-Hopper · Feb 10th 2020

Hello

With the following script I want to import data from a closed Excel, into an open file with certain fields. I am working with Excel 2016, but I always get an error message (Right the error message and the script stops at this location.

Code

Option Explicit
Sub GetData ()
Dim oMe As Worksheet, iZeile As Long, oDatei As Object
Dim oFS As Object, wbQuelle As Workbook

Set oMe = ThisWorkbook.ActiveSheet
Const sDateiPfad As String = "......"
iZeile = 19

Application.ScreenUpdating = False
Set oFS = CreateObject ("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder (sDateiPfad).Files
If LCase (Right (oDatei.name, 4)) = "xlsx" Then

Set wbQuelle = Workbooks.Open (sDateiPfad & oDatei.name)
With wbQuelle.ActiveSheet

oMe.Cells (iZeile, 1) = .Range ("B4")
oMe.Cells (iZeile, 2) = .Range ("B12")
oMe.Cells (iZeile, 3) = .Range ("B13")
oMe.Cells (iZeile, 4) = .Range ("B14")
oMe.Cells (iZeile, 5) = .Range ("B21")
oMe.Hyperlings.Add Anchor:=oMe.Cells (iZeile, 28), 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

Posts by Excel-Hopper

Path not found

Path not found

Path not found

Path not found

Path not found

Path not found

Path not found

Error message When compiling: Project or library not found

Error message When compiling: Project or library not found

Error message When compiling: Project or library not found

Error message When compiling: Project or library not found

Error message When compiling: Project or library not found

Error message When compiling: Project or library not found