I'm attempting to login to Sharepoint and iterate over the folder containing three Excel documents. I want to loop while copying and pasting specific cells from each workbook into the template on my local drive. I want to create three new local files for each file detected in sharepoint. I currently have the following problem: There is no such path. I double-checked to make sure the path was correct, and I was given access. I also tried the DIR method, but for the same reason, it didn't work for me.
This is the code I have now:
Sub CopyFromSharePoint()
Dim folder As Object
Dim file As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim myPath As String
Dim newFile As String
Application.ScreenUpdating = False
Dim fso As FileSystemObject
Set fso = New FileSystemObject
myPath="https://breeuropecom.sharepoint.com/teams/UserAutomation/ Rollover project/Test 3 files"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(myPath)
For Each file In folder.Files
If UCase(Right(file.Name, 5)) = ".XLSX" Then
Set wb = Workbooks.Open(file.Path)
Set ws = wb.Sheets("art166")
Set wb1 = Workbooks.Open("C:\Users\sadyrovac2531\Downloads\test.xls")
Set ws1 = ActiveWorkbook.Sheets("F506a")
ws1.Cells(13, 7) = ws.Cells(1, 1)
ws1.Cells(14, 10) = ws.Cells(11, 2)
ws1.Cells(1, 18) = Replace(ws.Cells(2, 1), "Tax number: ", "")
newFile = "C:\Users\sadyrovac2531\Documents" & file.Name
ActiveWorkbook.SaveAs fileName:=newFile, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
End If
Next file
End Sub
Please let me know if you had a similar issue and fixed it or know how to fix it.
