VBA SharePoint macro

0 votes

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
 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.

Feb 14 in Others by narikkadan
• 59,740 points

1 answer to this question.

0 votes
I believe you are attempting to open a SharePoint folder using the local file system. However, local folder access is not possible for SharePoint folders. To access the folder's contents, you must instead utilise a SharePoint library or the SharePoint API.

It could be simpler to use the local path and sync the SharePoint folder to a local folder via OneDrive.
answered Feb 14 by Kithuzzz
• 34,760 points

Related Questions In Others

0 votes
1 answer

Creating an unique export data VBA Macro Function in Excel

Unfortunately, the tables will vary in size. ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

Excel VBA is not recording macro properly

The "awkward code" is an R1C1 notation ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19 in Others by narikkadan
• 59,740 points
0 votes
1 answer

My VBA macro slows down dramatically with each use

You have a function called ExportRange that ...READ MORE

answered Jan 21 in Others by narikkadan
• 59,740 points
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 59,740 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
0 votes
1 answer
0 votes
1 answer

Macro VBA code failing to execute a section of the code without an error

You get your code to make a ...READ MORE

answered 3 days ago in Others by Kithuzzz
• 34,760 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP