1

This is one of my first times using VBA. I have a command button that is supposed to be saving a file as, to my sharepoint online documents page. I can use the "excel services" and save to that documents page from excel manually, but when i try the same in VBA it is saying I do not have permission. Below is the code I am using, any advice would be much appreciated!

Private Sub CommandButton1_Click()

    Dim path as string
    dim filename1 as string

    path = "https://xxxxxx.sharepoint.com/sites/xxxxx/Shared Documents"
    filename1 = Range("B3").text
    activeworkbook.SaveAs FileName:=path & filename1 & ".xlsx", _
                          FileFormat:=xlopenxmlworkbook

End Sub
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
sbvt802
  • 13
  • 1
  • 1
  • 3

1 Answers1

2

If your current file is in the same folder as the destination you would like to save in, try this change for the definition of path:

path = "https://xxxxxx.sharepoint.com/sites/xxxxx/Shared Documents/"

The missing final / in your code is causing the FileName argument to be invalid, because path & filename1 & ".xlsx" evaluates to

https://xxxxxx.sharepoint.com/sites/xxxxx/Shared Documents[filename1].xlsx

Which means if permissions weren't restricted on the /xxxxx folder you would have written a badly named Excel workbook in that location.

ALTERNATIVE SOLUTION

Potentially another solution for your problem. Save an empty workbook in the location you wish to save your new Excel files. Open the empty Excel file, and run your macro there. Change the path line to:

path = ActiveWorkbook.Path & "\"

Try this to see if it works instead. This is how I got around Sharepoint permissions problems.

ERT
  • 719
  • 5
  • 16
  • In that case, OP forgot to append a final `/`. I can edit now. It would still work for OP, even if accessing from Sharepoint. – ERT Jul 11 '17 at 16:30
  • Ah, I see now. OP is trying to save to `https://xxxxxx.sharepoint.com/sites/xxxxx/Shared DocumentsMyAwesomeWorkbook.xlsm` – Mathieu Guindon Jul 11 '17 at 16:37
  • That's right. My original suggestion would have worked fine, if OP was trying to save a new file from an existing file in the sharepoint location. This solution will work fine as well, but is not robust (it only works for this *one* location, not numerous locations). – ERT Jul 11 '17 at 16:39
  • 1
    Upvoted, but I'd just put the final slash within the first string literal; there's no need to allocate 3 strings here (one for the URI, one for the slash, one for the concatenated string). `path = "https://xxxxxx.sharepoint.com/sites/xxxxx/Shared Documents/"` would be just as good, and only allocates the 1 string it needs. – Mathieu Guindon Jul 11 '17 at 16:41
  • It is now working properly and saving to my sharepoint documents page. Thank you both very much for helping a beginner out. – sbvt802 Jul 11 '17 at 17:29