0

In my Excel VBA I have the following:

Dim newFile As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set newFile = fso.CreateTextFile(currPath & "\" & txtFileName)

when currPath is a valid file address (e.g.: C:\temp ) then the CreateTextFile method is successful.

BUT ...

when currPath is pointing to a SharePoint Folder (in my company's "cloud") (e.g.: "https://mycompany.sharepoint.com/somefolder/anotherfolder/anotherfolder" the CreateTextFile method fails.

Because this is a URL and the CreateTextFile needs a fileaddress I changed the URL to a fileaddress as follows:

If InStr(currPath,".sharepoint.") > 0 then
currPathLength = Len(currPath)
currPath = Right(currPath, (currPathLength-8))
currPath="\\" & Replace(currPath, "/", "\")
End If

Resulting in the following currPath value: "\mycompany.sharepoint.com\somefolder\anotherfolder\anotherfolder"

This was not the total correction that was needed. It still failed with

"Error in MyFunction(): -2147417848 - Method 'CreateTextFile' of object 'IFileSystem3' failed"

Do you have any ideas or suggestions?

braX
  • 11,506
  • 5
  • 20
  • 33
Sue
  • 21
  • 4
  • Same problem/question as https://stackoverflow.com/questions/73114050/vba-to-access-sharepoint-with-excel-vba-filesystemobject? – Shrotter Oct 16 '22 at 14:09
  • 3
    I use OneDrive to sync SharePoint Folders. Then I can just use the local drive and folder structure to do whatever I want and OneDrive will do the rest. Or you try to go this [path](https://stackoverflow.com/a/24221462/6600940) – Storax Oct 16 '22 at 14:35
  • 2
    Another option would be to map the sharepoint folder to a network drive but then you might need to provide credentials as well. – Storax Oct 16 '22 at 14:43

0 Answers0