2

I am a relatively novice programmer and this is my first question on Stack so bear with me:

I am trying to write a macro on one spreadsheet that will automatically update another spreadsheet on SharePoint. The issue I am running into is that this document is protected by a password.

I have been able to open the document using vba, but in order for the changes to be saved to the SharePoint site I also need to check the workbook out and check it back in. Here is the code I have so far:

Sub Open_Document()

'Declare and define variables
Dim document As Workbook
Dim documentName As String
documentName = "\\anonymousSharepointSite\document.xlsm"

'If document is not already checked out, check out and open
If Workbooks.CanCheckOut(documentName) Then
    Workbooks.CheckOut (documentName)
    Set document = Workbooks.Open(fileName:=documentName, Password:="XXX")
Else
    MsgBox ("Can't check out")
End If

End Sub

My goal would be for the program to open and check out "document" without asking the user for the password. As of now the Workbooks.Open method contains the Password argument and works fine, but the Workbooks.CheckOut method is invoking an additional password prompt that I am unsure how to manage.

In my research I have not been able to find any additional arguments for Workbooks.CheckOut other than the Filename. For example I tried the following, but the Password argument is not defined.

Workbooks.Checkout Filename:=document, Password:="XXX"

What am I doing wrong? How can I use VBA to enter the password for this document as with the Workbooks.Open method?

Like I said, this is my first time asking a question on Stack so I apologize if the question is in any way unclear or vague. I would be happy to clear things up if there is confusion.

I appreciate the help!

Community
  • 1
  • 1
bpjesusfreak
  • 33
  • 3
  • 1
    Not a SharePoint dev, but the hard-coded password is already a bad idea; the checkout mechanism likely needs an authenticated user so it can know whether the user is authorized to checkout the file or not - by circumventing that prompt you would effectively open a security breach, not to mention that from that point on every single checkout made by that macro will be made using whoever's credentials you supply/hard-code, which makes the logs useless in case of a breach. Does the user get the pwd prompt if they already have an authenticated session on the SharePoint site? – Mathieu Guindon Jun 27 '17 at 16:53
  • maybe that can help you: https://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code – Jonathan Applebaum Jun 27 '17 at 17:41

0 Answers0