VBA and Sharepoint files

Question

It's becoming clear to me that VBA does not handle file operations with Sharepoint/OneDrive very well. I have the following code:

Sub TestSaves()

Dim fName, fNameNoEx, SaveName As String
Dim fldr As FileDialog

CurrPath = ActiveDocument.Path
'CurrPath = "C:\temp"
If Left(CurrPath, 4) = "http" Then
    PathSep = "/"
Else
    PathSep = "\"
End If

FileNumber = FreeFile
OutputFile = CurrPath & PathSep & "fred.txt"

MsgBox OutputFile

Open OutputFile For Output As #FileNumber

Print #FileNumber, "Hello World"
Close #FileNumber

End Sub

When my CurrPath = ActiveDocument.Path is active, I get a run-time error 52 (bad file name or number). In thise case, CurrPath looks like:

When my CurrPath = "C:\temp" is active, it works fine.

I'd like to be able to save another file in the same location as the original document, regardless of whether it's local or on OneDrive/Sharepoint. Any suggestions?

not sure but I guess the `Open` method can only open files from the file system and not from sharpoint/https. — Pᴇʜ, Oct 12 '21 at 11:18
It was just a guess, maybe someone can confirm that, or you can do some research to confirm that. But in any case what you could do is checkout the file from SharePoint to a local temp folder, edit it with your VBA code and [upload it again to sharepoint](https://stackoverflow.com/questions/50510770/excel-vba-to-upload-file-to-sharepoint). • Or maybe [Map a network drive to a SharePoint library](https://support.microsoft.com/en-us/office/map-a-network-drive-to-a-sharepoint-library-751148de-f579-42f9-bc8c-fcd80ccf0f53) is a solution for your issue. — Pᴇʜ, Oct 13 '21 at 06:18

score 2 · Answer 1 · answered Oct 13 '21 at 10:17

The only way I could figure out how to do it was:

Save my output file to a non-synced location (Environ("temp") in my case)
When I've finished writing to it and after doing Close #FileNumber, I reopen my file from the temp folder Documents.Open filename:=TmpFile
Then I do ActiveDocument.SaveAs2 filename:=OutputFile, FileFormat:=wdFormatText before closing the file again.

Slightly clunky, and I end up with a screen flash at the end of processing as the .txt opens, saves and closes (yes, I did try setting visible:=false on the open, but that also affects ActiveDocument).

But...it works.

score 2 · Answer 2 · answered Oct 22 '22 at 22:28

2

If you are syncing the folder to your local machine using the OneDrive app then you can use this solution to convert the OneDrive Url returned by ActiveDocument.Path to the local path, using the provided function like this GetLocalPath(ActiveDocument.Path).

As a side note, I recommend using ThisDocument instead of ActiveDocument. So including the function, that would be GetLocalPath(ThisDocument.Path)

Your resulting code could look like this:

'Depends on function GetLocalPath, provided here: 
'https://stackoverflow.com/a/73577057/12287457

Sub TestSaves()
    Dim fName, fNameNoEx, SaveName As String
    Dim fldr As FileDialog

    CurrPath = GetLocalPath(ThisDocument.Path)

    FileNumber = FreeFile()
    OutputFile = CurrPath & Application.PathSeparator & "fred.txt"

    MsgBox OutputFile

    Open OutputFile For Output As #FileNumber
        Print #FileNumber, "Hello World"
    Close #FileNumber
End Sub

answered Oct 22 '22 at 22:28

GWD

3,081
14
30

yeah! many thanks, GWD. It works and saves me, but Sharepoint must be synced. – navafolk Nov 07 '22 at 01:51
Don't we have anyway to ```Save.As``` directly the ```#FileNumber``` to Sharepoint, before ```Close #FileNumber```? Given that pre steps we printed the file purely in a local path. – navafolk Nov 07 '22 at 02:07
@navafolk, if you want to `SaveAs` directly to SharePoint then you have to use something along the lines of the other answer in this thread. You can even avoid the screen flash by creating a second instance of the word app. If you want, I can add code how to do this to my answer. – GWD Nov 07 '22 at 06:51
Thank you, GWD. I would mean that ```SaveAs``` before ```Close #FileNumber```, even Stuart L's script works. – navafolk Nov 07 '22 at 08:36
Hello @navafolk. Unfortunately, I don't know what you mean. The file is already saved before `Close #FileNumber` because the `Open` statement enables the `Print` statement to write directly to the disk. As already mentioned in the comments, the `Open` and `Print` statements can not manipulate/write files on a server via an URL. – GWD Nov 07 '22 at 08:50
understood, big thanks. I will end my research :) – navafolk Nov 07 '22 at 09:10

score 0 · Answer 3 · answered Jul 19 '23 at 05:21

0

You CAN open and save files directly to SharePoint with VBA ...the file name and address needs to be a URL (such as https://[domain].sharepoint.com/sites/[foldername]/Shared Documents/[sub folder]/[filename]) AND have "?web=1" appended.

answered Jul 19 '23 at 05:21

Robert Hind

41
3

VBA and Sharepoint files

3 Answers3

Linked