1

I would like to write a macro that will retrieve a file from our SharePoint and paste it in a special location for every user that runs the macro. I've used the following code to download the file

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Sub Button1_Click()

   DownloadFileFromWeb = URLDownloadToFile(0, 
   "https://our.sharepoint.com/sharedFolder/file.pptx", "C:\Users\User\folder\myfile.pptx", 0, 0)
   Debug.Print DownloadFileFromWeb

End Sub

Code from: How to download a file from Sharepoint with VBA

The value returned by URLDownloadFile is 0, but when I open the file PowerPoint is asking me to repair it. Ideally, I would like users to simply log into SharePoint and then use this macro to download a fresh copy of the shared file. My guess is that it has to do with authentication but I'm not sure how to solve this problem as I don't have much experience in this area. I would definitely not want to prompt for credentials and pass them on as plain text to a function nor do I want the user to hard code them somewhere. I would prefer for the user to sign in into SharePoint and the code to do the rest. Any help on the matter would be greatly appreciated.

EDIT: I've also tried using the following code producing the same corrupt file. It looks like credentials may not be the problem.

Dim myURL As String
myURL = "https://our.sharepoint.com/sharedFolder/file.pptx"

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile "C:\Users\User\folder\myfile.pptx", 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If

EDIT2:

I tried mapping the drive using various commands including net use and New-psdrive without any luck - I still fail to authenticate. Here's a PowerShell command and its output

net use S: '\\out.sharepoint.com/Shared Library' password /USER:me@domain.us

System error 1244 has occurred.

The operation being requested was not performed because the user has not 
been authenticated.
Community
  • 1
  • 1
ribarcheto94
  • 436
  • 11
  • 25
  • Map the SharePoint as a shared drive (see https://bitwizards.com/Thought-Leadership/Blog/2015/December-2015/How-to-Map-SharePoint-Document-Libraries-as-Networ) then use the `FileSystemObject` – Ryan Wildry Feb 19 '18 at 16:20
  • 1
    That's also not an option since everyone has to map the drive. This is not a good approach to use on a virtual machine either since we don't have admin rights to those sort of things. – ribarcheto94 Feb 19 '18 at 16:52
  • You can map network drives with `Windows Script Host Object` with VBA. The reason the program isn't functioning is likely because you haven't authenticated with the SharePoint for that request. Authentication is tricky with VBA and SharePoint depending on the version. Here's some code that might help: http://ckannan.blogspot.ca/2012/09/vba-download-files-from-sharepoint.html – Ryan Wildry Feb 19 '18 at 17:02
  • Ok, I will give it a try. I was wondering is that drive mapping virtual? – ribarcheto94 Feb 19 '18 at 17:12
  • @ Ryan Wildry, I tried mapping the drive programmatically without any luck - see Edit 2. – ribarcheto94 Feb 20 '18 at 21:04

1 Answers1

-1

I've got this issue yesterday and nothing seemed to work. So I added one row: Workbooks.Open Filename:=myURL after setting URL address and macro now works perfectly now.

JurateCap
  • 1